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?
Also trying to do an expression that automatically filters the given rows based on the userโs selections. Seems like that might be a viable option.
If Iโve interpreted your use case correctly, Iโd think youโd want to create a slice on the Recommendations table with a row filter along the lines of:
IN([Key], LOOKUP(USEREMAIL(), "Submissions", "Email", "Recommendations"))
Submissions
is whatever the name of the for submissions table or slice is.
Email
is whatever the name of the column in the Submissions table is that contains the submitterโs email address.
Recommendations
is whatever the name of the column in the Submissions table is that contains each rowโs calculated โlist of recommendation KEYS per userโ.
LOOKUP(USEREMAIL(), ...)
gets the recommendation list for the current logged-in user from their submission.
IN([Key], ...)
asks, is the row key ([Key]
) this row (of the Recommendations table upon which the slice is layered) contained in the userโs recommendation list gathered by (4).
See also:
@Steve your recommendation is so so so very close. It does what I need with one general challenge.
Here is the actual formula Iโm using:
IN([Maturity Key], LOOKUP(USEREMAIL(), "Compass Form Responses",
"Email", "Maturity List"))
The โMaturity Listโ is a list of โMaturity Keyโ that looks like this: "0Gov , 0Sel , 0Tal , 1Clo , 1Lif"
After the filter expression above, I only get back the details for the first โMaturity Keyโ in the list โ0Govโ
However, when I run a test on the expression, I get confirmation that each of the 5 rows return a โYโ - so the test appears to be working, but not the View of each row.
To clarify, thereโs no way to combine the details of the five rows into a single view. Instead, you can either present the rows in an aggregate view (deck, gallery, or table), from which the user would click through to get details on individual recommendations, or use a detail view with slideshow mode on to display the details of each row individually, giving the user the ability to swipe left and right to move between them.
Yes, thatโs what Iโm trying to do. What Iโm saying is, the formula above when I TEST the formula, it successfully identifies the 5 rows that should be displayed after filtering.
BUT when setup in a detail view with slideshow mode on, it will only display 1 of the 5 rows. I canโt swipe left or right to see other view of the next row. Or if I use a table, gallery, etc view, same thing, I only get 1 out of the 5 rows.
Please post screenshots of the following from the app editor:
I added a test view from the Expression Assistant. You can see the 2 "Y"s that show there should be multiple rows.
Slice Config
Slice Formula
Slice test results
View Detail
View Table
That is definitely confusing. Try clicking View Data at the top of the slice configuration. Does that show one, or five rows?
Yes, all 5 are shown. Screenshot below.
Iโm at a loss, then! Iโm going to have to refer you to support@appsheet.com for further help.
Yep. Thanks for walking through it.
User | Count |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |