The “Saved Search” I created is not returning the expected results and so I’m hoping someone can point out the fault in my logic.
Saved Search Description
I have two fields for rating that allow values of 1 to 5
The goal is to select records where the value of both fields is either 2 or 3.
First Line: All Are True
Second Line All Are True
Third Line Value one is greater than 1
Fourth Line Value One is less than 4
Fifth Line All Are True
Sixth Line Value 2 is greater than 1
Seventh Line Value 2 is less than 4
—————
This returns some records with Value 2 at 4 or 5
I’d appreciate any points as to what I’m missing.
Thanks.
Hi Richard,
Can you please upload a screenshot of your Saved Search? Or send me a form template (.tff) file export and I’ll take a look at it.
Thanks,
Brendan
Screen shot of the search query.
Attachments:
You must be
logged in to view attached files.
Is this type of nesting search criteria possible with the iPad app? I have a home work order form (aka Honey Do List). In this form, I have “Request Status” field that is a single-select pick list field and contains a list of statuses for open and closed requests. I also have a field called “Due Date”. I would like to be able to filter all of my open requests that have a due date <= 30 days in the future. From what I can tell, I only have the option to add all of my search criteria and can only select “Any” or “All” for all of my criteria. I need to be able to say “show me all requests for ANY of these statuses AND a due date of 30 days or less in the future”. Is this possible on the mobile app? I don’t have access to a Mac, so I’m limited to what’s available in the iPad/iPhone app.
Attachments:
You must be
logged in to view attached files.
I don’t know if you can use the “Search Term” feature, but what will work is using a calculation field (Status Match) to check the content of the”Request Status” Field:
IFEQUAL([Request Status];"New";1;IFEQUAL([Request Status];"In-progress";1;IFEQUAL([Request Status];"Part on Order";1;IFEQUAL([Request Status];"Waiting for Appointment";1;IFEQUAL([Request Status];"Referred to Landlord";1;0) ) ) ) )
This field returns 1 if there’s a match, otherwise 0. The return type of the calculation field needs to be integer.
The IFEQUAL() function takes 4 parameters. A field, a value to compare the field with, a return value if the field equals the value, otherwise another value if they’re not equal. It does get a little complex when you’re embedding functions within other functions.
Now you can use a smart search on the Status Match and the date field.
Note, in your example you matched with contains while I need to have an exact status match!
Hope this helps!
Hi Josh,
The nested search query building is only available on the Mac version at the moment. I never did get around to adding it to the iOS version. But I definitely want to spend some time building that function. It was easy on the Mac because that functionality is just built-in. On iOS I have to build it all myself.
Another option other than what Daniel has suggested above is to use theOR keyword in your Search Terms with just one instance of your Request Status field.
Request Status contains New OR In-progress OR Part on Order OR Waiting OR Landlord
Due Date Days is less than 30
You would also need a Calculation field to calculate the number of due days away.
So a Calculation field called Due Date Days would take the Due Date and TODAY() to figure out how many days away the Due Date is.
DAYS([Due Date]; TODAY())
Hope either of these solutions will help.
Thanks,
Brendan
Request Status contains <code>New OR In-progress OR Part on Order OR Waiting OR Landlord
Wow, that’s cool! Revisiting the on-line help, I realize that this is written there but I didn’t capture the meaning!
Thank you guys for the quick responses. The OR criteria in the search terms (I didn’t realize you could free text those in and it would only take one value from the pick list at a time) and the calculation field did the trick. Would love to see the nested search functionality come to iOS, but this a great workaround in the mean time. Thank you both!
.
