Hello Fred

I have been able to make some progress in my project (Expense Manager) in the last few days, however I am stuck in a very weird problem at the moment.. I am writing to seek advise on this..

Requirement: Share JSON object between multiple formula fields in the same table/form

Scenario: I have two tables (Bill and Payment). I first create a new Bill and then make Payment (single or multiple) for that bill. An expense can have multiple bills and one bill can have multiple payments. 

I have a button "Make Pmt" and three formula fields "Total Paid", "Bal Left" and "Message" on the Bill form. After entering the details of the new bill or opening the existing bill, I click on "Make Pmt" to create a new Payment against that bill. Then based on the amt paid (Full pmt, part pmt), I update the "Total Paid" and "Bal Left" formula fields on the Bill form.

I have created the below script on "Bal Left" formula field that validates the payments  made and updates and displays the balance left to be paid on the bill. All this data (balDue, PmtCompleted, Message, PmtType and Message) is stored in a JSON object (pmtData) that I create inside the script on the "Bal Left" formula field.

let pmtData := {balDue: 0, pmtCompleted: "NO", pmtType: "NOT PAID", message: ""};

Problem: I want to display a custom message in the "Message" formula field on the Bill form after the pmt is made for that bill. The customised message is created inside the script depending upon various conditions.

The fact that "Bal Left" and "Message" are two separate formula fields in the same table, how can I access from the "Message" formula field,  the "message" string of the pmtData JSON on the "Bal Left" formula field.

Is there a way to share a local JSON object between two fields on the on the same form rather than creating a global JSON object for the whole database ??

Full code below..

let currBill := this;
"-- create JSON data type for pmt details";
let pmtData := {        balDue: 0,        pmtCompleted: "NO",        pmtType: "NOT PAID",        message: ""    };

"-- function to validate pmts to claculate balance left for the bill";
function validateBillPayments(billID : text,pmtData : any) do
let pmts := (select Payment where trim(upper(Bill.'Bill ID')) = trim(upper(billID)));
switch count(pmts) do
case 0:(
setItem(pmtData, "balDue", currBill.'Current Due');
setItem(pmtData, "message", "No payments found for this bill")
)
case 1:(
setItem(pmtData, "pmtType", text(pmts.'Pmt Type'));
if item(pmtData, "pmtType") = trim(upper("full")) then
setItem(pmtData, "balDue", 0);
if Arrears > 0 then
setItem(pmtData, "message", "Current Due PAID IN FULL. Arrears (if any) will be paid in their respective bills")
else
setItem(pmtData, "message", "")
end
else
    if pmtData.pmtType = trim(upper("part")) then
      setItem(pmtData, "balDue", number('Total Due') - number('Total Paid'));
      setItem(pmtData, "message", "This bill has been part paid only")
    end
end
)
end;
void
end;

validateBillPayments(currBill.'Bill ID', pmtData);
"record('Global Data',1) := formatJSON(pmtData)"