Check if Record Exists before Updating

Help Center Get Answers
1

Hello,

The subject is the most concise version of what I am trying to do but here are some more details in case there is an easier way to do this I am not thinking of.

I have an Events Object and a I have a Location Object. Each event is connected to a location. A location can have multiple events on 1 day but each requires it’s own registration so they are each their own event in the events object.

When users are browsing events to register. I would like them to be able to browse by date and then when they click for details they can see the location and all of the events available for registration on that date.

So I would like the users to see a list view of dates with the location but I would only like each date to appear once for that studio. Here are some image examples of how I would like the listings to appear for users.

example
example917×382 10.8 KB

I don’t want the location to appear 3 times if there are 3 events at that location on that date.

SO I created an Event Dates Object with a date field and a connection to the location (location to many event dates) and a connection to the event (many events to one date).

So my plan was to have when an Event is added or edited it inserts or updates that Event Dates object. So I would need to check first if that date is in the Event Dates object and if the location was the same as the location connected to the event being added. If it does exist, update that record with the event, if it does not, create a new record with the date, studio and event. The plan being I can use that to do the listings and use the connected records for the filtering.

So I just need assistance doing that check if that date + location combination already exists and then perform an update or insert depending.

Step by Step:

Administrator Adds or Updates event in Event Object and sets the date/time and connected Location Object

Check if Event Date exists in Event Dates Object

IF No
Insert new record into Event Date Object with date, connected location and connected event Finish

IF Yes
Check if Location matches connected Event’s location

IF No
Insert new record into Event Dates object with date, connected location and connected event Finish

IF Yes
Update Event Dates Record with new event Finish

I would appreciate any help or insight or any other way do to do what I am looking for. It is worth noting that each event has variables that need to be used in filters so just adding some sort of dates to the location for listing would not work since it also has to retrieve the info from those connected events.

2

That’s a lot to unpack :stuck_out_tongue_closed_eyes:, especially at the end of a busy working day.
Initial thoughts are that you either have to do the validation with code or take it out to Integromat to do all the route logic. As I’m not a coder I’d look to use Integromat to lookup the relevant records and the update or creat new.

Bit too complex for me to visualise and supply a specific answer but happy to connect if you want to discuss and walk me through.

https://www.knack.com/experts#expert-directory/expert-details2/605b4a9dca4b85001bddc6da/

carlholmeschps@gmail.com

3

I think you could fill your event date table for example for 1 year with a field: Available of type yes or no, by default to yes. When an event is scheduled for this date the value changes to no. For display the data int a table you group your data by location then by date.
On the field Available you use a link to a new page to fill out the form.

Location 1
date: 12/10/2021
Hours Event Available
3:30 PM event 1 Yes ( when you click on yes, it open a form to add a new event)
4:00 PM event 2 No

You could use Integromat to fill the event table.
If you want to present your data by location or date, you can use 2 buttons that bring you to different views.

4

Thank you, the listings are working as I want with the objects set up this way so getting the records in correctly is the last piece of the puzzle for me.