0

Sharing JSON object between two formula fields in the same table

Hello 

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)"

7 replies

null
    • red_kite
    • yesterday
    • Reported - view

    You have access to the values of a JSON object for example via dot notation. Try

    pmtData.message

    Don't use the JSON as formatJSON(). That's just text.

      • Ninox Developer
      • vermau81
      • yesterday
      • Reported - view

       So can I use pmtData.message (which is defined inside "Bal Left" formula field) from within the "Message" formula field ? 

      Can you please give an example how will I do that !!

      • red_kite
      • 21 hrs ago
      • Reported - view

       Yes. First write in Field "Bal Left" 

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

      or only

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

      Then you must give in your function something like "return value". Instead of the void statement as above, type pmtData.message

      Now, you have in fx-field "Bal Left" the original JSON-Object and you could use it as a value for the parameter pmtData in your function. Now the parameter named: pmtDate but you must give the value "Bal Left" inside the function.

      validateBillPayments(currBill.'Bill ID', 'Bal Left');

      In function now you transform this object and last it return the new message. 

      Of course, I don't know your train of thought. For example, I don't know why you want to create the object outside the function.

      I hope, it helps. English ist not my mother tongue. Mirko

    • Ninox Developer
    • vermau81
    • 19 hrs ago
    • Reported - view

    Thanks mate for the reply, I appreciate your help 😊

    So far what I have understood is that I should either have an extra hidden formula field on the form that should ideally return the whole pmtData JSON object (as a good design) instead of one single value from inside the object.

    Once I have the JSON object returned, I can then capture that object inside another script in a different formula field and extract the required value to be displayed in the new formula field !!

    But I will have to repeat the script on every form. Or I can have a global function and move this script inside that function and return the JSON object from there. This way I can access that JSON object on any form in the entire app.

    The only thing I am trying to find out is whether Ninox supports optional parameters or not, to make the function more flexible.

    what are your thoughts on this!!

    • Fred
    • 9 hrs ago
    • Reported - view

    My question is why are you creating a JSON at all? From the code it doesn't seem like you are creating data that doesn't already exist in your tables.

    Just to verify, a bill record can have many related payment records?

    If so then can't you do something like for a message formula field:

    switch count(Payments) do
case 0:
    "No payments found for this bill"
case 1:(
    if Payments.'Pmt Type' = "FULL" then
        if Arrears > 0 then
         "Current Due PAID IN FULL. Arrears (if any) will be paid in their respective bills"
        else
        ""
        end
    else
        if Payments.'Pmt Type' = "PART" then
          "This bill has been partially paid only"
        end
    end
    )
end;

    There are some changes still need for lines 7 and 12 as Payments is an array that needs to be figured out. 

    You can see in line 1, we use the reference link to Payments so we don't have to do any selects.

      • Ninox Developer
      • vermau81
      • 6 hrs ago
      • Reported - view

       

      So if I understand correctly, you mean to say that 'cuz we are in the context of "Bill" table and writing this script inside a formula field on "Bill" table, we don't need to do a select on Payments and match them against that particular Bill in context -- count(Payment) will automatically return the Payment records corresponding to this current Bill ??

      Secondly, This script is not inside the "Message" formula field, it is inside the "Bal Due" formula field. So I am returning the numerical value of balance left to be paid on the bill that gets displayed in this formula field.

      I have a separate formula field for custom message to be displayed on the form that draws the message value from inside this script's pmtData JSON.

      This is the whole problem -- How to access (from one formula field) the value of a key inside a JSON object (pmtData) from another formula field when that JSON is not being returned from that formula field

      To be honest, 'cuz this was not working for me so I changed this whole logic into a Global function that accepts a Bill record as parameter and returns a JSON object as a string. Now I am calling that global function (validateBillPayments(billRecord: Bill) from two different formula fields ("BalDue" and "Message") on the same form (Bill)

      function validateBillPayments(bill : Bill) do
    let pmtData := {
            balDue: 0,
            pmtCompleted: "NO",
            pmtType: "NOT PAID",
            message: ""
        };
    let pmts := (select Payment where trim(upper(Bill.'Bill ID')) = trim(upper(bill.'Bill ID')));
    switch count(pmts) do
    case 0:
        (
            setItem(pmtData, "balDue", bill.'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 bill.Arrears > 0 then
                    setItem(pmtData, "message", "Current Due PAID IN FULL. ARREARS WILL BE PAID IN RESPECTIVE BILLS")
                else
                    setItem(pmtData, "message", "Bill PAID IN FULL")
                end
            else
                if item(pmtData, "pmtType") = trim(upper("PART")) then
                    setItem(pmtData, "balDue", number(bill.'Total Due') - number(bill.'Total Paid'));
                    setItem(pmtData, "message", "BILL PART PAID ONLY")
                end
            end
        )
    default:
        (
            setItem(pmtData, "balDue", number(bill.'Total Due') - number(bill.'Total Paid'));
            let billBal := number(item(pmtData, "balDue"));
            if billBal = 0 then
                setItem(pmtData, "message", "Bill PAID IN FULL")
            else
                if billBal > 0 then
                    setItem(pmtData, "message", "BILL PART PAID ONLY")
                end
            end
        )
    end;
    "return the JSON data object as a string";
    formatJSON(pmtData)
end

       

      All I now have to do is:

      1. Call this function from within "BalDue" formula field (with the current BillRecord as parameter) to display the balance due

      let currBill := this;
let pmtData := parseJSON(validateBillPayments(currBill));
number(item(pmtData, "balDue"))

      2. Call the same function again from within "Message" formula field (this time with null as parameter) to display the custom message on the form

      "-- create a temporary reference to a record";
let nullRec := this;
"-- make the reference null";
nullRec = null;
"-- pass null parameter to the function";
let pmtData := parseJSON(validateBillPayments(nullRec));
text(pmtData.message)

       

      Attached below is the image for the Form that displays the correct Balance Due and the custom message after one or multiple payments are made for a particular bill.

       

      Only thing I am not able to understand is the question I asked in another post I created yesterday (link below). Would be nice if you could go through that post also

      https://forum.ninox.com/t/g9y11t9/passing-null-for-a-parameter-of-record-type-to-custom-functions

       

      Thanks,

      Vermaji

      ---------------------------

    • Fred
    • 4 hrs ago
    • Reported - view
     said:
    So if I understand correctly, you mean to say that 'cuz we are in the context of "Bill" table and writing this script inside a formula field on "Bill" table, we don't need to do a select on Payments and match them against that particular Bill in context -- count(Payment) will automatically return the Payment records corresponding to this current Bill ??

     Yes, we want to use the power of relationships. According to your pictures, there is a reference field called Payments(it has the create and add text at the bottom). Which means Ninox already knows which records in Payments are linked to this specific Bill record, so use it.

    When you find yourself reaching for the select, think if you have a link you can use instead.

    Of course this does not apply to global functions, which makes sense since you are not in an particular record to start.

    I don't use functions so I can't help you on your other post.

Login to reply

Content aside

  • 4 hrs agoLast active
  • 7Replies
  • 34Views
  • 3 Following