Establishing a relation based on a key phrase

Hello! I have a sheet of events to display in an app, and for v1 of the app I simply associated an image with each event. This was an inefficient way of doing things, as some events ran multiple times and so the images were duplicated in rows (ugh, I know…).

I’m trying to revisit the app and build in some sustainability and efficiency, so naturally I’ve moved the event images to a different sheet, which has the event title and an associated image - so I can establish a Relation where the event title matches and then Lookup the image. That is standard and easily doable.

My challenge is that the event titles may change slightly each time the event is run - for example “School leaders briefing March 2023” could be “School leaders briefing Jan 2024” in future. With this in mind I’d like to tweak the relation so it looks for a key phrase. My “event images” sheet looks like this with phrases which will be appropriately targeted, and will crop up in future events, but aren’t so specific that they will only work once:

What’s my most efficient route to making this work? I’ve considered Is Truthy and Check Text Matches but the documentation for those is… sparse at the moment as far as I can tell. Is there a simpler way to achieve this?

Thanks for reading :pray:

Add a RowID to your Events table, and use this as an EventID. The RowID will never change, regardless of the event name. And then in your Course Images table, instead of storing the Event Name, store the EventID and use that to create your relation to the Events table.

This is standard best practice.

oh, wait… I read your post too quickly the first time.

Okay… you need to associate multiple events with a single image.

Do as I said above, but instead of storing a single EventID in the Course Images table, store a comma separated list of EventIDs. You can then convert those into an array using a split text column, and relate to that from your Events table using a single relation, then use a lookup column to fetch the image.


Thanks @Darren_Murphy that’s really helpful. :pray:

I think my “wouldn’t it be nice…” approach is that if I can have a phrase related to an image (“Outdoor play”, “Termly briefing”) then I can move away from having to add the EventIDs you describe to the comma-separated list of IDs.

In my mind (where this works, flawlessly, obvs :roll_eyes:) I’d be looking at a regex-flavoured relation / lookup which said

where an event starts with / contains
Event phrase
then add
Image associated with phrase
to that event.

I kind of have a similar situation. In my case, I have annual competitions at different locations. The competition name at each location is the same from year to year, with the exception of a year tagged onto it. In my case, I use a Replace All column with some RegEx to strip off any numbers, so I end up with the competition name without the year. Then I use that for a relation to my images table.

Something similar could be done with a template column or other various columns. It’s not 100% perfect though, but in my case it works well enough. Sometimes the user will vary the name a little bit so I have to create another entry in the image table.