Subtract the value of an auto-updated number field - from an existing number field.

[hauwi]

Hi. My real-world application uses an 'update record' automation to pass the last date and number of hours of a client session to my 'client' table as 'last session'.

I need a new automation to run each time that field changes. I want it to look at my 'number of hours allocated' field (each client is allowed a fixed number of hours), and deduct the new 'last session hours' from it, to show 'hours remaining'.

It seems simple but I just can't seem to find an elegant way of doing this. Originally I thought it might be possible in a formula, but of course I can't reference the field the formula is in.

(I'm thinking of this as similar to keeping track of number of available stock items)

[Ella]

Hi @hauwi ,

I think this could be done with linked records and lookup or rollup fields. For example, link the client table to the sessions table and then add the duration of each session to the the session table as individual records. Then in the client table have a rollup field using SUM(values) for the sessions. And then have a formula field in the client table that subtracts the rollup field value from the allotted time.

[hauwi]

Hi. Thank you, but this is not really what I need to do. I don't want to create a new record for every session. A number of session hours are claimed by the tutor in a single claim. So each record is a separate claim, recording x number of hours in that claim period for a given client.

Each time a new claim/record is entered, I need a formula that subtracts the number of hours in that claim from the number of hours allocated to the client. It seems simple to me, but I cannot find a way to achieve it!

Thanks in advance. 🙂