Update Linked Field After Form is Submitted on Another Table

[aleajewelle]

This is basically a leave tracker. 

Table 1: Employee Details

• This table contains the start date of the employee and a column that computes the available leave credits they have accumulated.
• Column LEAVES is linked to Table 2 and should look up the duration of the leave request applied for while another field computes the remaining leave credits

Table 2: Leave Requests

• This table computes the duration of the leave applied for using the FROM and TO DATE. I've made the primary field an autonumber for a unique identifier.

I've created an automation that does the following:

• Trigger: When a record enters the view (meaning the a form has been submitted)
• Find Records: From Table 1 that matches the first and last name of the requestor
• Update Record: This is where I'm stuck. I want the Linked Field LEAVES  in Table 1 to be updated with the Leave Application # so that it looks up and sums up the duration and automatically computes the number of leave credits left.

I keep getting the below error no matter what I try to tweak:

I am not very familiar with scripting. I've seen replies from @JonathanBowen and @kuovonne but I need more background in scripting to understand. 

Is there no way to automatically update a linked field?

[Marielle_Gueis1]

Hi @aleajewelle , 

I hope I can help you out. It seems like you got mixed up in your last step. 

You want to update the employee's record. That's why you picked the Employee's table. That means that in the previous step, you look for a record from this table. Therefore, in the field "Record ID", you need to enter the record ID found in the previous step (Find records) and not the record ID from the request. That's why you get the error message that the record doesn't exist. 

Then, in order not to overwrite the requests with the new request made, you need to combine the requests found in the "Find Records" step, and combine it with the newly created request. I believe you can do that by entering the found records, adding a "," and finally adding the Record ID from the trigger step. 

Hope this helps!

[aleajewelle]

@Marielle_Gueis1 , thank you for taking the time to answer my query.

I was able to make this work by following your suggestion. However, I am not sure how to do the last part to not overwrite existing records. Currently, It is getting overwritten. I've tried adding , and "," before and after the Leave Application # but it gets an error if I try.  

[Marielle_Gueis1 · Accepted solution]

@aleajewelle What you could try to do instead is to do it the other way around. 
- Now: you're updating the linked fields in the Employee Details Table

- You could try: instead, you could update the record from the Leave Requests table (the one that was just created), because the latter only needs one linked field. So in the step "Update record", you pick the table Leave Requests (instead of employees table), enter the Record ID from the first step/trigger, and add the employee record ID found in the second step "find records" (from the Employee's table) into the field that is linked with the Employees table, I don't know the field name. 

That might be easier! 

[aleajewelle]

@Marielle_Gueis1 I had a hard time figuring this out. Reversing the steps is easier said than done. I need more practice but your recommendation did make more sense after rereading it a million times! lol

Again, thank you! 😍