If condition not working with comparison operators
Hi,
I have got a headache trying to understand why can Ninox scripting engine comprehend such a simple If condition together with "and" and "or" operators:
let currentDate := today();
let invoiceDueDate := 'Due Date';
let currentInvoiceStatus := number('Invoice Status');
let overdueDays := days(invoiceDueDate, currentDate);
if overdueDays > 0 and (currentInvoiceStatus != 3 or currentInvoiceStatus != 5) then
styled(text(overdueDays), "#FF8FAE")
else
if currentInvoiceStatus = 1 then
styled(text(null), "#B7E3F1")
else
if currentInvoiceStatus = 2 then
styled(text(null), "#264EF9")
else
if currentInvoiceStatus = 3 then
styled(text(null), "#20C461")
else
if currentInvoiceStatus = 5 then
styled(text(null), "#F1CC08")
end
end
end
end
end
Requirement:
To display the number of days by which the invoice is overdue in case the overdueDays > 0 and currentInvoiceStatus is not Cleared or Cancelled.
Different Invoice Status:
1 - New, 2 - Sent, 3 - Cleared, 4 - Overdue and 5 - Cancelled
Result:
With the current code above, I am getting the overdue days in all the conditions no matter what (whether the currentInvoiceStatus is "New" or "Cancelled" or "Cleared" or "Paid" or "Overdue").
It should only show the overdue days if the currentInvoiceStatus is either "New" or "Sent". If the currentInvoiceStatus is "Cleared" or "Cancelled", the overdue days should be null.
Thanks,
Vermaji
--------------------------
18 replies
-
This example can be referred to..
-
Well I got this to work for me:
let currentDate := today(); let invoiceDueDate := 'Due Date'; let currentInvoiceStatus := number('Invoice Status'); let overdueDays := days(invoiceDueDate, currentDate); if overdueDays > 0 and currentInvoiceStatus != 3 and currentInvoiceStatus != 5 then styled(text(overdueDays), "#FF8FAE") else if currentInvoiceStatus = 1 then styled(text(null), "#B7E3F1") else if currentInvoiceStatus = 2 then styled(text(null), "#264EF9") else if currentInvoiceStatus = 3 then styled(text(null), "#20C461") else if currentInvoiceStatus = 5 then styled(text(null), "#F1CC08") end end end end endI would've thought the 'or' would be the most appropriate way to write the code. I still get tripped up with this when I write my code. I guess the best way to remember is to think of it as a list of requirements so it is always an 'and'.
I want this to be true if:
variable doesn't equal 3
and
variable doesn't equal 5
and
-
But logically Fred it should be OR instead of AND (I don’t want it to be either 3 Or 5). This is how it’s always been for the last so many years (ever since I have started studying computer science). Since it is a single choice combo box, only one value can be selected at a time, so it should ideally be “Or”. I think something is wrong with Ninox coding. I will raise a support ticket highlighting this. With “AND” it just gets a whole new meaning altogether.
Thanks for your help dear. I will use AND for now, but contact support as well.
-
Actually on a second thought you are right. I gave it a good thought and realised that I was wrong on thinking it that way. It sounds correct to say like that in verbal English, but syntactically it should be "AND" instead of "OR", 'cuz if we put "OR" it will return TRUE as soon as one of the two conditions is met and it will then enter inside the IF condition.
Hence "AND" is the correct operator in this case. Thanks a ton like always for solving the problem. I am slowly getting the hang on Ninox.
Need help with another thing:
1. How can I change the value of the combo box from "New" or "Sent" to "Overdue" as soon as the overdueDays become > 0.
2. How to write comments inside a script. It becomes very difficult to write code without comments.
Thanks in advance :)..
-
said:
2. How to write comments inside a script. It becomes very difficult to write code without comments.There are two ways:
1) you can use double quotes, " ", followed by a semi-colon, ;.
2) you can use #{ }#;
said:
1. How can I change the value of the combo box from "New" or "Sent" to "Overdue" as soon as the overdueDays become > 0.The easy way is with a button.
Or put it in the Trigger after update of a field, but I can't figure out a field that would make sense.
Ninox doesn't have the ability for automated jobs based on date/time. You can trigger some code when you open the DB. Under Options for the DB, you can put code in Trigger after open.
I would say start with a button to modify one record. Then update the button to modify all records. When that is working, put the code in the Trigger after open.
Do you need help with the code?
-
It's a shame that Ninox doesn't have such ability to automatically update a record in the background based on Date/Time. It would be good if there was something that Ninox could provide on accessing the record (not after update), so that as soon as we access/open the record it could change the status upon checking the date.
There are so many things that are based on Date/Time like reminders, sending emails, scheduling emails etc.. I can only imagine that how difficult it would be to make workarounds in such a situation.
Making it happen on the click of a button is kind of manual thing that the operator of the app would have to do every day. What if the operator forgets to press the button some day?
Automating it at the time of opening the database is a good option as it seems, but it is good in the case of this small app (only Sales and Invoicing module) that I am writing at the moment. But this is going to grow slowly into different ERP modules and Sales and Invoicing module will just be one of the so many modules. In that case it would it would slow down the opening of the app overtime (especially as the number of invoices will grow).
I think the best workaround in such a situation is only on the click of a button. At least that way it would not slow down the whole app at the time of open in the future.
Another Question:
Is it possible to update value inside a data field from a script written in another formula field? I was thinking of writing one script at one place in the 'Invoice Total' formula field and update the 'Discount Amount' and 'VAT/GST Amount' fields based on the script written in the 'Invoice Total' formula field rather than making all these three fields as formula fields and calculating the discount, GST and invoice total separately in respective formula fields (which is the current case right now).
I am thinking like this 'cuz I realised that while calculating the 'Invoice Total', I have to calculate and consider discount and the GST anyways, then why write the common code three times?
Code that I have written in the 'Discount Amount' formula field:
let invoiceAmount := sum('Invoice Items'.Amount); (invoiceAmount * Discount) / 100Code that I have written in the 'VAT/GST Amount' formula field:
let invoiceAmount := sum('Invoice Items'.Amount); let invoiceAmountAfterDiscount := invoiceAmount - 'Discount Amount'; (invoiceAmountAfterDiscount * 'VAT/GST') / 100Code that I have written in the 'Invoice Amount' formula field:
let invoiceAmount := sum('Invoice Items'.Amount); let invoiceAmountAfterDiscount := invoiceAmount - 'Discount Amount'; invoiceAmountAfterDiscount + 'VAT/GST Amount'If you see the above script, you will realise that all of those can be clubbed together inside one script that can be written in the 'Invoice Amount' formula field and then the 'Discount Amount' and 'VAT/GST Amount' fields can be easily updated from within the script. This will also be good for the future if the discount on the invoice or the VAT is changed. The formula will auto calculate the new values and update both the data fields.
Does it make sense?
-
My response to this would be to look into using dashboards, sorry for sounding like a broken record. I do agree that the discount and VAT fields should be static fields.
Formula fields cannot modify data in other fields. Only buttons or triggers (I have a feeling that I’m missing another way) can modify fields.
Which then leads me back to dashboards.
Think of your Invoice table as your raw data. We want to limit end user interaction with it as much as possible. We want this so we can do pre and post actions. Actions like check to make sure the correct people can modify the invoice. Or make sure the proper item amount or VAT is used. Or make sure everyone involved gets an email.
So to answer your question, I would say the Create/Update Invoice button on your dashboard would do all of the calculations then put the data into the proper fields, except for Invoice Total. That can stay a formula field since it is only using data that exist in the Invoice record.
-
I have written a few suggestions/feature requests to Ninox support. Let's see if this would ring a bell in their ears and they implement those suggestions in the future releases...
I would consider your advice and learn dashboards. Also I agree with your saying that user interaction with the raw data in the table should not be allowed. In fact I want to hide the Table view absolutely and only show the Form view to the user to input data in the form (eg: Entering new sale data on the Sale/Invoice form and then create a PDF Invoice on the click of a button so that it can be sent to the customer.
Is it possible to hide the Table view when we open a database and go straight to the Switchboard which has different buttons on it (eg: New Sale, Inventory Management, Customer Management, Human Resources etc.)?
When we click on one of the buttons on the Switchboard, it opens the corresponding form for data entry.
I have attached images for reference..
-
Your feeling about using "or" is indeed correct, but it applies like this:
overdueDays > 0 and not (currentInvoiceStatus = 3 or currentInvoiceStatus = 5)
Following the rules of Boole algebra, it can be transformed to:
overdueDays > 0 and currentInvoiceStatus != 3 and currentInvoiceStatus != 5
-
Bingo... That's it. I didn't know that we can use the NOT operator like this in Ninox. Thank you both and for all your replies and suggestions. I think I will learn Ninox pretty soon if I can have mentors like you..
Thank you
-
-
I would second opinion to use a switch as it will make things easier to read and troubleshoot.
-
said:
Is it possible to hide the Table view when we open a database and go straight to the SwitchboardLook in the Options for the DB.
-
Hi, I just checked the options, but can't find any option there.. Screenshot attached..
-
isn’t the hide user interface the section you want?
-
Yes, but under hide user interface there is no option for Table View (not even under Database option).. I clicked on "Database" thinking it will give me further options, but it started asking for confirmation. I did not click on confirm thinking that it will hide the whole database and I might get into some kind of trouble.
You know in such situations it is better to seek advice from someone senior and experienced like you :)
-
I don't hide things at all since I'm the only user. So play around and see what happens. You can't do anything that you can't undo. Everything shows up under admin mode so no need to worry.
-
hey mate just out of curiosity, do you do development professionally for your clients or just for learning? I am at the moment learning Ninox, but eventually want to get into professional development. I guess then I will have to take their cloud monthly subscription. Currently I have the Mac app that I paid for 2 years ago (which is now free)...
-
I am definitely not a professional. Just another user helping others out.
I use the cloud because the app went free and they started taking a long time between updating the app after the cloud.
-
Content aside
- Status Answered
- 5 mths agoLast active
- 18Replies
- 62Views
-
4
Following