Multiple references to generate single view

[ChrisHood1]

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?

[ChrisHood1]

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.

[Steve]

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"))

1. Submissions is whatever the name of the for submissions table or slice is.

2. Email is whatever the name of the column in the Submissions table is that contains the submitter’s email address.

3. 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”.

4. LOOKUP(USEREMAIL(), ...) gets the recommendation list for the current logged-in user from their submission.

5. 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:

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

USEREMAIL()

User's email address

help.appsheet.com

Slice Row Filter Conditions

Explore how to use row filter conditions to build different slices of data.

[ChrisHood1]

@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.

[Steve]

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.

[ChrisHood1]

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.

[Steve]

Please post screenshots of the following from the app editor:

1. The slice configuration.
2. The slice’s row filter expression from Expression Assistant.
3. The first page of the detail view configuration.
4. The first page of the table view configuration.

[ChrisHood1]

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

[Steve]

That is definitely confusing. Try clicking View Data at the top of the slice configuration. Does that show one, or five rows?

[ChrisHood1]

Yes, all 5 are shown. Screenshot below.

[Steve]

I’m at a loss, then! I’m going to have to refer you to support@appsheet.com for further help.

[ChrisHood1]

Yep. Thanks for walking through it.