Subtract according to row values

[Blending_Produc]

Hello guys!
I would like help to do a subtraction according to the values ​​that are being established.

The situation is as follows: I work in credit format with my clients, so they start the month with 8 credits, as they request me, I establish the value in credits and these are consumed. Because of this situation, I would like to create 3 columns, 1 column “Cost in credits”, 1 column “Remaining credits” and 1 column “Total credits”. As I establish the cost of each material, I would like the column “Remaining credits” to be the result of “Total credits”-“Cost in credits”(for each row).

I know Airtable has a different focus than spreadsheets, but the point is that I’m really enjoying the platform and I’d like to centralize as much activities as possible here, my company is just starting out, so splitting things across multiple platforms ends up messing up the flow of work.

If there is a way to do what I said, I would be very happy.

Thanks in advance!

[Kamille_Parks]

The formula for this would be nearly exactly as you’ve written it, but instead of quotes you wrap the name of columns in brackets.

{Total credits} - {Cost in credits}

[Blending_Produc]

Hi Kamille!
I really appreciate the feedback.

So, the big question is the value to be subtracted according to the work demands, for example: the client starts the month with 12 credits, this value is in the column “Total credits”. On 05/02/2021 he asks me for a job, then I stipulate that this job will cost 2 credits and I add this value in the column “Cost in credits”, so the column “Remaining credits” would automatically have to go to 10 credits ( 12 - 2 =10). Then the client asks me for another job on 05/06/2021, now I stipulate that this job will cost 4 credits, so I put this value in the column “Cost in credits”, so the subtraction of the cost has to be done taking in considering that he had already asked me for a work of 2 credits in month 05 and always subtract the value of the column “Remaining credits” from the previous line, which is no longer 12 but 10.

I don’t know if it’s quite clear, it’s a little confusing logic, but I used it a lot in spreadsheets. I would like the most viable solution possible, if it is necessary to create another project to put the value of credits for the month and pull the values ​​from it, no problem, I just can’t see a subtraction solution taking into account the value of the column “Credits Remaining” from the previous line.

I have attached a photo of how the project is currently set up, I think it might help to visualize what I want. Note that currently the “Total Credits” column is being manually updated, so the values ​​in the “Remaining Credits” column are correct.

Sorry about the mess.
Thanks in advance!

[Kamille_Parks]

Blending_Producoes:

I just can’t see a subtraction solution taking into account the value of the column “Credits Remaining” from the previous line

Therein lies the rub. Airtable formulas only have access to the values from a single record’s fields. That means if you want to use values from another record, you have to link those records together and use Lookup (or Rollup) fields.

Now seeing your data structure, It seems like you should have a table for Clients, and whatever table you’ve screenshotted should link to it. Instead of retyping Client 1 over and over, you’d have a single record in the Clients table for “Client 1” that each relevant request links to. Additionally, using single select field for the Month will essentially limit you to only organizing data for a single calendar year (what happens when you get to November 2022?).

It is unclear whether Clients’ credits rollover each month or if credits expire at the end of the month. This distinction will affect the best possible database setup.

[Blending_Produc]

Perfect, I really liked the suggestion to create a table for customers, I’ll do it!

As for the credits model, every month the customer receives more credits and if there are credits from the previous month they are added. If the customer ends the month and still has 2 credits, the following month he will have 10 credits (8 from the month + 2 from last month).

What would be the best way for me to format this?

Regarding the table being limited to 1 year, I believe there is no problem, the idea is at the end of each year, to duplicate the table and store the previous one.

Sorry if I’m making a lot of confusion, I started using Airtable 2 days ago, so I’m learning about the platform and its possibilities.

Thank you for your attention!

[Kamille_Parks · Accepted solution]

Blending_Producoes:

As for the credits model, every month the customer receives more credits and if there are credits from the previous month they are added.

In that case your Clients table could automatically calculate how many credits they’ve accrued since a given start date:

• {Start Date} shows the date from which a client started accruing credits
• {Credits Accrued} uses the formula below to count how many credits they’ve earned (12 each month since the start date)

(DATETIME_DIFF(TODAY(), {Start Date}, "months")  + 1) * 12

• {Total Credits Used} is a Rollup formula of the first table’s {Cost in Credits} field using the SUM(values) aggregation
• {Credits Remaining} is a simple formula subtracting used credits from accrued credits

Blending_Producoes:

the idea is at the end of each year, to duplicate the table and store the previous one.

I do not recommend doing this. 9.9 times out of 10 it would be be better to store all similar data in one table and use Views to show individual years.

[Blending_Produc]

Thank you very much, Kamille!
Your suggestion worked perfectly!!

Sorry for the delay to return, I was only able to test now. So far I’ve only added one client to test, but I’ll do it for everyone now.

I would like to take this opportunity and clear one more question, it is not so essential, but if it is possible to make it work, it would help me a lot too. Would it be possible to create a column with credit history? For example: On 11/15/2021 the client requested a job “recording | cases”, then I define the cost of this job in the column “Cost in Credits”, then automatically I will have the amount of credits remaining in the column “Credits remaining” . Does the amount of credits he had that day remain in a column like “Credit History”?

I don’t know if it was very clear, the idea is that this column works exactly as a history, recording the amount of credits he had on the day he requested this work, already taking into account its cost.

Thank you very much for your attention!

PS: I’ve attached some photos for you to see how the organization was and make it easier to understand. Do you think it was well optimized?

[Kamille_Parks]

If you need to record running history like that then you need an Automation. Set the trigger for when record matches conditions where: {Client} is not empty, and {Cost} is empty. Then use an Update Record action to copy the value of the {Credits Remaining} lookup field into the {Credit History} field. This will only work for records going forward, you’ll need to fill in credit history for records already in the table.

[Blending_Produc]

Perfect, Kamille!
This suggestion also worked very well.

Now there’s only one question left, is there any way I can create IF() type conditions as I capture customers with different needs? The point is that not all customers pay for 8 credits a month, and the way I set up the “Customers” table each month everyone will have +8 credits.

There would be a way for me to add conditionals like “if the customer name is ‘Customer 1’ he will receive +8 credits each month, but if the name is ‘Customer 2’ he will receive +10 credits each month”. I don’t know if it’s the best way to do it either, but it was a solution that came to my mind. Which is the best route?

Once again, thank you so much for your patience and attention.

[Kamille_Parks]

Simply have a field for {Credits Accrued Per Month} and enter 10 for Customer 2 and 8 for Customer 1. Then adjust your {Credits Accrued} formula to use that number instead of the hardcoded multiplier shown in the example above.