Problem with lookup

I have 3 tables: Users (with primary key UserID as number variable, no duplicates allowed), Events (with primary key EventID as sequence variable) and Bookings (BookingID as sequence). I want to make a flow part that, for a given user, gives a list of events to which that user has been booked. First I made a lookup for UserID in the Bookings table, which gives me a list of bookings for that user. That works fine. But then I want to create a new lookup that matches the EventID values from the Bookings list created before to the EventID from the Events table, and it doesn’t let me do that. I add a new data lookup step in the flow part, choose “Events” as the lookup table, then I try to create the expression “Events / EventID = [Property of flow part] ‘Events per user’ / EventID”. When I select “Events / EventID” on one side I can’t choose ‘Property of flow part’ on the other side, and vice versa. Am I doing something wrong? I tried the same table structure in Access and it lets me make the query just fine. Thanks in advance!

1 Like

Hi @sjoerd.broekstra,

Thanks for working with Triggre. From your question I can see that you have experience with databases. Triggre works differently.

Triggre abstracts away from using primary and other keys. What you probably want to do, because it works much faster, is build the following data structure instead:

  1. User (already available)
  2. Event
  3. Booking

Instead of making keys, you want to use References. This is Triggre’s way to handle all the technical stuff with primary and foreign keys for you. I believe you are looking to create the following references:

  1. User / Bookings → Booking / Booked by
  2. Event / Bookings → Booking / Event

Then you don’t have to do hard work in the lookups, since you can simply expand the relations of items.

I highly recommend you do the Exercises in the Triggre Designer, where you learn to use these concepts in Triggre step-by-step.

Alternatively, you can check out our Knowledge Base. I think this article will help you with references in a good way: References between data items

Hope this helps. Happy building!

Thanks for your reply, this helps a lot!

About the exercises: I had completed all the exercises 2 months ago, but I guess I forgot about the references. Unfortunately, you can’t access them anymore once they have been completed. I would be very useful to be able to review them.

Good point indeed about the exercises. We are already working on making them re-doable, as we got this request more often.

Glad that at least I could provide some help in this way!

1 Like