I have a formula field in a transaction saved sear...
# generalb
Becca A
09/26/2019, 7:38 PMI have a formula field in a transaction saved search that seems to not be giving the correct results a majority of the time. I can't pinpoint why it works for some transactions and not for others. Can someone tell me if this formula looks correct? The "custcol_css_grant1" (and grant2 and grant3) fields are just custom transaction line fields that hold an amount a user assigns to them. We are aiming for the total of these three custom fields to be equal to the amount field (Completely Assigned).
CASE WHEN (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0) >= {amount} AND {amount} > 0) THEN 'Completely Assigned' WHEN ((NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0) < {amount}) AND (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0)) > 0) THEN 'Partially Assigned' WHEN NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0) = 0 THEN 'Unassigned' WHEN ({custcol_css_grant1} = '' AND {custcol_css_grant2} = '' AND {custcol_css_grant3} = '') THEN 'Unassigned' END
k
KevinJ of Kansas
09/26/2019, 9:13 PMI'd probably do something more like this
Copy code
CASE
WHEN (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0)
>= {amount} AND {amount} > 0)
THEN 'Completely Assigned'
WHEN
((NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0) < {amount}) AND (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0)) > 0)
THEN 'Partially Assigned'
else 'Unassigned' ENDb
Becca A
09/27/2019, 5:17 PMThanks @KevinJ of Kansas but that didn't seem to make a difference. They are still showing as "partially assigned" when they should be "completely assigned." Back to the drawing board I guess!
k
KevinJ of Kansas
09/27/2019, 5:17 PMOh
KevinJ of Kansas
09/27/2019, 5:17 PMI didn't understand the requirement fully...
KevinJ of Kansas
09/27/2019, 5:18 PMcan you share some values form one that is showing as partially assigned?
b
Becca A
09/27/2019, 5:18 PM(I didn't explain it very well looking back at what I wrote yesterday)
k
KevinJ of Kansas
09/27/2019, 5:19 PMare you sure you don't have some rounding errors in play?
KevinJ of Kansas
09/27/2019, 5:19 PMi.e. 23.555 would show as 23.56 - but when adding them together it would treat it as 23.555
KevinJ of Kansas
09/27/2019, 5:20 PMif you had a threshold where you consider something fully covered, let's say a dollar difference you could bake that into your formula
KevinJ of Kansas
09/27/2019, 5:20 PM Copy code
CASE
WHEN (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0)
>= {amount}-1 AND {amount} > 0)
THEN 'Completely Assigned'
WHEN
((NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0) < {amount}) AND (NVL({custcol_css_grant1_amt}, 0) + NVL({custcol_css_grant2_amt}, 0) + NVL({custcol_css_grant3_amt}, 0)) > 0)
THEN 'Partially Assigned'
else 'Unassigned' ENDb
Becca A
09/27/2019, 5:21 PMSure. We have one where the foreign amount field is PHP 22.12 and the amount field is 0.42. The custcol_css_grant1_amt field is also 0.42 and so it should be "completely assigned" but shows as "partially assigned." I'm assuming it's an exchange rate issue - or I guess maybe it's a rounding issue - but I don't know how to change that formula to take the rounding into account.
k
KevinJ of Kansas
09/27/2019, 5:21 PMAlso - double check that your custom columns are numeric or currency. If they are set up as text, they could "look" right, but not calculate properly
KevinJ of Kansas
09/27/2019, 5:22 PMoh... exchange rate fun...
KevinJ of Kansas
09/27/2019, 5:23 PMYou might try using the amount foreign currency field instead of the amount field
KevinJ of Kansas
09/27/2019, 5:24 PM{fxamount}
b
Becca A
09/27/2019, 5:27 PMThis tool was created to be able to only take the amount field in those custom fields. So it has to compare to that. Can you tell a formula to just round to 2 places? Is that something that is possible?
k
KevinJ of Kansas
09/27/2019, 5:28 PMyeah. round({fields},decimal places)
KevinJ of Kansas
09/27/2019, 5:29 PMthough you may want to use cieling instead
KevinJ of Kansas
09/27/2019, 5:30 PMhttps://docs.oracle.com/javadb/10.8.3.0/ref/rreffuncceil.html - just beware - you'll need to do some kind of multiplicaiton by 100, then a division by 100 to get the numbers to "round" correctly with that
b
Becca A
09/27/2019, 5:31 PMI'll give these a shot. Thank you SO much for your help!!
Becca A
09/27/2019, 5:46 PMRounding the amount field worked! Thank you!!!
4 Views