Pass current viewing record Id to a view element column formula

Now that this post has been revived, I can share what I have figured out in the mean time.

As some of you know I have a sports DB. Which means there are lots of stats that need to be generated.

I have created dashboards to get insight into my data, which means I have many views on the dashboards. We can take one example. I track two team show jumping leagues. So one of my dashboards is a team level comparison.

I have the following tables:

Team

League Dashboard

On my League dashboard I have the following fields:

League

Season

City

Each of these fields affect the data shown on the dashboard.

I have a view element that gathers the appropriate team records from the Team table. Easy. At first I would create a column for each stat. One of them is Average Penalty (or AvP). With the three fields above I had to add logic to each formula column in the view to keep track of it. Which means if I had to modify my code I had to do it to each column.

For example each column would have something like:

let xDsh := first(select 'League Dashboard');
let xLeague := record(Leagues,number(xDsh.League);
let xSeason := record(Seasons,number(xDsh.Season));
let xCity := record(City,number(xDsh.City));
switch xDsh.mode do
case 1:
    (select Results[League = xLeague and Year_calc = xSeason.Year]).Penatlty4Average)
case 4:
    select Results[League = xLeague and 'City Name' = xCity.'City Name']
case 5:
    select Results[League = xLeague and Year_calc = xSeason.Year and 'City Name' = xCity.'City Name']
default:
    select Results[League = xLeague and ]
end

In addition I learned that this method also had a high overhead and would cause some dashboards to take 30 seconds or more to load.

Thinking that I was already in the Team table I thought I would move all of the work load to that table.

So I added a tab to my Team table form, called it Stats, then created the following fields:

allResults

ATDResults

ATDAvP

There are many more, but you get the idea.

The allResults field is where I do the initial data gather. I don't need a select as I have links here but you can if you don't already have the initial link.

Then in ATDResults, I put:

let xDsh := first(select 'League Dashboard');
let xSeason := record(Seasons,number(xDsh.Season));
let xCity := record(City,number(xDsh.City));
switch xDsh.mode do
case 1:
    allResults[Year_calc = xSeason.Year]
case 4:
    allResults['City Name' = xCity.'City Name']
case 5:
    allResults[Year_calc = xSeason.Year and 'City Name' = xCity.'City Name']
default:
    allResults
end

Then in ATDAvP I put:

avg(ATDResults.Penalty4Average,1)

Then back in my League dashboard in the view element for Teams I can select the field ATDAVP as a column choice.

Now whatever I choose for the fields League, Seasons, or City, all my stats will change appropriately. If I need to change my code because I want to add a new selection then I only need to update ATDResults instead of each stat field.

I hope this helps and I look forward to seeing what others have figured out.