Copy a Column Value to another Column without Automation


First off, I know this can be done with an automation, but I’ve noticed that automations can sometimes take up to 30 minutes or more to trigger, which for this instance won’t work.

I am looking to make a check in system where a person pulls up the form, and all they have to enter is their email and the “session” they are checking into is already filled in based on time. I have a formula that grabs the current session based on what time you’re looking at the form.

I have two tables, schedule and check in form:

Table one:

Table two:

So what happens is the current session column in the Check in Form table is already populated. But since it changes, I need a way to capture what the value was when the form was submitted. I have an automation set up right now for when the Current Session Column changes, it takes that value and enters it into the Checked in for column.

The issue I’m trying to avoid is having the automation take too long to trigger that the Current Session value changes. I’ve done some testing and it can take a long time for the automations to trigger (40 min+)

So is there a way to capture the Current Session column and put it in the Checked in for column without an automation? I tried using the Value for New Row option but that doesn’t work at all like how I expected it to. I tried different ways to Concatenate it with other strings, but it always ends up changing to whatever the actual current session is, and not what it was when the form was submitted.

Any suggestions would be appreciated, thanks!

Did you consider adding button to your Form with value of Submit or something like that, that will contain logic you need, and that user will press when finished with filling up the form? (if you are using Coda to collect data, if you are using some 3rd party form integration than this approach will not work)

Thanks for the suggestion. I want to just use Coda’s Form feature. I’ll test this out but it might be a bit strange to have two buttons though. But I will let you know!

Hi @Samuel_Langford :blush: !

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 :innocent: )

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 :blush:

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 :innocent: )

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 Opening and Closing fields in the table Event :blush:

Just under the callout, there’s a [View of Form Table] (so, the form for the submissions) and under it, its corresponding table [Form 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 → PropertiesCreated 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 :blush: ).

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

(Note that Opening is in fact CurrentValue.Opening and Closing is CurrentValue.Closing)

And what it does is : It takes the table Event and keep only the row where CurrentValue.Opening is less or equal to thisRow.Created AND also where CurrentValue.Closing is strictly greater than thisRow.Created.
And because Filter() always returns a list of rows, .First is actually there to extract the row from that list :blush: .

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 :smiling_face: )

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 [Event was].

Its Action formula is this one:

  thisRow.[Event was],

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 :blush: ).

Because the Event is stored in the lookup field, the delay that might occur with the automation shouldn’t be such an issue :innocent:

And the automation in itself just looks like this :

This is all just an idea :innocent: !
I hope this helps a little :smiling_face: .

1 Like

Thanks for the thoughtful and detailed response!
I’m not gonna lie, I don’t 100% the solution, but I think basically you’re saying to have a column that filters the session table based on the time of the checked in session. Which I tried to get working but my formula was off, I’ll tamper with it.

Your second solution also uses automations, which I want to avoid because when sometimes they can take so long to trigger (30 mins+) that the session might change between form submission and the automation firing.

But, a new wrench appears. My Form can’t see the the Sessions table, even though I have the lookup option checked. So this whole thing might be a bust :confused:

Based on the date and time the form was submitted (adding a row to the table), yes :blush: .

The time it might take to the automation to be triggered and do what it needs to do doesn’t really matter in the 2nd sample, as long as there’s a event in the lookup field and its value can be used by the button :smiling_face:

Maybe you could share a sample doc reproducing your actual setup, so we could take a look ? :blush:

Thanks for you help! Here is a link to a demo page: Copy of Attendance Testing

Hi @Samuel_Langford :blush: !

So, I took a look and stumbled upon a Schedule problem I actually have no idea how to approach: There are check ins overlaps in your Schedule table :no_mouth:

I highlighted those in the sample doc you shared in the lookup field [Check in overlap] in your table Schedule where you’ll see that for ~10 mins, Event C overlaps Event B and Event D overlaps Event C (again for about ~10 mins).

Your Current Session canvas formula also shows this.

This means that, even before I made any suggestions, a person checking in for Event B (for example) through the form, at some point in time (corresponding to the [Early Check In] of Event C) and during the ~10 mins remaining for the [Late Check In] of Event B, would see suggested as Current Session :

Event B, Event C

… Until only Event C remains…

In a nutshell, a person using the form could be checking in late for Event B or early for Event C.

So I don’t think that checking in by entering just an email would be sufficient or work as expected :no_mouth:.

The only option I saw was to:

  • Add a “message info” (it’s optional) in a text field (simply called Info) in the table [Check in Form Data]
  • Add a single-select lookup field (named [Checking In For]) from your Schedule table to the Check in Form Data table and add a filter for its selectable options in Lookup optionsOption settingsFilter so the only selectable options available on the form would be the one(s) where :
    [Early Check In] < Now("minute") AND [Late Check In] > Now("minute")

You’ll find the corresponding form ([View 2 of Check in Form]) just below the first one in the sample doc you shared :blush:

Thanks again Pch.

I should mention that the times in this are just for an example, the actual schedule won’t have that issue. I’m more concerned about the lookup column issue.

If you’re sure about that then, you shouldn’t worry about the lookup :blush:

The lookup is not used in the form, at submission. It’s there to retrieve the appropriate event from the Schedule table after the form has been submitted :blush: .

So, in your form, you can keep the text field displaying the Current Session and the Email field the person needs to filled out before submitted the form.

Once the form is submitted and appears in the table [Check in Form Data], the lookup field will compare the values in your fields [Early Check In] and [Late Check In] (from your Schedule table) to the value in thisRow.[Date and time] in your table [Check in Form Data] and retrieve the appropriate event.

As your field [Date and time] in the [Check in Form Data] stores the moment when the form was submitted and a person can only check in for a event during a specific timeframe depending on when Now() falls between an [Early Check In] and [Late Check In], this means that the moment the table [Check in Form Data] registers the submission and a brand new row is created in the table should also only falls within the same timeframe (so between an [Early Check In] and [Late Check In]).

So, by comparing the value in thisRow.[Date and time] to [Early Check In] and [Late Check In] afterwards, you can actually retrieve the event.

From the lookup you’ll see in the table [Check in Form Data], you can then simply ask an automation to “extract/copy” the “name” of the event (if you don’t intend to keep the schedule as it is but still want to keep track of those events in some way) instead of taking it from the dynamic [Current Session] field in the table [Check in Form Data].

As the lookup output a fixed value (by a formula) the time it might take to the automation to do what needs to be done shouldn’t be an issue anymore :blush:

I hope, this makes at least a little of sense :sweat_smile:

So, here’s a copy of your sample doc I modified according to this :blush:
(Added the lookup field, and updated the automation)

You can copy it using the green button on top of the page so you can explore it at your own pace :blush:

A big caveat here could still be “human errors” (mainly, a person could still forget to submit the form after filling it or suddenly encountering troubles with their Wi-Fi… but that could have been true for your initial setup too (if it would take such a long to the automation to do what needed to be done))

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.