Access to ID several levels deep

Ask for Help
1

Hi everyone.
I am challenging myself to make an app using glide tables only as much as possible.

I am trying to make a walking tours app that has questions at each location.

For that I want to have a page for tour listings where the user can access some tour details and enroll in it (save it to their tours)

From their saved tours, they access the tour page and can trigger a start with a start time (tours have to be completed within 24h) and this will reveal the locations.
The page presents a list of locations where they can press to go into the location detail where they can find a way to get directions, some fun facts and a button that opens a form with the question and the answers.
At this point when they are writing the answer to the sheet through the form, I need to write the Enrolment ID, as it needs to belong to this specific entry.

The trouble I am having is not being able to get the enrolment ID at this point.
I’ve tried to illustrate things in the following images (I am naming static db something that only admins can add to and dynamic the ones where users can add stuff)

Am I missing something really obvious?

Screenshot 2020-09-17 at 11.40.48
Screenshot 2020-09-17 at 11.40.482476×1064 159 KB
Screenshot 2020-09-17 at 11.40.56
Screenshot 2020-09-17 at 11.40.563436×852 274 KB
Screenshot 2020-09-17 at 11.43.58
Screenshot 2020-09-17 at 11.43.582892×782 204 KB

2

Each combination of enrollment ID - user email - location should be unique, is that correct?

I imagine you can do a template of user email and location in the answers to get back the enrollment ID.

3

Yes, each enrollment ID - user email - location should be unique because users can enroll multiple times. so the enrolment ID makes it unique.

I didn’t really understand what you meant. sorry

1 Like
4

You should be able to

  • Create a template column in the enrollment sheet that joins the submitted email and tour id (test@email.com - T12345)
  • Create a self multiple relation in the enrollments sheet that uses the template column and links back to itself.
  • Create a rollup column to get the max enrollment date.
  • Create an If Then column to return ‘true’ if the enrollment date matches the rollup enrollment date.
  • Create a template in the tours sheet that joins signed in user email from the user profile sheet, the Tour ID, and the value ‘true’ (test@email.com - T12345 - true).
  • Create a similar template in the enrollment sheet that joins the submitted user email, tour id, and the if then column that is true or false depending on it it’s the latest enrollment for that user/tour.
  • Create a single relation in the tours sheet that links the template you created to the template in the enrollment sheet.
  • Create a lookup column to get the matching enrollment id.
  • This will give you the latest enrollment for that user/tour that you can then pass into the form through column values.
2 Likes
5

Sorry, probably badly explained but users can enrol at anytime. These walks are self guided. But I think the rest gives me the answer.
Thank you

1 Like
6

I threw in the max date check to future proof it if a user enrolls in the same tour multiple times. This is just to ensure that answers are applying to the latest enrollment of that tour.

1 Like
7

I was going through your instructions, but the following point has thrown me off.
The Tours sheet doesn’t have a user column.

8

That’s why I say to use the template column to get the signed in user’s email from the user profile sheet. Set up a replacement value in the template and from the dropdown for replacements, select the user profile email.

9

Sorry, I wasn’t using user profiles. Am now.

1 Like