SageForum for UK versions of Sage 50- Help and support.

Multiple Nested If loops.

We're a manufacturer, and we have one customer in particular who likes to re-brand our products with their own name on their invoices and delivery notes etc.
For years we've been editing the description manually every order, so I thought i'd automate it, using nested if / replace loops. What a pain the backside that was to get working!

Anyway, I got it working, so I thought i'd share it simply because I found very few examples of deeply nested IFs on the net.


So,
example product descriptions

"OurProd1 100mm x 10m" becomes "TheirProd1 100mm x 10m"

"OurProd2 100mm x 10m" becomes "TheirProd2 100mm x 10m"

"Ours Clip 100mm x 10m" becomes "OursClip 100mm x 10m" (no name change, but prevents the next replace affecting it by removing the space)

"Ours Somename 100mm x 10m" becomes "Theirs Somename 100mm x 10m" (all other products drop our prefix and use their prefix.)


The following expression is applied to the SOP_ITEM.DESCRIPTION (or INVOICE_ITEM.DESCRIPTION on the invoice layout)

_______________________________________
S.O, P.O and despatch note find replace loop
_______________________________________
SALES_ORDER.ACCOUNT_REF <> "TheirAccountRef"
?
SOP_ITEM.DESCRIPTION ////////////////////////////////// if it's not their account ref, display the normal description
:
SOP_ITEM.DESCRIPTION LIKE "OurProd1%" /////////////////// if it is them, search for "OurProd1%" (% is any chars after the text)
?
Replace(SOP_ITEM.DESCRIPTION, "OurProd1", "TheirProd1") //// make the switch
:
SOP_ITEM.DESCRIPTION LIKE "OurProd2%" /////////////////// if no match found, search for "OurProd2%"
?
Replace(SOP_ITEM.DESCRIPTION, "OurProd2", "TheirProd2") //// make the switch
:
SOP_ITEM.DESCRIPTION LIKE "Ours Clip%" /////////////////// if no match found, search for "Ours Clip%"
?
Replace(SOP_ITEM.DESCRIPTION, "Ours Clip", "OursClip") ////// remove the space from "Ours Clip"
:
SOP_ITEM.DESCRIPTION LIKE "Ours %" ///////////////////// search for "Ours " (this is why we removed the space, to prevent the next line matching)
?
Replace(SOP_ITEM.DESCRIPTION, "Ours ", "Theirs ") ///////// make the switch
:
SOP_ITEM.DESCRIPTION //////////////////////////////// if nothing matches anywhere, fall back to the default description.

______________________________________
invoice find replace loop
______________________________________
INVOICE.ACCOUNT_REF <> "TheirAccountRef"
?
INVOICE_ITEM.DESCRIPTION
:
INVOICE_ITEM.DESCRIPTION LIKE "OurProd1%"
?
Replace(INVOICE_ITEM.DESCRIPTION, "OurProd1", "TheirProd1")
:
INVOICE_ITEM.DESCRIPTION LIKE "OurProd2%"
?
Replace(INVOICE_ITEM.DESCRIPTION, "OurProd2", "TheirProd2")
:
INVOICE_ITEM.DESCRIPTION LIKE "Ours Clip%"
?
Replace(INVOICE_ITEM.DESCRIPTION, "Ours Clip", "OursClip")
:
INVOICE_ITEM.DESCRIPTION LIKE "Ours %"
?
Replace(INVOICE_ITEM.DESCRIPTION, "Ours ", "Theirs ")
:
INVOICE_ITEM.DESCRIPTION

Another way to do this, in a more generic way, is to use a field on the product record, say (Category A on the web tab) and if Category A is not blank, use Category A otherwise use normal description. This is neat in that you can prep all the description externally e.g. in Excel and import them into Category A in one go.

If you are drop shipping, and I guess you maybe if you are changing descriptions you can do some other neat things.

Make a delivery note that has the customers invoice address as the "from" address.

Create a bunch of logo graphics in a directory and then embed them in the layout using an expression to get the name of the jpg file to put in the layouts. You print the delivery notes and they all come out with different logos and from addresses looking like they are from your customer.

Hi, yea I use the category A-C fields on the Web tab for renaming products for two of our other customers, but since they have a a very specific set of products they take from us (thirty or so each) it's easy. The main customer, however, takes all our products (2000+) so in their case it's easier to have an auto renamer script than to import alternate names for every single product. Plus, any new variations we add are automatically handled.

We do use a simple 'sent on behalf of' on the delivery note already, but the logo idea is killer, and my be my brownie-point task for today!