Summing up an airtable field

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 :frowning:

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.

2 Likes

@NigelG @pfordmedia 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:
image

If I want to sum the Amount of all records, I can build a linked table like this:

  1. Add a new field Link of type Formula. Fill the formula with a unique string, such as “.”
    image
    Result:
    image
  2. Change the field type from Formula to Link to another record:
    Click the dropdown arrow of the field header and select Customize field type
    image
    Select Link to another record
    image
    Select Create a new Table
    image
    Enter the name of the new Table (“Calculations”) and click Save.
    Click Skip adding additional fields. (You can do this later, if you need to, but it’s not relevant for the calculation).

    Result:
    The field type changed and you created a new table.
    image
    The new table contains just one record.
    image
  3. For the calculation, add a new field of type Rollup:
    image
    Enter a Name and select the type Rollup.
    For the Rollup details select the linked table and the field you want to calculate (“Amount”).
    For the Aggregation choose SUM(values) and click Save.
    Result:
    image
    The Total is now automatically calculated whenever there is a change in the linked table.

A more complex scenario
In this scenario I am using several tables linked to each other:
A Customer table:


The Customer table links to the Orders

Orders link to Order Items

Order Items link to Products

Beside the base price In the Product table that is used for the multiplication with the ordered quantity, the table also contains an example for using aggregation in rollup field:

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:
image

image

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).
image.
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.
image

  1. Add an additional field of type Formula.
    image
    Result:
    we now have the key in a separate field that can be used for filtering in Adalo.
    image
  2. Repeat the same for all linked values you need for filtering in Adalo.

For example in the Customer table


image
3. If you need to work with multiple linked entries like in the Order field in (a customer has multiple orders linked) use the following formula to create a separate field you can search in Adalo:
image

In Adalo you need to use Search in the filterByFormula value, e.g. Search(‘string’,{airtable_field}).

  1. Don’t forget to update the changed Airtable structure in Adalo by clicking through the setup steps.

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: https://community.airtable.com/t/how-do-i-automatically-add-up-all-values-in-a-column/2669/4 and https://community.airtable.com/t/multi-record-calculations-c-f-running-balance-row-number-and-the-like/10961

@NigelG ARE YOU STILL WITH ME :slight_smile:

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 :slight_smile:
2 Likes

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 :frowning:

@pfordmedia confirmed that it wasn’t working, and I have subsequently heard from support that they have an open ticket on this.

Fine for me :slight_smile:
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!

2 Likes

@karimoo @pfordmedia

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 :slight_smile: