FIND Statement Syntax for Complex FIND

rbross

Hello,

I have a popup window for a report that lets the user enter a date range and select a sort option, either by Name or by Category. If they select By Category they can either choice to leave the following fields blank or select a value from the drop down for each fields, Category, Country, Program, (see pic)

If they leave them blank then the report will show records for all, if they select one or more then I need to do the FIND based on what they selected within the date range entered.
Question is, can I do this all within one FIND or do I need to break it up into a Nested IF THEN ELSE IF.... Statement creating a FIND statement for every combination.

Roger

BenHayat

Unfortunately Queries don't support IF THEN ELSE.
This has caused me lots of extra work in one of my complex search forms.

I made a Feature Request for that, but I doubt it will happen.

rbross

So it sounds like I have to qualify what the FIND statement will be before I do the FIND with several IF THEN ELSE IF Statements?
Like🙁I left off the BO name for simplicity)

IF SortOption=4 AND Category IS UNDEFINED AND
Country IS UNDEFINED AND
Program IS UNDEFINED
THEN
FIND...

ELSE IF SortOption=4 AND Category IS DEFINED AND
Country IS UNDEFINED AND
Program IS UNDEFINED
THEN
FIND...

ELSE IF SortOption=4 AND Category IS DEFINED AND
Country IS DEFINED AND
Program IS UNDEFINED
THEN
FIND...

ELSE IF...
THEN...
etc....
Until all combination have been covered. Does that sound about right?

BenHayat

As I said, the query editor doesn't allow IF statement. You have to use IF in a process first and for each find have a separate query.

IF SortOption=4 AND Category IS UNDEFINED AND
Country IS UNDEFINED AND
Program IS UNDEFINED
THEN
Query 1

ELSE IF SortOption=4 AND Category IS DEFINED AND
Country IS UNDEFINED AND
Program IS UNDEFINED
THEN
Query 2

ELSE IF SortOption=4 AND Category IS DEFINED AND
Country IS DEFINED AND
Program IS UNDEFINED
THEN
Query 3

ELSE IF...
THEN...
Query 4

rbross

Got it!
That's what I was trying to say with the FIND... representing the Query.
I will build out the IF Statements....
I wounder if a stored procedure would be better in this case?

BenHayat

rbross wrote
I wounder if a stored procedure would be better in this case?

I think so, if you just want result back as data.

tford

If you would be willing to break up the criteria into two steps: filter criteria + sort criteria, I think this might help you:

I have a very complicated query that allows users to filter a query based on various drop down filters. The criteria are collected in LoggedInSystemUser attributes and the query (not shown) is updated dynamically if a filter item changes. You will also notice the "clear filters" link that appears whenever at least one filter is selected.

The FIND in the query is very complicated:

FIND Student WHERE Student.Status='Active' AND (Student.Current_Grade=LoggedInRegularUser.Selected_Grade OR LoggedInRegularUser.Selected_Grade='All') AND (Student.Current_School=LoggedInRegularUser.Selected_School OR LoggedInRegularUser.Selected_School IS UNDEFINED) AND (Student.Current_School_Feeder_YN=LoggedInRegularUser.Selected_FeederYN_binary OR LoggedInRegularUser.Selected_FeederYN='All') AND (Student.T_020_PSD_Intent=LoggedInRegularUser.Selected_PSD_Intent OR LoggedInRegularUser.Selected_PSD_Intent='All') AND (Student.T_040_PlacementTestYN=LoggedInRegularUser.Selected_TestedYN_binary OR LoggedInRegularUser.Selected_TestedYN='All') AND (Student.T_090_Admitted_YN=LoggedInRegularUser.Selected_Admitted OR LoggedInRegularUser.Selected_Admitted='All') AND (Student.T_030_ShadowYN=LoggedInRegularUser.Selected_ShadowYN_binary OR LoggedInRegularUser.Selected_ShadowYN='All') AND (Student.T_100_HA_Invite_YN=LoggedInRegularUser.Selected_HA_InvYN_binary OR LoggedInRegularUser.Selected_HA_InvYN='All') AND (Student.T_720_Voucher_Candidate_YN=LoggedInRegularUser.Selected_ClevelandYN OR LoggedInRegularUser.Selected_ClevelandYN='All') AND (Student.T_050_Apply_YN=LoggedInRegularUser.Selected_AppliedYN_binary OR LoggedInRegularUser.Selected_AppliedYN='All') ORDER BY Student.Name_Last,Student.Name_First

In your case, if a user did not choose to filter on all of the criteria, that criteria would just be considered UNDEFINED and therefore not filter any items out of the underlying query.

rbross

That's what I was trying to do: See Red text below for if condition and Query. It returns nothing when I enter a value in Category but leave Country and Program blank/UNDEFINED

Green statements work and return a result

IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=3
THEN

FIND DonationDetail WHERE DonationDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru ORDER BY DonationDetail.sc_ContactLName ASC, DonationDetail.sc_ContactFName ASC

ELSE IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=4 AND
LoggedInRegularUser.r_SelectedCategory IS UNDEFINED AND
LoggedInRegularUser.r_SelectedCountry IS UNDEFINED AND
LoggedInRegularUser.r_SelectedProgram IS UNDEFINED

THEN

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

ELSE IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=4 AND
LoggedInRegularUser.r_SelectedCategory IS DEFINED AND
LoggedInRegularUser.r_SelectedCountry IS UNDEFINED AND
LoggedInRegularUser.r_SelectedProgram IS UNDEFINED

THEN

ELSE IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=4 AND
LoggedInRegularUser.r_SelectedCategory IS DEFINED AND
LoggedInRegularUser.r_SelectedCountry IS DEFINED AND
LoggedInRegularUser.r_SelectedProgram IS UNDEFINED

THEN

ELSE IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=4 AND
LoggedInRegularUser.r_SelectedCategory IS DEFINED AND
LoggedInRegularUser.r_SelectedCountry IS DEFINED AND
LoggedInRegularUser.r_SelectedProgram IS DEFINED

THEN

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
DonationSplitDetail.sc_Category=LoggedInRegularUser.r_SelectedCategory AND
DonationSplitDetail.sc_Country=LoggedInRegularUser.r_SelectedCountry AND
DonationSplitDetail.sc_Program=LoggedInRegularUser.r_SelectedProgram
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

ELSE
REPORT ERROR'Invalid Sort Selected For This Report'

tford

1) I think you can simplify the FIND substantially with:

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.sc_Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.sc_Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry) AND
(DonationSplitDetail.sc_Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram)

Using this approach will account for all combinations of the filter criteria and you only need to account for the varying ORDER BY selection.

2) I'm confused where exactly you are using IF .. THEN logic.

BLOMASKY

This would be a most excellent use for a Stored Procedure to build and sort your query.

Assuming you are using a SQL backend that supports Stored Proc.

rbross

I get this error with this Query:
ELSE IF CHARS_FROM_LEFT(LoggedInRegularUser.r_SortOption,1)=4
THEN
FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.sc_Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.sc_Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry IS UNDEFINED) AND
(DonationSplitDetail.sc_Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram IS UNDEFINED)
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

Can you do a query using shortcuts? sc_ is for a shortcut on Category, country and Program.

wonder if I need to add .Category to the LoggedInRegularUser.r_SelectedCategory attribute and Country and Program?

tford

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.sc_Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.sc_Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry IS UNDEFINED) AND
(DonationSplitDetail.sc_Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram IS UNDEFINED)
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

Have you tried just this?

rbross

Yes, I still got the error. 🙁

tford

Assuming you have reference attributes in DonationSplitDetail like DonationSplitDetail.Category, see below:

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry IS UNDEFINED) AND
(DonationSplitDetail.Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram IS UNDEFINED)
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

If you don't have those reference attributes, but instead have the shortcuts through another reference like DonationSplitDetail.Donation.Category, try this:

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.Donation.Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.Donation.Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry IS UNDEFINED) AND
(DonationSplitDetail.Donation.Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram IS UNDEFINED)
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC[/quote]

Both of these assume that LoggedInRegularUser.r_SelectedCategory is a reference and not a shortcut.

rbross

LoggedinRegularUser.r_SelectedCateory is a refference same for LoggedInregularuser.r_Selectedcountry and LoggedInregularuser.r_Selectedprogram

I don't see the difference in your last suggestion?

tford

Like like you are trying to compare a DonationSplitDetail shortcut to a LoggedInRegularUser reference

so I suggested adding a reference to DonationSplitDetail side of the equation in the FIND.

Not sure of your reference setup, but depending on your scenario something like:

DonationSplitDetail.Donation.Category

DonationSplitDetail.Category

rbross

If I do this query with a value in LoggedInRegularUser.r_SelectedCategory I still don't get anything back.

So that tells me something is wrong with this statement: DonationSplitDetail.Donation.sc_Category=LoggedInRegularUser.r_SelectedCategory
I checked to make sure LoggedInRegularUser.r_SelectedCategory has a value in it.

rbross

Solution was found 😃
I was not using the Reference Attribute DonationSplitDetail.PS_Category in the query I was using the Shortcut to DonationSplitDetail.sc_Category which was wrong.
Correct Query looks like this:

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
(DonationSplitDetail.PS_Category=LoggedInRegularUser.r_SelectedCategory OR LoggedInRegularUser.r_SelectedCategory IS UNDEFINED) AND
(DonationSplitDetail.PS_Country=LoggedInRegularUser.r_SelectedCountry OR LoggedInRegularUser.r_SelectedCountry IS UNDEFINED) AND
(DonationSplitDetail.PS_Program=LoggedInRegularUser.r_SelectedProgram OR LoggedInRegularUser.r_SelectedProgram IS UNDEFINED)
ORDER BY DonationSplitDetail.sc_Category ASC, DonationSplitDetail.sc_Country ASC, DonationSplitDetail.sc_Program ASC

Thank you to Tom Ford for the solution and pointing out the error of my ways 😉

Jaymer

also... to simplify your code, create a calculated field with that the sort value (1 thru 4) in it. that way you just check "LoggedInRegularUser.sort=1"

glad you got this working. I had a very similar thing that would syntax check but in essence was checking a string vs. a pointer and would crap out at runtime... same thing as here. very frustrating.