Filtering linked fields of a record

Janna_Moskaluk · ‎Nov 08, 2019

Hey :slightly_smiling_face:

I have 2 tables, 1 is ‘Sessions’ and the other one is ‘Athletes’.

Each session that an athlete does, is entered to the Sessions table, with a link to the Athlete’s specific record in the Athletes table.

I would like to make some aggregations for each athlete, based on sessions he had during the week previous to this session, 2 weeks, 3 weeks and a month (not to TODAY() but to the ‘Day’ field of the session)

What I did is that I created a lookup in Sessions table, to look up the “Sessions” field in the Athletes table, which, for each athlete, provides all the sessions that this athlete did.

What I want to do is to filter these records based on the day/week of the session, so that I could “leave” only the ones that were on the week previous to this specific session, and then use rollup on these record to create a SUM on some metric from the Sessions table.

Is this possible in some way?

W_Vann_Hall · ‎Nov 08, 2019

Yes — but it takes a little clever linking; the results are viewable only from the [Athlete] table; and [I suspect] only certain kinds of calculations will work.

Here is a link to a demo base I tossed together. (That’s a read-only link; open it and copy the base to your workspace so you can access the formula fields.)

I think I built the basic structure according to your base. Each record in the [Athlete] table links to one or more records in the [Sessions] table. Since I don’t know what metric you’re tracking, I simply added a field called {Time} in which I’ve entered random durations in ‘mm:ss’ format. I’ve also included a {Date} field for the session.

From [Athlete], I’ve created a rollup field called {Last Session}. It rolls up all the {Date}s for sessions associated with a given athlete using the aggregation function MAX(values). This gives me the date of the most recent session. Each record in the [Session] table also has a {Last Session} field, which simply looks up the value of {Athlete::Last Session}.

{Session::Last Session} is used in two formula fields. First, {Last Time} checks to see if the [Session] record represents the most recent one for that athlete. If so, it duplicates the session time using the formula

IF(
    {Date}={Last session},
    Time
    )

In turn, {Athelete::Last Time} looks up that value so the [Athlete] record displays the latest time.

Second, in [Session], {Past Week} duplicates the {Time} values for all records with a datestamp within a week of {Last Time} using this formula:

IF(
    DATETIME_DIFF(
        {Last session},
        {Date},
        'w'
        )<1,
    Time
    )

Finally, for the demonstration I calculate the average time for all sessions occurring in the week prior to the most recent session by rolling up {Session::Past Week} with an AVG(values) aggregation formula.

If you want, you can track a rolling performance over previous weeks by defining comparable fields to {Past Week} that look for sessions where DATETIME_DIFF() is between 1 and 2, 2 and 3, and so on, along with corresponding averaging rollup fields in [Athlete].

This sort of structure should support most needs. For instance, by using MAX(values) and MIN(values) to aggregate a rollup of {Past Week}, you can identify high and low performance marks for that week, you can COUNT() the number of sessions, and so on.

Everything else should be self-explanatory — with the possible exception of the <Blank Times> view in the [Session] table. That’s simply a view that displays only sessions where no time has been assigned. Since any completed session should have a time, this allows me to configure the link from [Athlete] to [Session]to display only records from theview --- which should be *no* records. This keeps the use from being confronted with --- and possibly incorrectly selecting --- an existing{Session]record when creating a new session. It *does* mean session records can effectively be modified *only* from the[Session]` table, though.