Updating an existing record through a form- a few issues

[Limi]

I have a table (Table #1) in which we keep upcoming events, along with their dates, topic, marketing content etc. I created a mirror table (Table #2) with the same fields. Table #2 has a form our staff uses when they need to update any of the events that are in Table #1 (existing records). I created an automation that when a form is submitted in table #2, it finds the records in table #1 that match the name of the program and then updates the existing record.

Here's where I am stuck and wondering if there are solutions:

1) Finding the records: My conditions are the name of the program and the date, but the issue is with the date. I'd like Airtable to only find it if it's the exact date in both tables and not a range of dates. Is there a way to do this?

2) Updating the records: when staff submits a form, they may ask for the topic (field) to be updated, but will leave the content (another field) blank because that didnt change in their program. When the automation find the record and updates it, i dont want it to override existing content with the blank content. Is there a way to have additional automation logic that basically says, updates this record only with the form fields that weren't submitted empty?

[Stephen_Orr1]

Matching on names and dates isn't 100% reliable but might serve your purpose fine. Another approach is to put the linked record field between the two tables on the form so that updaters can choose an existing event. Your automation could then process the form such that if there is linked record, update it with the form values, otherwise, create a new event. You can specify which fields to update in the Update Records action and also make certain fields required on your form to prevent needless update submissions.

Hope that helps!

[Limi]

Thanks!! Can you clarify what you mean when you say "put the linked record field between the two tables on the form"? Do you mean to create a 3rd table that all the records from table 1 will be linked in? Then the form on table 2 pulls from that? 

[Stephen_Orr1]

Sure! You have two tables:
Events - every record is a unique event
Even Updates - every record is an update/create submission for an event

You have a linked record field between these two tables that you can physically place on your form so that submitters to Event Updates can choose an existing Event (from the Events table) to update. Your automation can include a conditional action that updates or creates record in Events based on this field.

If you have a truly unique identifier for Events (not name + date... as uniqueness can't be certain), you could use this identifier instead and not require form submitters select an Event in the linked record field as a question on the form. This might be better if your form submitters are bad at checking for existing Events when submitting a form. In that case, I might assign an Event ID and try to socialize the concept with the team.

Please let me know if you have any other questions 🙂