0

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

null
    • Fred
    • 1 yr ago
    • Reported - view

    do all your appointment times always fall within the same day?

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred yes

    • Fred
    • 1 yr ago
    • Reported - view

    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?

    • Fred
    • 1 yr ago
    • Reported - view

    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.

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred thanks for your help.

      The "Date du dernier rendez-vous" field of the "Comptes" table is an appointment date format. I have just added the date() function to be able to use the days() function and I have the number of days between "Date du dernier rendez-vous" and today.

      So I move forward in my search.

      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?

      I work with a copy of my database.

      Thanks for your help.

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred When I paste the code in "On click" of the button and I adapt as requested, I have the following error which appears:

      Expected symbol: end at line 4, column 3

      J'ai saisi le code : 

      let sample := select Comptes
      for loop1 in sample do
      if days(loop1.'Dernière interaction',today()) > 90 then loop1.statut := "2"
      end
      

      This is a small database of 70 lines that I use for the test. So I removed "where x = x;"

    • Fred
    • 1 yr ago
    • Reported - view
    Sébastien Guillet said:
    I have the following error which appears:
    Expected symbol: end at line 4, column 3

     For 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. 

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred 

      A double end corrects the error. I tested the button that works. It's great, I will adapt it to push a little more.

      A big thank-you !

      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?

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred Thank you very much for your help, it helped me a lot.

    • Créateur de bien-être
    • Sebastien_Guillet
    • 1 yr ago
    • Reported - view

    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
    
    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred Thank you so much ! Your formula works perfectly!

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 14Replies
  • 139Views
  • 2 Following