I am trying sum and average a column based on the displayed rows after a search bar search. I can’t seem to find anywhere I can do this. Another less favorable way would be to get the search bar text and add it to a cell in the sheet which then calculated based on that cell but I can’t find a way to get the search bar text and add it to a cell. Feels like my app builder is missing some tools!
Thoughts?
No, you can’t use rollup based functionality on only rows that are the result of the built in search.
I think you have the general idea of what you need to do, but you can’t use the built in search. Instead you would have to build your own search with a text entry component that fills a user specific column. Then use a single value column to populate that search term across all rows. Then do any logic you need to return the numbers or other values by using an IF column to return that number if that row matches your search.
You have to build your own search. You can’t use the built in search functionality for this use case.
Makes sense. So I’ve done that but the roll up of the values which have been true are returning the value divided by 1000… And then when I put a math column on that to multiply back to 1000 it is only doing so for values under 1000000…
Can you explain more about your rollups and the dividing and multiplying by 1000? I would guess a rounding issue somewhere, but I’m not sure I understand the problem.
Ya so when I roll up the column of filtered values, the sum of that column is not correct. As I dug it seemed like it was skipping values which I have no idea why. It was also returning a value that seemed to be the sum / 1000. So I did a math column which just multiplied the filtered values by 1000 to see if that fixed it. And for many it returned the correct value, but for those that were lower than 100 it mulitplied too much, and for those that were above 1000000 it just erased the value. You can see that at the bottom of my screenshot. The value was $5.1M, but the “Corrected Search Values” which is just math of Searched Values * 1000, it returned blank.
You can see this here: This is showing the math column dividing all values by 1000 when there is no arithmetic included.
But yet still not pulling over values over $1M
That doesn’t sound right. Something isn’t configured correctly. It seems that it is recognizing your thousand group separator as a decimal indicator. I know some countries use commas as their decimal and others use a period as the decimal. The math column is rounding because you have precision set to 1 (whole number), and anything after the comma is considered a decimal. Thus the reason you think you need to multiply by 1000. Anything over one million isn’t working because is not a valid number due to have 2 decimal points instead of 1.
Where are your original numbers coming from?
Is your computer/browser region/locale set to the wrong country?
I don’t have any issues with large numbers. Even 1 billion works fine with a rollup.
Oh interesting. I will take a look. The numbers are coming from my browser which I thought was set to the US. Ill dig.
Its weird because any other browser app (particularly Google Sheets) is working just fine and inputs the comma directly for anything past 1000. Still looking. Does glide have a setting for this?
No it shouldn’t be a Glide setting. Glide does adapt well and will format dates and numbers based on a user’s locale, so for example, if I open an app and someone in India opens the same app, they will see dates and numbers in their normal format, but I will see the same dates and numbers in a format that I’m used to. So, it should be automatic.
Can you show a screenshot of the original location of those numbers in your table? Is the column type not a number? I’m just trying to see if there is something that I can notice that you may not have.
The second column in is the value column which is filtered by a text input on the app. The search filter (middle/right) column then has true or false if that row is in the search, then the correct search value column is the math column I showed above which right now is just the value with no math on it.
So here is what fixed it temporarily. It looks like glide was not recognizing values over 1M when the original value was coming from sheets as a currency format. When I changed that format to just plain numbers it can now recognize them and counts them. This issue should be fixed though by Glide.
OK, I’m able to reproduce it, but I’m not sure I fully understand what’s happening…but I kind of do…but I’m not sure I want to classify it as a bug yet.
Can you show me how your SearchedValues column is configured? At some point that number is getting converted to text with the commas intact, and that throws the math column for a loop because it’s not expecting anything other than raw numbers, and by default it treats commas as decimals. If the IF column was returning a raw number without any dollar sign or commas, then everything would work. I feel like you have an extra template column or something else that the SearchedValues column is returning, so it’s only treating the numbers as text instead of actual numbers.
