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)

Acknowledgements.JPG (22.36 KiB) Viewed 21740 times

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]

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.

filter.jpg (28.07 KiB) Viewed 21713 times

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

FIND DonationSplitDetail WHERE DonationSplitDetail.DateOfDonation>=LoggedInRegularUser.r_DateFrom AND DonationSplitDetail.DateOfDonation<=LoggedInRegularUser.r_DateThru AND
DonationSplitDetail.sc_Category=LoggedInRegularUser.r_SelectedCategory
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 DEFINED AND
LoggedInRegularUser.r_SelectedProgram IS UNDEFINED

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
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 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

error.JPG (14 KiB) Viewed 21698 times

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?