I’m struggling with this one. Here’s my use case:
I have a form that is capturing responses to questions from users.
The form takes the data, and generates scores for each answer.
For the lowest 5 scores, I want to make recommendations on how to improve their score.
Each recommendation has 4 possible options, basically none, low, medium, high.
For example if they scored low, they would get the recommendation for a low score.
If they scored high, they would get the recommendation for the high score.
I have two tables, 1 for form submissions, that calculates the scores and creates a list of recommendation KEYS per user ie. (1a-low, 2b-high, 3c-none, 4d-low, 5e-medium).
The 2nd table is a list of all possible recommendations. This table has about 12 columns of data (Key, Title, Description, Blurb, Action 1, URL 1, Action 2, URL 2, etc).
For each user, I want to display the 5 recommendations on a Detail view. The user should only see the 5 recommendations for them, based on 5 recommendations the app calculates.
I have attempted to do a REF, but the REF shows all recommendations and the users that received those recommendations. I put a filter on it by email, but then I still see all possible recommendations, and not just the 5 that are identified.
If I put the recommendations in a separate slice, then I get the desired results. So one possibly thought would be to create a new table for each user, and capture the recommendations for that user in that table. However I’m guessing this isn’t the best approach as with more users, I’d end up with more tables. Maybe virtual columns could help as well?
With REF I get this:
With a table I get this:
How do I get the results of a table, without creating a table for every user?