A formula for running total based on LINKED record...

[0xab]

Hi everybody,

Let's assume I have 3 tables:

• Tools
• Users
• Requests (= Audit Log)

Users can create a request to get access to a certain tool. When the tool admin approves the request, the user is linked to the tool. That's fairly simple and already working.

Now tool admins can also add ore remove users directly to their tools, by linking or unlinking them.
In that case, I want to automatically create a record in the Requests table that contains the user and the tool that was either linked or unlinked, as that table serves also as my audit log. 

Any idea how I could solve that?

[TheTimeSavingCo]

I think you'd need another field to act as a 'History' that you could compare the current one too, and then automate off of there being a difference between the two.  I can't think of how you'd find the difference with just a formula though, so I feel like you'd need a script for that? 

Hopefully someone else has a more elegant solution!

[ladracy]

A couple follow up questions:

1. Is there a business need to have the user linked to the tool only once the requests has been approved?
2. Do you actually need a new record created on the requests table when admins update existing requests by linking or unlinking users? 
   1. In doing this, you would be creating duplicates – the original requests that has a been manually updated by your admins with new users, the newly created requests record you are requesting to create using automation, and the audit record

I would recommend the following:

1. Configure your Requests table as a junction table [linked to Users and Tools]
2. Have users submit a request form where they select their name and they tool they are requesting
   1. The form submission will auto fill with an 'In Review' status and alert your admins
   2. Admins can then approve the request [no need to link the user because the user has already been linked when the request was first submitted] 
      1. If needed, you can send an alert to the user letting them know there request has been Approved by the admin
      2. Can also easily track and report on how many requests have been approved, denied, in review etc
3. On the Requests table admins can update any request with a new user which would trigger a new record being created on your Audit Log table with the name of the user, the tool they were linked to and the time in which the audit log record was created. 
   1. A business decision needs to be made at this step:
      1. Are you okay with treating the updated record as a "New Request" since you will be creating an audit log record with the previous User and Tool?
      2. Instead of having admins add a new user to an existing record through linking, are you open to marking the original request as 'Audit' which would trigger a string of automations:
         1. Create the record on your audit log
         2. Create a new record on your Requests table
         3. Remove the data from the original record. This would require you to manually delete these records which I would recommend deleting weekly or monthly

I've built out this solution for your review and would be more than happy to discuss the solution live.

Hopefully this helps. 

Your Business Automation Expert 🤓