0

Comparing a formula field value with a string from within another formula field

Hello  , hope you are well.

I am facing a simple but silly issue and I am stuck at this point. Thought you might be able to help me out with this one. I have tried everything, but maybe I am missing something out in this simple thing.

I am trying to compare the value of a formula field with another string from within another formula field, bit it does not recognise. Below is my code

 

let dueInMessage := "";
let numberOfDays := days(today(), 'Due Date');
if numberOfDays > 0 then
     dueInMessage := numberOfDays + " D"
else
     if numberOfDays < 0 and Status = "NOT PAID" then
          alert(Status);
          dueInMessage := "Overdue by " + abs(numberOfDays) + " D"
      end
end;
dueInMessage

 

1. In the above code I want to be able to display the number of overdue days if the number of days < 0.

"Status" is a formula field in Bill table that has the value "PAID", "NOT PAID", "PART PAID" depending on the payment done for that particular bill.

Just to let you know that if the bill date is in the future, it correctly shows the number of days due as per the above script.

2. I want to be able to display the "Due In" formula field open the form only in two scenarios

i) When the due date is in he future so that I can track how many days left for making the pmt

ii) When the Status formula field is "NOT PAID"

In case the Status formula field has the value "PAID", there is no need to display the "Due In" formula field.

Snapshots attached below.

 

6 replies

null
    • John_Halls
    • 2 days ago
    • Reported - view

    Hi  

    Try taking the alert statement out as it doesn’t work in a formula field.

    regards John

      • Ninox Developer
      • vermau81
      • 2 days ago
      • Reported - view

       I put alert only to see it it even goes into that if or nor. With or without alert, it doesn't work. I have searched the internet and the documentation.

      It's a simple statement that checks the value of "Status" formula field with the string literal "NOT PAID". It doesn't work. ( numberOfDays < 0 ) condition works on it's own though

    • Fred
    • 2 days ago
    • Reported - view

    Try putting text() around Status?

    What is the code for Status?

    Or in a test formula field create an if statement for Status to see what happens:

    if Status = "NOT PAID" then 1 else 0 end
      • Ninox Developer
      • vermau81
      • yesterday
      • Reported - view

       Thanks Fred, that little trick worked.

      Here is the problem,

      1. Nowhere on internet I could see that we need to use text() function

      2. The documentation is so weak and limited. 

      3. Now it is clear that the formula box internally returns another data type instead of a string (although it holds a string inside it that is also visible)

      4. When we want to compare it's value with a literal string, we need to convert the output of formula field to a string using text()

      • red_kite
      • yesterday
      • Reported - view

       The logic behind, is type conversion. Using styled() creates a new type. If you use the comparison operator "=", then you get a mess if they are not the same types.

      You can check types if something goes wrong by using the typeof() or debugValueInfo() functions.

      Well, anyway, with styled do you have an object that contains information: text, color, background, icon. You can see it if you type raw(status). This can all be read by using formatJSON and parseJSON. The only other way to read out the text value is to use text() as  wrote.

      let result := parseJSON(formatJSON(Status))

      and the result is something like

      {"text":"NOT PAID","color":"blue","background":"red","icon":""}

      Now you can use all elements of object like

      result.color
      result.background

      etc.

    • Ninox Developer
    • vermau81
    • yesterday
    • Reported - view

    Ninox should allow the following through their API

    1. To change the styling of the form elements (text box, formula box etc) through the script

    2. There should be a version of the styled function that can change the style properties of the physical elements on the form and not the text inside the elements. Not everyone wants to see the formatted/styled text in the grid view/table view.

    3. Text formatted using the styled function should have full  background cell color in table view instead of just the background of the text. It looks so weird. I don't understand what good does Ninox see in this. It is not at all pleasing from UI/UX point of view.

    4. There should be a separate facility to style the text in the table view (if required to be done)

     

    I hope Ninox is or can read this feedback and implement this in the future updates!!

Login to reply

Content aside

  • Status Answered
  • yesterdayLast active
  • 6Replies
  • 25Views
  • 4 Following