My PTO app has two tables, Requests and Users. Every user has a 'quota' of days off they can ask for, and I want to update this 'quota' whenever a new PTO request is approved. I'm having a hard time implementing this because I need to update one table based on the values of another.
I need to select the current Request and update the User accordingly because there can be multiple requests linked to the same User. If I use the "Data: set the values of some columns in this row" option on the User table, I need to find an expression to select the right Request for it to work. I tried to use the LOOKUP() function, but I'm having trouble filtering the exact (request) row I want because the only way to target a specific Request is through the Key column of my Requests table, which is the randomly generated 'request id'.
Basically what I want is: When approved -> (user remaining days) - (number of days of the approved request')
My bot is currently working on the context of the request, and I want this step to be a part of it, so after it gets approved (Aprovar Externo), I want to access the "number of days" in this particular request and then alter the column of the User related to this request using that number.
Here are my tables for reference
User table (saldo de fรฉrias = remaining days)
Requests table (dias = number of days of the approved request) with "Funcionรกrio" as the reference betweet these two tables
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |