Simple Task : Sum up a number field on an Airtable base. Output to a screen like “Total : 10” in a text field.
Airtable API
https://api.airtable.com/v0/appfViFwjuQCnG24Q/sum
API Token
Bearer keyE50iZJgrnUaYMP (throwaway airtable instance)
Now, you would think it would be this. But it isn’t. Count works. Sum doesn’t.
$250 ?
Easy money for an “expert” I would have thought.
Can you try making the text a list of sums limited to one item and then calculating the amount field? Also is the amount field a number or a currency field in Airtable?
Also @NigelG I just tested you collection and you only have one record that is returned. Could you add some more test data?
Not quite sure what you mean here.
Number. Have tried varying the precision from 0 to 2.
Done. I was trying to make it even simpler by just trying to sum one thing.
Ah I see. Yeah it sucks because this is really a combined issue between Adalo and Airtable. Adalo can’t parse the JSON data past the first level really, and Airtable doesn’t include the botton calculate bar in their API. The only real solution here is to create another table (call it SUM or whatever) with a linked field to the table you want to sum. Then in the new SUM table create a Rollup of the Amount field in the original table. Then call this SUM table by itself anytime you want the sum - it will only ever have one record in it. Anytime you add a record with an amount, you’ll have to link it to the SUM table to include it in the rollup. I wish there was a more elegant solution but that’s the easiest way to accomplish this I think.
Thanks.
I have tried that and found updating the total from Airtable via a page load action to be a problem. It works on a “list” button click, so maybe a “welcome back” home page that updates the User data.
I need to sum for a filter, which was the next step. So there will be multiple rows in there per user. This was just the first step into getting the sum to work.
Could you perhaps include the sum as a part of the filterByFormula? Sorry if that doesn’t apply here - it’s hard to diagnose without context.
I think doing it via the API is probably the way forward.
Please DM your payment details, thanks for helping. At least I know it can’t be done in the way I was thinking.
The only other thing that I can think of is that Adalo doesn’t seem to like decimals very much. Have you tried making the field an integer instead? (just for grins and giggles)
Yup. Tried that 
I figured so. I think if you could make it work with filterByFormula that’s probably a good way to do it. Regardless, I will pass this along to Adalo and see if they have any plans for improving deep parsing on JSON data.
@NigelG @pford If you can’t use the Sum function in Adalo, you can do it in Airtable.
You need linked tables and the Rollup function in Airtable.
Let’s start with an simple example:
If I want to sum the Amount of all records, I can build a linked table like this:
A more complex scenario
In this scenario I am using several tables linked to each other:
A Customer table:
The field Ordered Quantity Total calculates the sum of all quantities ordered:
To show the difference I also added a Lookup field with multiple values:
Filtering linked tables in Adalo
Using linked tables in Airtable is somehow tricky when it comes to filtering in Adalo.
Important to know is that a linked record in Airtable is displayed with the key (= first column of the table).
.
Technically it is handled as an array like [id,key] where key is the first column of the table, in my example the autogenerated Index number.
BUT
In Adalo you cannot use the key or the array, because Adalo cannot handle the array.
So what to do when you want to filter the Orders by customers, or the Order Items by customers or product.
Here comes the trick
You need to duplicate the key using a simple fromula field in Airtable.
In the Order table example we have the Customer linked by the index number (=customer number) of the Customer table.
For example in the Customer table
In Adalo you need to use Search in the filterByFormula value, e.g. Search(‘string’,{airtable_field}).
Filtering in Adalo using the additional fields
When I want to see the orders of a selected customer the filter for airtable looks like this:
For more details see also: How do I automatically add up all values in a colu... - Airtable Community and Multi-record calculations (c.f. 'running balance,'... - Airtable Community
@NigelG ARE YOU STILL WITH ME 
If this is helpful and you think this qualifies for your $200 offer, please send the money to the charity organization my wife is running in Sudan:
Click on Donations to find the relevant information.
If you are in the US and scarred to do a banking transaction with Sudan, please DM me
Thanks for this. I am trying to avoid using the Airtable rollups (although I appreciate that they are a great solution) as I really want to be able to sum using various filters. That would mean a lot of rollups
@pford confirmed that it wasn’t working, and I have subsequently heard from support that they have an open ticket on this.
Fine for me
I would prefer to have the aggregations done on the database.
I guess(!) aggregation for external sources such as Airtable has not been implemented yet because of the possible restrictions you have with the APIs:
With Airtable e.g. you will have to handle pagination: Airtable’s response is limited to 100 records per page. So, if you need to aggregate on data beyond 1 page=100 records you need to pull several pages or even the full set of data in the case you want to calculate something related to grand total.
I am curious what development will say. Reminds me that there was a feature request to look into GraphQL support.
@karimoo Your writeups are legendary! Thanks so much for the input here. I think this method is what I was (not-so-elegantly) trying to describe, but this writeup is amazing and sure to help someone else down the road! Thank you!
Actually, clever though it may be, it doesn’t always work in practical terms because of the way Adalo handles external collections.
Example: If you have a COUNT field on Airtable, and the majority of your rows have a count of zero, then when you do the “Run Test” in Adalo to pull in examples Adalo only pulls in the first few rows. If they all happen to have zero in the field you want … you are stuffed.
Because it thinks that you can’t possibly want a field with zero in, you can’t then use that in “Magic Text”.
The work around is to add in a sort field to your External Collection to make sure you get a record with this in the first returned results.
Hopefully, this restriction will be removed at some point as I can’t see why it is needed particularly as I now have to sort the Airtable table each time which has a performance impact.
This is a live app, for real live paying customers, so I can’t just update the data 
This topic was automatically closed 10 days after the last reply. New replies are no longer allowed.
