If I follow the requirements correctly then this would be the formula you need:

SOP_ITEM.DUE_DATE = null ? SALES_ORDER.DESPATCH_DATE : SOP_ITEM.DUE_DATE

Hi Darron,

Fantastic! That works.

I'm a bit of a novice with these expressions and I didn't see anywhere on the Sage help a reference to using "null" I was using the double parentheses to express a blank.

Many thanks

Andy

It is mentioned in the help files but a bit hidden to be honest as it's under the 'operators' topic, so probably not somewhere intuitive to look. And even then the example in the topic isn't the most helpful in my opinion.

The detailed explanation of null and why this works is quite technical but I'll try to capture it here in case it is useful.

Null is a software development concept meaning the 'value of nothing'. This differs from double parentheses which denotes an empty string and is not the same *** nothing. Essentially by comparing to "" you are saying does the date equal an empty string, which it can never do as an empty string can't be converted to a valid date. Whereas comparing to Null is saying is the date equal to nothing, and this check is possible as the field can hold a valid date or have no value so a comparison is possible. It's not just dates that have this issue, any other data type other than a 'string' has the issue e.g. an expression such as 

1 = "" ? "Equal" : "Not Equal"

is not valid either as an empty string cannot be converted to a valid number. Comparing an empty string to a string value is of course possible so something like this

"Sage" = "" ? "Equal" : "Not Equal"

is valid because the empty string value can be converted to a string.

Appreciate this is fairly technical (and in fact sill I'm glossing over things a bit) and these concepts actually go way beyond Report Designer and Sage generally. The key thing to remember is that if you are trying to compare two values is that it must be possible to convert the data on the right of the equals sign to the type of data on the left. So comparing numbers to numbers, dates to dates, strings to strings, etc is all OK. However comparing dates to strings, strings to numbers, numbers to dates, etc will not work. Note that it is sometimes possible to convert or coerce values from one type to another e.g. 

CString(SOP_ITEM.DUE_DATE)

would give you the value of the due date represented as as a string rather than a date and you could compare this to an empty string, but this still does not cater for the concept of null as if the SOP_ITEM.DUE_DATE has no value (i.e. equals null) then the result from the above expression would be null and not an empty string. So if you had a formula such as this

CString(SOP_ITEM.DUE_DATE) = "" ? "Equal" : "Not Equal"

You would always see "Not Equal" as the result regardless of if there is a date or not. This is because if a date is specified the CString would return "21/06/2021" (for example) which does not equal an empty string and if no date is specified CString would return null, which also does not equal an empty string. This is why I suggested the comparison to null in my original response rather than converting the values.

Hopefully that is useful and has not gone too far off topic. Expressions in Report Designer are very powerful but there can be a few gotchas like this lurking so wanted to call it out in some detail as the help files currently don't provide good clarity on the concepts.

Thanks for that very detailed explanation of the "null" concept Darron,

If I read it through enough times I think I'll get the hang of it.

It's always good to learn a new bit of code protocol now and then as this helps to improve the reporting for me in SAGE and elsewhere such as when I'm in throes of maintaining/improving the company website.

Have a great day and thanks again for your help!

Regards

Andy