Hi all,
I want to sort records in table by the number of filled data of several blanks (Q1-Q5), say if all data in Q1-Q5 are filled, the records will be prioritized to show on top in the table.
Should I setup a score data by IF formula? Can a IF formula include “{field} is not blank”?
What we really need for this is being able to sort data by more than one field. This is something we will have very soon.
In the meantime depending on your data structure and your ultimate goals you might be able to add an equation field and then sort by that field. This can get very tricky and complex.
As a quick overview, suppose you have 3 numbers fields: Number1, Number2 and Number3.
If you want to first sort by only fields where all 3 numbers are higher than 0 you can have an equation that look like this:
IF({Number1} > 1 && {Number2} > 0 && {Number3} > 0 ,1,9)
The above equation check if all 3 are filled in, if they are then it will set the value to 1 otherwise it will set the value to 9. This will be value you sort by.
You can add a deeper layer now with a nested if statement, so let’s take the above statement and add another If inside of it.
Before we add it, let’s see how it looks not nested:
IF({Number1} < 1 && {Number2} > 0 && {Number3} > 0 ,2,9)
Here we check if Number is less than 1, but number2 and number 3 are greater than 0. If True, set the value to 2, otherwise set it to 9
We can combine these 2 together which means it will check the first IF then if the first IF is False it will check the second.
We combine it by replacing the 9 in the first statement with the full IF from the second statement.
IF({Number1} > 1 && {Number2} > 0 && {Number3} > 0 ,1,IF({Number1} < 1 && {Number2} > 0 && {Number3} > 0 ,2,9))
You can keep nesting them technically as well use different conditions instead of && to check for “OR” (use || for OR).
My key advice is to always create one IF statement at a time and test it. If it works, add it to the original by replacing only the False value in the first statement.
You can see this GIF from our support docs:
Learn more here:
All said and done this would be 100x easier with multi field sorting which is in the works.
We’ve now added ability to sort by multiple fields. Please see the Data Source tab of your Table or List Component.
Great to know! will check out, thousands thx
