Can't calculate the GST and Balance Pmt formula fields

Fred Hello mate,

Thanks for your solution. You caught it right. This is exactly what I was looking for.

I implemented your solution exactly given by you in my app, but I don't get the number value back. All I get is either "null" or zero.

let f := formatJSON('Order Charges');
let p := parseJSON(f);
let allLineItemCharges := number(item(p, "text"));
let orderTotalAmount := allLineItemCharges + 'Delivery Charges' + Adjustment;
orderTotalAmount

 In the end the value returned for orderTotalAmount is either "null" or zero 'cuz the textbox shows $0.00 instead of $90.00 and the balance due from the customer is -$90.00 although it should be $0.00 as the customer has already made full payment of $90.00 already.

There is another problem -- I tried to run a sample code in the console also, but nothing happens in the console. There is no output in the output window. I keep on clicking the "Run" button, but nothing happens.

I have attached the screenshot as well.

Fred I have done all this and banged my head against the wall many times. The moment I hard code a value in the first line ( let f := formatJSON(styled(text(90), "#F7F8FC")); ) everything is fine and I get the desired result in OrderTotal field.

Looks like for some reason it is not picking up the value from "OrderCharges" formula field in the first line. It is picking up null ('cuz that was what was showing in the alert)...

What's your idea what would it not capture the value from "OrderCharges" formula field?

Sotirios Zormpas Hello Sotirios,

below is the snapshot of my code. Just two lines:..

This is just to give gray background color to the formula field for UI/UX to show that this field is not manually editable..

Also attached is the copy of my database app..

Sotirios Zormpas But the $ sign denotes that this is a currency field. It is customary to have $ sign in any currency field to differentiate it from any other numeric field that is not currency. Every software supports that.

What have I done wrong here by formatting a currency field (rate) to show a $ sign in it?

Sotirios Zormpas Fred Thanks to both of you for helping me resolve this problem. After Fred's advise I wrote the below code to resolve the problem..

"-----------------------------------------------------------------------------------";
"Extract the value of charges from styled JSON object";
"-----------------------------------------------------------------------------------";
let f := formatJSON('Order Charges');
let p := parseJSON(f);
let charges := string(item(p, "text"));
"-----------------------------------------------------------------------------------";
"Exclude the $ sign in charges and pickup the numeric value till the end";
"-----------------------------------------------------------------------------------";
let sumTotalOfLineItems := number(substr(charges, 1));
let orderTotalAmount := sumTotalOfLineItems + 'Delivery Charges' + Adjustment;
"-----------------------------------------------------------------------------------";
"Format the charges back to currency format before showing the value on the form";
"-----------------------------------------------------------------------------------";
styled(format(orderTotalAmount, "$#,##0.00"), "#F78F8FC")

However I will go with the shorter solution that Sotirios has given ( using string() ).

So from my understanding the text() function makes a snapshot of what it gets as an input parameter however the string function ignores the special characters and picks up only the ascii characters.

I can't thank you both enough to have helped me resolve this. This problem was creating problem in my account ( balance due from the customers ) and I was not able to wrap my head around it.

Fred, can you please enlighten me how did you find those values in the JSON set.

{"text":"$30.00","color":"#262F4D","background":"#F7F8FC"} (empty)

How and where can I see the values this way in the future..

Fred I actually implemented the above solution in the end.

My only worry is that we have to calculate the allLineItemCharges and orderTotal at least 4 times on the same form. Is it a good design to ping the same table 4 times for calculating the same values.

1. Calculate OrderCharges

2. Calculate OrderTotal

3. Calculate GST

4. Calculate BalanceDue

Is it possible to have global variables instead so that we calculate them once and just use them 4 times, or have a global function return these two values everytime we call that function.