0

Showing top performers based on linked records

Okay so

 

I have 3 tables:

- Coaches

- Challenges

- - Participants

 

I then have a dashboard table. 

I currently have a view with the following formula:

 

let m := Month;
(select Coaches).Participants[Challenges.Month = m]

 

I then 'count' the month column of the view, which tells me how many participants each coach has. My problem is that I cant sort that high-low since I'm using the view filter, and not a formula.

 

So I'm trying to figure out the formula for the view and an additional formula to:

 

- Show in the view, a list of coaches that have participants in the specified month

- in a formula column, show the total # of participants so I can sort high -> low

 

And my brain just keeps short circuiting no matter what I try. Halp! TIA!

2 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    Before going further: it seems that there is a N:1 relation between "Participants" and "Challenges", which means that a participant can only... participate in one challenge. Right?

    • Fred
    • 2 yrs ago
    • Reported - view

    Just to verify that the following is true:

     

    Participants table has a reference field to Coaches. Since your View element formula has select Coaches.Participants. So each Participant record has a record in Coaches related to it.

     

    Then you can create a formula in your View element:

     

    cnt(Participants)

     

    Since you are in a View element and the formula calls on the Coaches table, you are now effectively in the Coaches table. Each line in your View element is a record in the Coaches table. Now we take advantage of the link between Participants and Coaches. Ninox will automagically find only Participants that are already linked to the record in Coaches.

     

    Good luck and let us know how it goes.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 2Replies
  • 183Views