Easiest way to implement multiple search term text list?

I’m trying to develop a text list tied to my Pan X database through Database Navigator that uses multiple search options to narrow the list (Entity, Start Date, Stop Date, Low Amount, High Amount, Check Number, and Invoice Number).

Not all search boxes are in use in all cases, so the text list query has to be able to take the lack of a value in any/multiple boxes into consideration.

I’m having trouble getting the simple query in the text list options to work with this more complicated search scenario (too complicated). Should I abandon the text list query and generate the text list with an arraybuild? If so, how do I then tie the text list back to select records in the database (like Database Navigator does)?

Multiple search options can certainly be done. The most elegant example is what Jim’s built in Find/Select dialog, which I believe he created using just the PanX tools available to us. His code for that is probably viewable within PanX if you know how and where to look for it. I haven’t tried anything that fancy, but I have made a working text list, tied with Database Navigator, in which the query is built from up to 14 different inputs!

I put multiple different search boxes, starting with the style the Construct Menu creates, each tied to their own file global, in three rows below my Text List Object. I used their Prompt settings to show each box’s purpose and sized them as needed.

Building the query was initially a trial and error exercise in Boolean logic, combining multiple bits with And, Or and (). Practice helps; I’d built simpler multiple search forms before my 14 input one. Figure out and test a query for each separate search separately. Then combine them one at a time, testing as you go. Surrounding each with (), using “And” between each set, should give a working overall query. Handle empty values like this: (Entity="" or Entity contains entitySearch) You can substitute whatever operator you wish for the contains, but the first part will give you a match if that box is empty. If you want to further restrict your searching to just the records currently selected in the data sheet, add a info("visible")=true() clause to your query. To just optionally restrict that, tie it to whether a data button is checked.

A long complicated query of a large database may give sluggish performance. The “Form Programming” training video showed me how to improve that. Most of the time I don’t need all my 14 criteria and you probably won’t need all your 7. I modified my form so each search box could be clicked between on or off. The switches call a procedure that determines which boxes are active. Then combines the query pieces for just those into the simplest needed overall query. And replaces (using a Changeobject statement) the TLO’s query setting with that. So the overall search is as fast as possible.

As form space was tight, rather than adding data buttons paired to each search box I customized my search boxes:
Chart%23On vs.
Chart%23Off
It’s not standard Mac UI but it should be clear and shows off PanX’s power. The ‘icons’ are rich text characters, respectively char:0xf002:FontAwesome::33FF33 and char:xF078:Webdings::FF3333. Clicking them triggers the code in their procedure code pane. That reads the current value there with Objectinfo, changes it to the other with Changeobject, then calls the procedure to adjust the TLO’s query.

I also made non-standard customized search boxes for better use with numerical data, replacing my text ‘icons’ with a miniaturized pop-up button:
off AmountOff chosen operator Amount%3C
AmountPopUp search by a comparison math operator of your choice!
The pop-up also triggers calling the procedure to adjust the TLO’s query accordingly. I could have used a pop-up menu instead, but I couldn’t find a text version of the pop-up icon Jim used on his button and didn’t like the alternatives I saw as well. The look is a bit kludgey, but it’s the best I could do.

Yes, this is true, and the code can be viewed. However, I don’t really recommend this as an example because it is super complicated. It took me over two weeks to write this.

The basic technique is described under Complex Searches on this help page:

Your application sounds really cool! :call_me_hand:

Okay, I got it all working with judicious use of AND and OR statements and a boatload of parentheses in the query. And the speed is not bad (although I haven’t yet tried it on a full dataset).

Thanks for making me revisit the possibilities of AND and OR, without the use of if/then statements (because they are that, in essence).