Calculating based on a Filter result


Let's say my table has 10 rows

Based on user inputs upon a button click, I create an action to filter on that table.

=Filter(Table, "Table[Column1] =$[userInput]")[Column2]

Let's say I get 4 values, how could I specifically consider them as v1, v2, v3, v4 and calculate something like below:

$[calculatedValue]= v1-v4/(v2*v3)

Thank you.

Hi @John-TT,

Thanks for your post here! :honeybee:

For your example, I'll note that a returned FILTER() value holds row data, not numeric data. So if a filter returns "5", that value being displayed represents 5 rows of data that fit the filtered criteria. (not the numeric value of 5)

This means that you couldn't do the calculation example you described, as your values should hold numeric data instead.

What would work though, would be to use the ROWS() function, which returns the number of rows for a filter (numeric data). So you would wrap the filtered formula with ROWS(), and you could then do the calculations with those returned values.

Let me know if that helps, and if you have any further questions. :slight_smile:

Hi @Alyssa,

Thank you for the information.

I would appreciate if you could provide me an example for I am still not able to figure out how to perform calculations based on the user input and values from another table.

The customer selects a couple of items and specifies the quantity. How could I calculate the bill amount and display in the same screen, please?

Hi @John-TT,

Thanks for providing your use case! I can show you an example of calculating sums from user input :slight_smile:

I created an app with an order screen that allows a user to add items and a specified quantity to an order, and the total bill is calculated on the screen.

First, I created a table of items and specified a cost for each item:

I have another table where I store OrderEntries. You can see the columns I added and that the Item column is formatted as a rowlink to the Items table:

And in this table, the Cost column is formatting to calculate cost of the item multiplied by the quantity. The cost is retrieved from the rowlink of the item in the Item column. So for my example, the formula in the cost column is: =[Item][Cost]*[Quantity]

And I have third table set up that stores the order total, once submitted. So these are the columns that I've added:

For the app, this is what the screen looks like:

And this is how I set it up:

The screen has a form where the user can select an item to add to an order and specify a quantity. Both fields are editable to allow user input. The quantity field is a set to a number format, and the item field is a rowlink format to the items table:

When a user clicks on the "Add to Order" button, it will write a new row to the OrderEntries table. This writes the order number on the screen, item selected, and specified quantity. (The calculation of that cost happens in the table, as shown in the table set up above):

Below that form, I added a list that is filtered to display only the entries of the order on the screen. So it's a column list of the OrderEntries table, and the filter formula I used in my example is: =Filter(OrderEntries,"OrderEntries[Order]=%",$[Order])

At the top of the screen, I have a field that calculates and displays the total of all of the entries for that order. This is achieved with a SUM() function wrapping a FILTER() of the cost for each entry for the order on the screen.

For my example, the formula looks like: =SUM(FILTER(OrderEntries,"OrderEntries[Order]=%",$[Order])[Cost])

And finally, I have a "Submit Order" button that will submit the calculated total, order number, and customer name to the OrderSummaries table:

And this is what it looks like in the OrderSummaries table once submitted:

I hope this helps! As shown in this example, you can do calculations of values both in the Tables and in the app through Builder.

Let me know if you have any questions on this. :slight_smile: :honeybee:

1 Like

Hi @Alyssa,

Thank you so much, I appreciate your time and solution.

Could you please help me with another scenario?

I have a table with 3 number columns with names A, B and C and a set of pre-defined data.

When the user inputs u-A and u-B, I want to lookup and calculate C and display.

The reason for the calculation is that the user given input may not match exact A nor B at times.

In such cases, I would have to fetch 4 rows based on the given inputs limits and calculate the C.

so I would have to retrieve 4 rows and calculate on the screen side. Let lower limit be l and higher limit be h

A[l] B[l] => C1
A[l] B[h] => C2
A[h] B[l] => C3
A[h] B[h] => C4

All this I want to do from the screen side and don't want to store in the DB , I want to calculate C with some formula and dispaly.

Thank you so much again.

Alyssa, I'm book marking this thread. Very well explained.


Hi @John-TT,

You're welcome, I'm happy to give you ideas for your solutions :slight_smile:

For your recent question, if you'd like to have a value displayed on the screen that is not stored in the database, you would use a screen variable. Screen variables display a specified value, but do not write to Tables until triggered by an action on the screen (such as clicking a button to trigger an automation).

So for your scenario, you would set up input fields, and also different screen variables, that each have a specified value or formula to calculate and display a desired value.

To show this, I set up a screen with two editable user input fields (data cells):

And a data cell that pulls a C value from the table:

You can then set up data cell variables that calculate values and display them on the screen:

For my example, I calculate and display these two:

You can also set up additional screen variables that compare against the C value, and verify if a condition is true or false. You would do so by wrapping the formula in an IF() function.

Here's a sample formula I used in my example:
=IF($[InputA]+$[InputB]>$[ValueC], TRUE, FALSE)

Now in the app, you can enter two values in the input screen, and the variables on the screen will calculate the values and compare against the C value as specified:

I'll note too that none of those values displayed on the screen will be stored in Tables (or database), until you create and trigger a write action to do so.

Let me know if that helps! :slight_smile: :honeybee:

And @Morelli, that’s great to hear! :star2:

Thank you for sharing that you bookmarked this thread, I’m happy all of this is helpful for you :slight_smile:

Hi @Alyssa,

Thank you so much for your valuable time and for the solution.

Now, I got a clear understanding of using screen variables and employing conditions in them. Thanks a lot.

Let me explain my exact scenario once again, I would like to fetch several rows from the table based on the user inputs and perform the calculation.

I have a table with 3 number columns with names A, B and C and a set of pre-defined data.

When the user inputs u-A and u-B, I want to lookup and calculate C and display.

When the user inputs don't exactly match with table column values, I would have to fetch 4 rows from the table treating them as 'limits' and calculate the C.

so I would have to retrieve 4 rows from the table and calculate on the screen side. Let lower limit be l and higher limit be h

A[l] B[l] => C1
A[l] B[h] => C2
A[h] B[l] => C3
A[h] B[h] => C4

After fetching the 4 different C values from table , I want to calculate the actual C with some formula and display.

Thanks a lot again. :slight_smile:

Hi @John-TT,

Sure, I'm happy these ideas have helped so far! Let me see if I can guide you further -

If you have a table with A and B values already stored, and wish to grab the high and low limits to use in a calculation, you could use the MIN() and MAX() functions to achieve this.

For example, if you have a table of values already stored like this:

You could then get the MIN() or MAX() values of those columns, to be the lower and higher limits respectively.

I set up a screen to display the min and max values of the A and B columns, and each of the formulas in my example are as follows:

Lower limit of A: =MIN(Values[A])
Higher limit of A: =MAX(Values[A])
Lower limit of B: =MIN(Values[B])
Higher limit of V: =MAX(Values[B])

And now you can use those values to do your desired calculations on the screen, or compare the values against C (as demonstrated in my previous reply).

Let me know if that provides more clarity! :slight_smile: :honeybee:

Hi @Alyssa,

Thanks a lot for the information.

Could you please help me with this query?

If I have to fetch more than one row and process the rows on the screen side, how I could do that?

Let's say, I need to fetch all the rows where A is greater than 4 and then based on the number of rows I need to apply a custom formula like C1 + C2 /C3.

Thank you.

Hi @John-TT,

Yes, of course :slight_smile:

For this you would set up a screen variable with the following formula:

This will retrieve all of the rows where the value in column "A" is greater than 4. Keep in mind that this stores the entire row data for each of the rows (not the numeric value).

If you need this value to a numeric value of the number of rows, you will need to wrap the formula in a ROWS() function. So it would be something like this:


And from there, you could use that returned ROWS() value for any custom calculations that you would like (as demonstrated in my previous reply showing screen calculations).

Let me know if that helps and makes sense! :honeybee:

Hi @Alyssa,

Thank you so much for your patience and for the kind information.

Could you please let me know how I could loop through each of the values of rows after I get the FILTER and ROWS results to be able to get hold of each C column value separately?

Let's say we have 4 rows as result, how could I get hold of each C value separately from the result in order to apply a custom formula?

Hi @John-TT,

No problem :slight_smile: And sure, for this question -

You can loop through values on a table using the FILTER() function, as this function contains the row data. You would do so in the "Run Options" of an automation (perhaps on a button), and it would run the automation only on the rows that fit that filter criteria.

This would write data to a table, which you could display in a list on the screen.

This is how the automation would look on the button. I used the "Overwrite" action to update column values, when clicking a calculate button:

Screen Shot 2020-10-29 at 11.04.16 AM

And you could write desired formulas to write data to a column for the rows that meet the criteria filter. So for example:

Screen Shot 2020-10-29 at 11.04.29 AM

Then if you'd like, you could set up additional columns in the Table that do the custom calculations in the table with the new C value (which can then be displayed in the screen).

So for example, I set up a column with a formula in the properties:

This formula will be applied to this column for any new row added. You could add as many columns as you'd like with custom formulas, and then display these values in a list on the screen like this:

Hope this is another helpful solution for you :slight_smile: You can apply these concepts to fit the needs for your app. :honeybee:

Thanks a lot, @Alyssa. This is of great help. :slight_smile: