IF Statement

Question

I'm trying to create a IF statement. what I want is that if the delivery address box is not populated I want it to take the Invoice address. I have managed that BUT if not all lines are populated on the delivery address it takes it from the invoice address.... so it can sometimes have a mixture of delivery address and invoice address. 
 I want my statement to be IF no delivery address/ then Invoice address but if noting populated on delivery line to leave blank. my current statement is 
 SALES_ORDER.DEL_ADDRESS_1= "" ? SALES_ORDER.ADDRESS_1:SALES_ORDER.DEL_ADDRESS_1

Darron Cockram · Accepted Answer

If I understand you correctly you mean that you get a mix of address details because only some of the delivery address fields are blank? For example delivery address line 1 is populated so you get that line as expected, but then delivery address lie 2 is blank (legitimately as the address has no line 2) but because of your if statement you actually get invoice address line 2. 
 If that is the case there are 2 possible approaches. The simplest one would be if you can guarantee that delivery address line 1 would never be empty if populated i.e. you'd never have a blank line 1 but a non-blank line 2. If this is the case then you simply need to check address line 1 for each line. So your formula for address line 1 would be exactly as you have already: 
 SALES_ORDER.DEL_ADDRESS_1= "" ? SALES_ORDER.ADDRESS_1:SALES_ORDER.DEL_ADDRESS_1 
 And the formula for address line 2 would be: 
 SALES_ORDER.DEL_ADDRESS_1= "" ? SALES_ORDER.ADDRESS_2:SALES_ORDER.DEL_ADDRESS_2 
 NOTE that the field we check for empty does not change in that formula and remains a check on line 1. It is only the result that changes to address line 2. 
 Then simply lather-rinse-repeat for the remaining address lines. 
 If however you do have a situation where delivery address line 1 might be blank but address line 2 (or any other line) is not, then you'd need to resort to a more complex formula. This would make address line 1 something like this: 
 SALES_ORDER.DEL_ADDRESS_1 = "" And SALES_ORDER.DEL_ADDRESS_2 = "" And SALES_ORDER.DEL_ADDRESS_3 = "" And SALES_ORDER.DEL_ADDRESS_4 = "" And SALES_ORDER.DEL_ADDRESS_5 = "" ? SALES_ORDER.ADDRESS_1:SALES_ORDER.DEL_ADDRESS_1 
 And address line 2 becomes: 
 SALES_ORDER.DEL_ADDRESS_1 = "" And SALES_ORDER.DEL_ADDRESS_2 = "" And SALES_ORDER.DEL_ADDRESS_3 = "" And SALES_ORDER.DEL_ADDRESS_4 = "" And SALES_ORDER.DEL_ADDRESS_5 = "" ? SALES_ORDER.ADDRESS_2:SALES_ORDER.DEL_ADDRESS_2 
 Again the only thing that changes in the formula is the result which becomes line 2. The conditional check remains the same. 
 Apply the same logic to the remaining address lines and you should be good to go with no mixing of delivery/invoice address data. 
 Hope that helps

Sage 50 Accounts

IF Statement

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING