Filtering table lookups based on matches

I am looking for some help on this formula, Im not sure how to do it or even if my topic subject is correct.

Below I am creating a Leaderboard.
I am attempting to populate each teams score in the [leaderboard.score] column.
I have 2 tables relating to each team with their team name,owner and score. I am trying to filter the result to find the table that matches the team name and grab the score and populate the leaderboard.score column with the teams score.

Dear @joneswa27

Great having you in this community:

Concerning your question, pls see the sample doc below:

In your case it’s the best to have all teams and all owners/members in one table.
Either by grouping you get the quick result / overview or as you wished in a seperate overview table to extract the info from the main table.

Hi @Jean_Pierre_Traets, thank you very much for your reply.
In my case i can’t (well not that i am aware of) have all the source data in the same table. for this example i put these tables in the same section, but in my case these tables are in different sections in my doc.

Hi,

I have made 2 dedicated sections for your question, just to show that from the main table you can create a view that shows ONLY the dedicated info necessary or relevant for the audience/task.

:bulb:
A good practice is to "hide"your main table in one “admin” section and in other sections you can create views (by filtering / hiding columns) not relevant. I strongly recommend to have a look in the template section to get a better understanding how to approach.

This feels kind of dirty to me, but I’ll suggest it anyway just as a quick solution (in the event that one hasn’t been found yet).

You could have a table of Teams that maps a team name (from the Leaderboard view) to a reference to the team’s table. Your score column in leaderboard can then filter the contents of that table to only include the Team Name that appears in Leaderboard, and then use the reference to the table to actually get the score.

Note: This is assuming that you are avoiding hard-coding for some reason.
Another note: I think that aggregating your data into a single table and creating a view of it is probably going to provide you with the most flexible solutions and prevent the most future frustration, but I trust you when you say you can’t do that.

Hey @Jean_Pierre_Traets.
Yeah i see what you have done. This does not work in my scenario

In my scenario each team has their own table which has calculated their overall score.
the overall score table is names the same as the column [leaderboard.team name]. i want to lookup and match the teamname to the table of the same name then pull the score.

How do i reference the teams table??

I managed to get the initial result I am after using a similar method from this link.

Definitely not pretty and I will tune it as i tidy up the schema and develop my doc.