Form to update record column MUST select record based on previous answer on form

[Victoria_Read]

Hi,

Every day drivers fill out a form to indicate the vehicle they drove (via reg number) and the amount of charge left in the vehicle ‘Charge’.

I would like the form to automatically update the vehicle record in the ‘Charge’ section every time a new form is submitted for that specific vehicle. I need it to be intelligent enough to base the update on the vehicle that was selected.

Is there any way to do this? Happy to use extensions if it helps, Thanks!

[TheTimeSavingCo]

Hi Victoria, I ran with the assumption that you’ve got two tables: Form Submissions and Vehicles.

I’ve put something together here for you to check out

On form submission, an automation will run looking for a record in the Vehicles table that has the same reg number that was submitted.

If it finds one, then it’ll update that record in the Vehicles table with the new Charge value as well as link the form submission to it

If it doesn’t find anything, it’ll create a new record

Happy to answer any questions you have!

[Victoria_Read]

Thanks for that Adam! I’ve gotten as far as choosing the condition property for the ‘find record’ step. I’m going to try and explain this simply!

Info: On the form the drivers submit the selection of ‘vehicle reg’ via a linked field (which selects the primary field of the Vehicles table (Vehicle Name)

All of the following are Unique to each vehicle;
Vehicle Name; formula that takes {Vehicle Type} & - & {Reg Number)
Vehicle Reg; Reg number (via single select, unsure why previously set up this way and can change if helpful)
Internal ID; manually assigned unique ID number

I essentially want to say where Vehicle Name (from vehicles table) contains Vehicle Reg from form (chosen via a link, so is exactly the same value as Vehicle Name) then update the record.

If this is not possible (due to the linking) could I add a lookup field to the form? This would keep drivers selection as is but show ‘Internal ID’ as a lookup (which i could hide from the form but would show in the grid/data view) then I can internal id from the form to internal id on the vehicles table?

Hope that made sense! Thanks

[TheTimeSavingCo]

Hi Victoria, hmm, if the drivers are already linking it this might be even simpler and we might not need a “Find Record” action at all

Have a look at this

Does this look about right to you? Apologies in advance; I feel like I may have misunderstood your workflow

[Victoria_Read]

It does look right but I can’t replicate it for the life of me. I do apologise this is so painful to explain haha.

So on my base Complete Vehicle List is equivalent to the Vehicles Table you’ve created

On Record ID: Vehicle Reg:Name (shows as equivalent to Vehicle Name from the Complete Vehicle List)
Range Left: The amount filled in on the form

I get this error: ‘Received invalid inputs.’ I don’t even know what more information to include to make the issue any more clear, which is probably quite unhelpful…

The only thing I can think is…

• The link to the vehicle name/reg on the form is filtered to only select from a specific view (does this change anything?)
• When I’m selecting on ‘Record ID’ it has all the fields from the Form and none of the fields from the ‘Complete Vehicle List’ table - is that normal?

[TheTimeSavingCo]

Hmm, could we try changing that Vehicle Reg | Name token to Vehicle Reg | ID like so?

Victoria_Read:

The link to the vehicle name/reg on the form is filtered to only select from a specific view (does this change anything?)

Nah, I think this would only affect what the user can pick when filling out the form and wouldn’t affect the automation

Victoria_Read:

When I’m selecting on ‘Record ID’ it has all the fields from the Form and none of the fields from the ‘Complete Vehicle List’ table - is that normal?

Yeap this is normal!