Automatically change a select field based on a date from another field
Hello,
I allow myself to seek the help of the community to know if it is possible to modify a field after a defined time.
I use Ninox as a CRM (customer management)
I have a subtable for activities (appointment) with an appointment field titled "Rendez-vous" in the format: August 24, 2021 6:00 PM - 7:00 PM (appointment lasted 1 hour) .
On the accounts table, I have a single select field titled "statut" which matches the status of the account (active, inactive, away...) and an appointment field titled "Date du dernier rendez-vous" which matches on the date of the last activity recorded for each account on the activity sub-table.
This date allows me to know when I had the last interaction with my customers. It also helps me to know when I have to delete customer data (RGDP).
I would like the selection field titled "statut" to be automatically changed to "inactive" if the date of the appointment field titled "Rendez-vous" is more than 3 months old.
Can anyone help me?
14 replies
-
do all your appointment times always fall within the same day?
-
an appointment field titled "Date du dernier rendez-vous" which matches on the date of the last activity recorded for each account on the activity sub-table
How is the field 'Date du dernier rendez-vous' set? Is it an appointment field or a date field?
-
Try the days() function. This will get you the number of days in real numbers. Then you can add a for loop function.
There is no way in Ninox to trigger something based on time. The best you can do is to use the Trigger on open of the whole DB.
Since it sounds like you want to make changes to a whole table I would recommend you make a duplicate DB and use that for testing. Start by testing with a button, then when you are ready to use the for loop function, limit it to a small subset of your main table before putting it in the Trigger on open of the whole DB.
-
Sébastien Guillet said:
However, I don't understand what I should do next. I know how to create a button but I don't know what I have to add as code so that it can modify the status of each row of the "Comptes" table?Now you can add a for loop function to your button. It would look something like:
let sample := select Comptes where x = x; for loop1 in sample do if days(loop1.'Date du dernier rendez-vous',today()) > 90 then loop1.statut := "inactive" end
change the x's in line 1 so you are only selecting a small set of records to change where you know some of the records the days() function would return a number greater than 90. if your test db is small you can remove the where completely.
change "inactive" in line 3 to match the text in your field. you can also replace the word with the choice number if you want.
if this all works well then you can copy it to the Trigger after open and remove the where from the select in line 1. Now every time you open your DB, it will run this code and change the 'statut' for all records where days() is greater than 90.
-
Sébastien Guillet said:
I have the following error which appears:
Expected symbol: end at line 4, column 3For forgot the end to close the if statement. Add an end at the very end of the code.
also, if you use the choice number you can remove the “” since it is a number.
-
Sébastien Guillet said:
And if I want to automate the function when opening the database (and no longer with a button), where should I add the code? Is it the same or does it have to be adapted again?Make sure you are in admin mode (red wrench), click on the name of your db, then click on Options, then paste your code in the Trigger after open section.
You shouldn’t have to make any changes to the code.
-
Fred
Hello, maybe you can help me regarding a small problem encountered with conditions.
In the previous formulas, there is only one condition (the number of days) however, I need to add conditions with the status.
To go back to our previous example, changing the status to number 1 should not be done if the status (selection field) is 3 or 9 or 10. So I tried to add this condition but it doesn't work. Only the last condition (10) is taken into account (so neither 3 nor 9).
Can you help me ?
let sample := (select Comptes); for loop1 in sample do if days(loop1.'Dernière interaction', today()) < 90; loop1.Statut != 3; loop1.Statut != 9; loop1.Statut != 10 then loop1.(Statut := 1) end
-
Sébastien Guillet said:
changing the status to number 1 should not be done if the status (selection field) is 3 or 9 or 10.Looks like you put the conditions in the wrong place. Try the following changes:
if days(loop1.'Dernière interaction', today()) < 90 and loop1.Statut != 3 and loop1.Statut != 9 and loop1.Statut != 10 then loop1.(Statut := 1) end
Try it out in your button first, removing references to loop1. It should work.
Content aside
- Status Answered
- 1 yr agoLast active
- 14Replies
- 152Views
-
2
Following