Skip to main content

Hi, My Airtable use case is to maintain Farmer’s finances. We have developed a WhatsApp bot, and by using webhook triggers the information gets stored in Airtable.

I have a query regarding calculations.

This is my current data structure.

 

Since we cannot create columns dynamically in Airtable using Automations, I am generating dynamic rows for each expense type for each crop.

Now in the Current Crop Expense column, I want to calculate the sum of all the Expense Amount for the current crop for that particular user.

For eg: For user John CROP B, the total in the Current Crop Expense column will be (200+60+70) = 330 and stored in the corresponding row to the crop name. It can be stored correspondingly to all 3 crop B rows or just one. But I need the calculated value to send back to the users.

For Crop A for the same user, the value stored in Current Crop Expense will be 170, and so on.

For User 2 - the total Current Crop Expense for Crop A will be 7, Crop B = 410  (240+170)

I want to perform this above operation for different users and calculate their total Current Crop Expense based on the sum of Expense Amount for each crop

So how do I achieve this? I tried lookup and roll-ups but here I am. is this possible in Airtable?
Any help or advice is appreciated

Hi. No one has answered in a few hours, so I will take a shot. Hopefully someone chimes in with a better way if this doesn't work. 

First, have you thought about having another table for "Crops", where the primary key is short text and holds "Crop A", "Crop B", etc? In the table you show above, you would change the field type for "Crop Name" to a link field to "Crops".  Then, back in "Crops", you can sum the expenses for each crop in a rollup column. And finally, if you want that sum to be shown in the table above, you make "Current Crop Expense" a lookup field into "Crops" for the rollup.

Make sense?

Hi. No one has answered in a few hours, so I will take a shot. Hopefully someone chimes in with a better way if this doesn't work. 

First, have you thought about having another table for "Crops", where the primary key is short text and holds "Crop A", "Crop B", etc? In the table you show above, you would change the field type for "Crop Name" to a link field to "Crops".  Then, back in "Crops", you can sum the expenses for each crop in a rollup column. And finally, if you want that sum to be shown in the table above, you make "Current Crop Expense" a lookup field into "Crops" for the rollup.

Make sense?


Thank you so much for your reply, but will this work for multiple users? 

Hi. No one has answered in a few hours, so I will take a shot. Hopefully someone chimes in with a better way if this doesn't work. 

First, have you thought about having another table for "Crops", where the primary key is short text and holds "Crop A", "Crop B", etc? In the table you show above, you would change the field type for "Crop Name" to a link field to "Crops".  Then, back in "Crops", you can sum the expenses for each crop in a rollup column. And finally, if you want that sum to be shown in the table above, you make "Current Crop Expense" a lookup field into "Crops" for the rollup.

Make sense?


as suspected, this solution isn't working for multiple users. It sums the expense amount for a particular crop for all the users

as suspected, this solution isn't working for multiple users. It sums the expense amount for a particular crop for all the users


Assuming the total number of crop types is known and doesn't change that often, I would:

1. Create a new table called `Users` or some such
2. Create a link between the `Users` table and the data table from your screenshot
3. In the `Users` table, create one rollup field per crop type that sums the value
  - The end product would be a table where each record is a user, and each field is the summed expense amount per crop
4. In the data table, make sure each record is linked to the right `User` record, either via updating the way the data gets input originally or via an automation

This should get you what you're looking for I think

Reply


Terms & Conditions