0

Display a maximum number of data

Hi!

I use a formula field to display text data which is itself from another formula field which contacts data.

I would like to find a solution to display only the last 5 lines and not all of them as currently.

Is it possible ?

 

Here is the code I am using:

join(Compte.'Historique des activités 1:N'.'Nom concaténé', "
")

Here is the current result:

Massage intuitif de 1:00 - Statut : terminé

Réflexologie plantaire de 1:00 - Statut : annulé par le client

Réflexologie plantaire de 1:00 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : reporté

Réflexologie plantaire de 0:30 - Statut : reporté

Réflexologie plantaire de 0:30 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : reporté

Massage intuitif de 1:00 - Statut : reporté

Massage intuitif de 1:00 - Statut : terminé

Réflexologie plantaire de 0:30 - Statut : terminé

Massage intuitif de 1:00 - Statut : terminé

Thanks for your help

10 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Not sure how your tables are configured but this is something that could work:

    let me := this
    let n := slice((select 'Historique des activités 1:N' where Compte = this) order by -number(Id), 0, 5);
    join(n.'Nom concaténé', "
    ")
    
      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

       Thank you, I had tried this solution but I have an error when I add "where"

    • Fred
    • 1 yr ago
    • Reported - view

    beat me to it. again!

    Here is another option.

     said:
    I use a formula field to display text data which is itself from another formula field which contacts data.

    My guess is that 'Nom concatene' is that formula field.

    I would recommend you try the slice() command.

    It could look something like:

    let gatherData := Compte.'Historique des activités 1:N';
    let cntgather := cnt(gatherData);
    let last5 := slice(gatherData, cntgather - 5, cntgather)
    join(last5.'Nom concaténé',"
    ")

    This finds the last 5 records according to the default sort of record Id.

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

       THANKS ! This formula works perfectly.

      I have another request. This is a detail but if it is possible to modify the display order (from the most recent to the oldest)?

      The date field in question is: 

      Compte.'Historique des activités 1:N'.'Date du rendez-vous'
      

      "Date du rendez-vous" is an appointment field (with date and time range). I will therefore have to take into account the date and the start time (at least the date if the time cannot be taken into account).

      I know there is something like "order by" but I don't know where to put it in the formula.

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

       Ideally, I would also like to be able to filter rows and display only those that do not have Status 5.

      Status can be queried at: Compte.'Historique des activités 1:N'.'Statut' != 5

    • Fred
    • 1 yr ago
    • Reported - view
     said:
    I know there is something like "order by" but I don't know where to put it in the formula.

     You would need to order the initial gather of data.

    let gatherData := (Compte.'Historique des activités 1:N' order by 'Date du rendez-vous');

     

     said:
    Status can be queried at: Compte.'Historique des activités 1:N'.'Statut' != 5

     Again you would filter at the initial gather step.

    let gatherData := (Compte.'Historique des activités 1:N'[Statut != 5] order by 'Date du rendez-vous');
    
      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

       THANKS ! Except for sorting by date, everything works fine.

      The descending sort by date from the appointment field named "Date du rendez-vous" does not work.

      I tried with rsort() but nothing worked.

      I made sure to enter the formula like this so that only the first date (start date) of the appointment field is taken into account (and not the end date): date('Date du rendez-vous') but the trie remains ascending (oldest up and newest down).

      If it's too complicated, I'll stay like that. If you have a simple trick, I would be delighted to know it.

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    order by -number(Id) should work if the last record holds the latest date as in my example code.

    • Fred
    • 1 yr ago
    • Reported - view
    Sébastien Guillet said:
    I tried with rsort() but nothing worked.

     Yeah, you would think rsort() would take your sort order and reverse it, but it seems to reverse the record Id regardless of the original sort.

    Luckily you can just put a minus sign in front of the field name after "order by". Yeah, it is that simple. It only works with number and date fields. It would look like:

    let gatherData := (Compte.'Historique des activités 1:N'[Statut != 5] order by -'Date du rendez-vous');
    
    

    If the records are in order but just in reverse to what you want then you can just change your slice:

    let gatherData := (Compte.'Historique des activités 1:N'[Statut != 5] order by 'Date du rendez-vous');
    let cntgather := cnt(gatherData);
    let first5 := slice(gatherData, 0, 5)
    join(first5.'Nom concaténé',"
    ")

    Now you are getting the first 5 records.

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

      By adding the - sign and correcting the rest of the code as you suggest, it does bring out the last 5 records which are sorted in descending order. Many thanks for your help  and

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 10Replies
  • 72Views
  • 3 Following