IF statement help

[gazmoz17]

We enter the kilo quantity in our invoice keying. Some things are paid unit of measure EACH other are PER KG. So we enter the total kiloage of these products in the weight section in the background. Then when we key the quantity on the inv item line it pulls through the correct price quantity x sales unit price.

I know the below screen shot is a sales order rather than invoice, but I want this to work for quote, sales order and inv.

Aim

A control which highlights if someone hasn’t keyed the quantity correctly. The problem is we key multiples of the quantity so its not just a case of quantity keyed= unit weight. Needs to be quantity keyed (for Kg unit of sales products only) is a multiple of background weight.
Something like this…
If INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT doesn’t not equal whole number then make text red. But for Unit of measure per kg products only. Dont know if can write an expression for "whole number"?

E.g in screen shot the bottom item line……. customer wants 3 of the Roofing Topcoat 20kgs. So should key in 60kgs not 3. If this isn’t picked up we’ve under charged someone by 57kgs (£156.75 net) and most likely make a loss on the inv overall.

Keying quant control.JPG

Dont know if Supplier Unit Qty can be used in some way?

Unit of measure per Kg product background..JPG

Unit of measure each product background.JPG

Any help much appreciated or mostly likely there’s better alternatives

Kind regards
Gareth

[brucedenney]

Hi


You could use the truncate function to get the integer part of the number, you can then subtract the whole number and if it is not zero you have an issue.



The If expression is constructed like this

Condition ? Instruction 1 : Instruction 2

So If the UNIT of sale is KG
Then If the Integer of the Qty divided by Weight - the Qty divided Weight = 0
Then "Valid Qty"
Else "Invalid Qty"
Else "Not Applicable"

You need something like this.

SOP_ITEM.UNIT_OF_SALE="Kg"?"NOT APPLICABLE":SOP_ITEM.QTY_ORDER/STOCK.UNIT_WEIGHT-Truncate(SOP_ITEM.QTY_ORDER/STOCK.UNIT_WEIGHT)=0?"Valid Qty":"Invalid Qty"

I haven't tested this, I may have made an error and I have not used the Truncate function before so there are more than a few caveats, but It illustrates the approach and I hope it helps.

You may need to extend this to handle "KG" "Kg" "kg" "Kilo" etc You could use just the first char forced into upper case and compare it to "K"

[gazmoz17]

Thats absolutely fantstic Bruce and definitely sounds like it may work. Appreciate you taking the time to do that .

I definitely need to spend a bit of time reading the sage articles on IF statements to understand the concept a bit more instead of winging it. But I think that will work, I'll let you know a bit later.

[brucedenney]

IF is really badly documented, if you search for it in the help you get information on the other if function used for setting colours etc in conditional formatting not for setting values in an expression!!!

There is however a good KB article.
https://ask.sage.co.uk/scripts/ask.cfg/ ... ssibility=

Report Designer - Using If statements in the Expression Editor

An If statement is an expression that is used on a report to say if a certain condition is met then do one thing, else if the condition is not met do something else. You can use an If statement to return certain values depending on the condition specified.

Format of a basic If statement
A typical If statement uses the following format:

Condition ? Instruction 1 : Instruction 2

The ? command is used to mean then.
The : command is used to mean else.
The expression is therefore saying:

If the condition is true THEN apply instruction 1, ELSE apply instruction 2.

When constructing the If statement, you must always include at least one condition, an instruction 1 and an instruction 2.

Format of a nested If statement
You can specify more than one condition with different results within one expression. This is a nested If statement.

Nested If statements use the following format:

Condition 1 ? Instruction 1 : Condition 2 ? Instruction 2 : Instruction 3

where:

The ? command is used to mean then.
The : command is used to mean else.
The expression is therefore saying:

If condition 1 is true THEN apply instruction 1, ELSE if condition 2 is true THEN apply instruction 2, ELSE apply instruction 3.

When constructing a nested If statement, you must always end the statement with an else instruction to cover when the specified conditions aren't met.

[gazmoz17]

Thanks for the above your right it is really badly documented!

[gazmoz17]

Hi,

Works great . Had to change the beginning of it around though so EACH was "NOT APPLICABLE.

SOP_ITEM.UNIT_OF_SALE="EA"?"NOT APPLICABLE":SOP_ITEM.QTY_ORDER/STOCK.UNIT_WEIGHT-Truncate(SOP_ITEM.QTY_ORDER/STOCK.UNIT_WEIGHT)=0?"Valid Qty":"Invalid Qty"

Struggling though to conditionally format the below red & underlined when theres any item line with a "Invalid Qty".
Dont want the "Check Quantity of Inv Item lines!" to show when theres no "Invalid Qty" showing.

Conditional format.JPG

Sales Order check.layout

Kind Rgds
Gareth

[gazmoz17]

Not sure if I still need to have "KG" in my IF statement somewhere?

[brucedenney]

Sounds like I may have got things the wrong way around in the logic, if it works then that is fine.

You don't need conditional formatting you can set the value to "" for the cases where it is not an invalid qty and they will not print even if they are red.

[brucedenney]

I see your issue.

When you inspect to the value of CHECKXPRESS in the footer what you are inspecting is the value of the last line on the invoice not all the lines on the invoice.

If you want to see if there are any lines that have an error on the invoice (or if you want to know how many lines have errors) then you need to inspect all the lines on the invoice.

The way I would do this is to make CHECKEXPRESS be either 0 or 1 as a number, I can then do a total in the footer CHECKEXPRESSTOTAL and it will tell me how many lines have errors, if it is greater than 0 I want to show the warning, easy.

I also use the value on each line to unhide an "ERROR" text on each line in Red and to apply your formatting to the line.

I attach my attempt at it.

Sales Order check 2.layout

I hope this makes sense.

[gazmoz17]

Great Bruce I’ll take a look tomorrow on the software . Is it possible for that if statement to work as it does but have an exception for 4 products.

Said Products apply the if statement as is but only if quantity of said product is 34 kegs or below. 35 kilos and above ignore so doesn’t return a “Invalid” error.

Reason is...have 4 products which are a full roll 35kgs. Have quant variations of these products under different SKU’s. You remember you used to work with GRP lol there full rolls of chopped strand Matt. The variations are diff kiloage and price breaks the greater kiloage you take.

Eg Sku 4023: Description:1Kg-4kgs CSM

Etc with different price breaks, these products are EACH not KG uom in background.

If someone wants 39kgs we give them a full roll (35kgs) and cut them 4kgs but we key it as 39 quantity under full roll SKU so they benefit from the price reduction of taking a full roll...instead of 35@full roll SKU and 4 @ SKU 4023 greater per kilo cost. This instance would trigger an “Invalid” but would be fine. So want it to still trigger invalid when below 35kgs but not when above.

Many Thanks

[gazmoz17]

Good morning, just tried your layout and it works absolutley bang on mate thanks a lot would never of got there in a million years...Plus the number of item line errors at the bottom isnt something I even though of.

Thanks

[gazmoz17]

Hi Bruce,

I'm struggling a bit to edit the error message at the bottom.

Ive changed the conditional format of my expression CHECKEXPRESS. And this works great, the "Invalid Qty" sign no longer shows for Acetone products (even though these are sold each as a quantity, I need to keep KG in background as pull through for a dangerous goods delivery note I have) the other change is not show below a certain quant for rolls of chopped strand matt. Tested this and it works the invalid quantity wont show for these exceptions. But includes them as error messages at the bottom, which I know it will because its totalling from my CHECKEXPREss total. Tried changing it using a different expression but doesnt work

I think ideally I need to amend the original IF statement on CHECKEXPRESS but keep getting errors when I attempt. So the IF statement follows the expression amendment Ive done to excluded acetone prods & the chopped strand matt above certain weights.

Any help much appreciated.

[gazmoz17]

Amended layout below:

bruce inv2 21.11.layout

Errors when ran, number of errors doesnt match error lines:

[brucedenney]

Can you explain what you are trying to do.

[gazmoz17]

Hi Bruce, yes sorry wasnt very clear. Your template works great but I want some exceptions to the IF statement please if possible. So if possible in the construction of an IF statement keep it as it is but exclude certain scenarios.

Current IF Statement

INVOICE_ITEM.QUANTITY="EA"?0:INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT-Truncate(INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT)=0?0:1

But exclude the following products with weight in background.

Acetone products.JPG

These products are sold as EACH but because of the weight in the background it triggers the "Invalid Qty" text. Can't just take out the weight because its needed in another layout for a hazardous delivery note which uses the weight to calc pack size and number of packs.

INVOICE_ITEM.STOCK_CODE IN ("8100", "8101", "8102", "8103", "8104", "8105")

Then for other prods which are UoM per KG need below exceptions:

OR (INVOICE_ITEM.STOCK_CODE IN ("4111", "4112", "4160", "4161", "4162") AND INVOICE_ITEM.QUANTITY > 35.0) OR (INVOICE_ITEM.STOCK_CODE IN ("4109") AND INVOICE_ITEM.QUANTITY > 26.0) OR (INVOICE_ITEM.STOCK_CODE IN ("4110") AND INVOICE_ITEM.QUANTITY > 28)

These are prodcuts which are full roll weights, e.g.g 35, 26 & 28kgs. But sometimes we key above these values so if people want more than a full roll weight there getting the total kiloage quantity at the full roll lower price. So "4109" someone wants 29kgs its a full roll 26kg plus 3 kgs and we just key the use the full roll code and key 29. Currently this would trigger an error "Invalid Qty" because 29 isnt a multiple of 26. Still want to trigger an error if below weight value e.g in this case below 26.

Ive managed to do the above as conditional formatting so the "Invalid Qty" doesnt show but they still show in the error count at the bottom. I understand why they do because there still flagging as CHECKEXPRESS but I dont know how to amend the original IF statement or if theres another way.

Many Thanks
Gareth