Need help translating nested if else to coldfusion report iif

Hi ​@JimBerkes ,

in the code in my last post, you may replace 

(query.SumOfTrainingHours + query.Carryover) GT 40 AND (query.SumOfTrainingHours + query.Carryover) LTE 80

with 

(query.SumOfTrainingHours + query.Carryover - 40) * (81 - query.SumOfTrainingHours - query.Carryover) GT 0

for integers, or with

(query.SumOfTrainingHours + query.Carryover - 40) * (80.005 - query.SumOfTrainingHours - query.Carryover) GT 0

for decimals 

Hi ​@JimBerkes ,
The CFML code you’ve provided won’t work, let alone provide the correct result. That is because it contains an error (missing >)

<cfset result = query.SumOfTrainingHours

Let’s assume that that is a typo.
You should experiment with Claude AI. When I ask it to convert your CFML to Report Builder code, the result is

    iif(
        (query.SumOfTrainingHours + query.Carryover) GT 40 AND
        (query.SumOfTrainingHours + query.Carryover) LTE 80,
            query.SumForward + query.Carryover - 40,
        iif(
            (query.SumOfTrainingHours + query.Carryover) GT 80,
                40,
            iif(
                query.SumOfTrainingHours EQ 40,
                    40,
                iif(
                    (query.SumOfTrainingHours + query.Carryover) EQ 40,
                        0,
                    query.SumOfTrainingHours
                )
            )
        )
    )

Hi Charlie,

Thanks for your help!  The existing iif code has always provided a result, but the result has always been incorrect.    

Now it’s my job to fix it but I don’t recall ever working on any ColdFusion reports :-)   Your code will help me understand the syntax better

Thanks

Jim 

Jim, before offering a conversion, do you mean that old code had worked for years and suddenly now gives wrong values? That would seem to point to a change in the data. Or do you mean you're building or editing a report and you crafted that first variant and found it not working well?

In either case, it helps to mention how iif in report builder (like in cfml) takes 3 args, the first being the condition to evaluate, and if it results in true then the second arg's results are evaluated (and returned as the result in rb) , else the 3rd's are. (In your cfml, you're setting a result instead, but I assume that was just for testing the logic.)

Note that the (very old, cf7 era) rb parser does not support if/elseif /else logic, only that simple iif. (It also doesn't support cfscript, which came out initially with cf8, so we can't use what it offers.)  So doing if/elseif /else demands we used nested iif calls--and we need to be very careful about it. :-) 

Here's a shot at converting it for use in rb. Note I've used new lines and indentation to help make the logic better stand out better. This is not tested, so apologies for mistakes:

Iif(
    (query.SumOfTrainingHours + query.Carryover GT 40) AND (query.SumOfTrainingHours + query.Carryover LTE 80), 
    (query.SumForward + query.Carryover - 40), 
    Iif(
        query.SumOfTrainingHours + query.Carryover GT 80, 
        40, 
        Iif(
            query.SumOfTrainingHours EQ 40, 
            40, 
            Iif(
                query.SumOfTrainingHours + query.Carryover EQ 40, 
                0, 
                query.SumOfTrainingHours
            )
        )
    )
)

Even if it fails, perhaps the observations I've offered might help you sort things out.