Fill Next Empty Column

I am working on a massive base that has multiple columns and tables.

I want to create a form that allows users to fill in information that will:

Pick Main Record to Update
Select an item from a list

This is tricky part - once the form is submitted it will look at the Main Record selected to update and I need it to update the first blank defined column in that record.

i.e.
First Form Submission - Order 1 Column in Main Record = selected Item from list
Second Form Submission - Order 2 Column in Main Record = selected item from list

The caveat is that the form will be submitted by a few people at random times. The only important thing is that as the forms come in it updates the next Order # column in the main record.

If you want the records linked to each “Main Record” to have auto-increment from 1 onwards to whatever the current “order number” is then follow the advice linked in the thread below:

Thanks but think about it as one record

Todays Date -

Todays Winners Submitted By form over the course of the day

Form 1 Submission - Jason Winner
Form 2 Submission - Staci Winner

So Todays Date record is updated with

Game 1 - Column - Jason
Game 2 - Column - Staci
and keeps going every time the form is checked.

If it was an IF it would be =
If Game 1 empty = form submission
If Game 1 not empty, Game 2 = form submission
If game 1 not empty, if game 2 not empty, game 3 = form submission

And so on.

IS there some fixed amount of games per day? How sustainable is it to have x number of columns, and does that x fluctuate?

Regardless, I would still advise you to follow the instructions linked and supplement with 3 (or whatever) Lookup fields with the condition applied:

yes it is a fairly fixed column set. That is preset not dynamic. We may not fill all of them but that is ok.

It must update the columns in the Master Record it is linking to on another table. It is not updating a specific column in the form update.

You can do this with the newly released conditional automations.

It sounds like you have two linked tables: a [Main] table and a [Form] table. The [Main] table has for several different games, and a link to the [Form] table. The [Form] table has a link to a record in the [Main] table and a text field for a person’s name.

I suggest that you add a lookup field in your [Main] table that looks up the person’s name from the linked form.

Then setup your automation to trigger when a record in the [Main] table has a value the link to the [Form]. Use conditions in the automation to determine which column/field in the record is empty. Once the automation finds a blank column/field, update the triggering record to copy the lookup of the person’s name to the proper column, and then reset the linked record field to empty.

You should also setup a filter view in the [Main] table that only shows records where there are available slots/columns/fields. Then have your form only show records in that filtered view.

1 Like

This is so close to what I need, but I do allow for people to submit multiple winners per form.

So it could be Race 1 - 1st, 2nd
Race 2 - 1st, 2nd, 3rd.

How would I map those properly and ignore blank fields

How do I write a condition to find which field is empty? For the life of me I can not figure out how to do it because you can not put conditions on update record.

Here is what I am doing in the automation:

  • Finds the Record When Enters View (this record has the result that needs to go in the main record)
  • Action - Find the main record that needs the result
  • Conditional - If the result is not already recorded then find the (Probably can get rid of this)
  • Last Action I can Not make work. I need to say
  1. If Spot 1 is empty paste input from Trigger Record
  2. If Spot 1 is not empty check to see if Spot 2 is empty and paste input from trigger record and so on

I am failing to be able to do that check.

In my previous message I suggested that you trigger the automation based on the record in the main table that contains the record that you want updated, not from the new record that was created by the form. I also recommend using the “when record meets conditions” as the trigger, instead of when the record enters a view. The condition for the trigger would be based on the backlink to the record created by the form. Then in the conditions for the action, you could see which fields in the triggering record are blank.