Triggering off a Form Submission, New Record linking itself to another table

[Killian_Sulliva]

Howdy,

So I have a table of devices called “Devices Log” and I let people check them out to do testing with them. ‘Devices Log’ has a field called “Owner” which is a link to a directory of testers who can check out and own devices.

The check out functionality is to submit a form on a different table called “Device Checkout Log” which includes linking the device being checked out, and the owner checking it out.

Now on the record for each device is a linked history of who checked out a device and when, which is useful for when digging into ownership is necessary. But I want to have a sperate field listing the current owner only. Is there a way to make an automation so that when a new record is submitted, the ownership of a device is automatically updated to reflect the new checkout?

[Killian_Sulliva]

Would this possibly be easier to do through a lookup field with conditional formatting? I have tried playing around with that but I seem unable to get it to select only the “most recent” entry

[Kamille_Parks]

I’m assuming you have a field for {Check Out Date} and a field for {Check In Date} (or a some other means of noting when someone checked the device back in). If that’s the case you can do a Lookup field displaying the {Owner} field in the [Log] table with the condition that only records which have {Check In Date} empty.

[Killian_Sulliva]

Hmm, I do have a check in date, but it is currently not part of the same record as we use a form to allow checking devices back in too. Is there any way to have a form search for and append a record?

[Kamille_Parks]

Okay, so you have one form submission to check out, and one form submission to check in?

In that case, you should be able to use a Count field to get the total number of logged form submissions, and a Formula field to analyze if that number is even or odd: if the count is odd, the item has not been checked in. `IF(AND({Count} > 0, MOD({Count}, 2)), “Out”, “In”)}

In your Log table add a Lookup field called “Status” that brings in the formula field described above.

In your Devices table add a Lookup field displaying the {Owner} field in the [Log] table with the condition that only records which have the {Status} field = “Out”

[Killian_Sulliva]

Ok, so this all is working up until the final step. The problem is that when a new log gets created, say to check the device out, all the old logs also update with the new ‘out’ status and we still display every owner who ever there was. So is there a field or automation I can do so that when a new record is created, the value of the status field gets copied across into a separate cell which never changes?

What I am thinking is I will turn this into a type of auto number where each device’s associated logs are enumerated separately. A count field is keeping track of how many logs are tied to it, and on the log side when the record is made its count at that moment is saved.

Then i will have the device page pull the ownership record from the checkout log where the number is = to the count. If the device is out i get a return, and if it is in i get a blank

[Kamille_Parks · Accepted solution]

You could do this with an automation, but I’m going to continue down the Formula/Lookup rabbit hole:

If you add a Rollup field using MAX(values) to the Device table pulling in the {Date} field from the Log table, you could then add a Lookup field to the Log table that pulls in the latest date recorded for that device. Then adda Formula field called {Latest?} using IF({Date field} = {Max Date lookup field}, "latest"

Adjust your conditional lookup field in the Devices table to include {Status} = “Out” AND {Latest?} is not empty.

[Killian_Sulliva]

Excellent! this did indeed work, thank you