Hi @Samuel_Langford !
If I understand correctly your setup, when a person submit a form, you have a field telling them which event they are checking in.
And this is probably done using a formula based on
Now() and the values in your fields
[Early Check In] and
[Late Check In] (this is all just a guess )
But, this also means that when the form is submitted and a row is added the corresponding table, the moment the row is created (which is a row property) could help you to retrieve the appropriate event from the table storing all your events
I mean, if you can retrieve the current event before submission and assuming a person would submit the form directly once it has been filled out, the event the person checked in shouldn’t have changed between the moment the form gets filled out and the row is created in the corresponding table (there might still be a slim chance that this would happen though )
So, in the sample you’ll find below, you’ll see a callout displayed the “Current Event” (within a canvas formula named
Current Event ) based on the values in the
Closing fields in the table
Just under the callout, there’s a
[View of Form Table] (so, the form for the submissions) and under it, its corresponding table
In the table
[Form Table], you’ll see a
Date/Time field called
Created which is there to simply store the moment
thisRow was created (i.e.: a form was submitted).
As it’s a row property, this can be easily added through the field’s menu →
Created on, when you add a field to a table.
You’ll also see a
Current Event field which just display the value from the canvas formula
Current Event (in the callout), so this value is only calculated once (as I didn’t know how you were calculating yours, I chose this way ).
And there’s the
Event single select lookup which is a lookup from the table
Event to the table
[Form Table], which retrieve the event depending on the value within
thisRow.Created using this formula :
Opening <= thisRow.Created
And Closing > thisRow.Created
Opening is in fact
And what it does is : It takes the table
Event and keep only the row where
CurrentValue.Opening is less or equal to
AND also where
CurrentValue.Closing is strictly greater than
Filter() always returns a list of rows,
.First is actually there to extract the row from that list .
Now the caveat with this approach is that to keep the link alive (and therefore, having the lookup displaying the appropriate event), you can’t edit the table storing the events (the values within your fields
Early Check In and
Late Check In) or simply delete an event from the table.
In both cases, this might broke the link or lead to erroneous results.
(Unless it doesn’t really matter and you intend to keep all the events in your table )
But, just in case, here’s a slightly modified version of the sample where I’ve added a button (
Get Event) to simply copy the text value from event currently stored within
thisRow.Event and “copy” it in the field
Its Action formula is this one:
The button is pushed through an automation on form submission and disabled once
thisRow.[Event was].IsNotBlank() (so when the button has fulfilled its purpose ).
Event is stored in the lookup field, the delay that might occur with the automation shouldn’t be such an issue
And the automation in itself just looks like this :
This is all just an idea !
I hope this helps a little .