Wildcard filters in variables

[Dan Blank]

Can you use wildcards in variables to query a database for a report. I have a report menu where the user enters a start date, and an end date (varialble fields). Then they enter a description (variable field) and then press the report button. Basically this works fine. However, some descriptions are Oscopes and some are Oscilloscopes. So if they could put Osc* it would pick up all records with Osc*.

I can't get this feature to work. What needs to be done to make it work?

Thanks
Dan

[Tom Cone Jr]

RE: Wildcard filters in variables

Dan, I see that no one has offered any suggestions yet. Personally, I haven't tried to embed wildcards in variables in hopes of retaining wildcard functiionality.

Describe how you run the query. Are you building the query filter expression in an xbasic script, for instance? If the "menu form" you describe is based on the table you want to run the query against, is there some reason you don't want to use QBF (query by form) to generate the filtered view of your data before runniing the report?

-- tom

[Bill Warner]

RE: Wildcard filters in variables

Here's one suggestion that has worked for me: I have used a query to filter for the dates, and then put the variable in the report filter:

*FIRST(NAME,VAR->DESC)

A word of caution: This can sometimes work against you. For example, if you want to find ONLY the name SMITH, this filter will also find SMITHSON. A way around that is to put a space after typing SMITH.

Putting part of the filter in the report has an added advantage. If you run the complete filter first, and then specify values that result in finding no records, the report will run based on the LAST selection of records (which could be the whole table). Having part of the filter in the report will give you a "no records" message.

[Dan Blank]

RE: Wildcard filters in variables

Attached is my application. The instructions of what to do are on the main menu page. You can try it from there.

[Tom Cone Jr]

RE: Wildcard filters in variables

Dan, I see what's happening in your app.

You have included a specific filter expression in the design of your report. This expression uses the values of variables you "collect" on your form. Unfortunately, the filter expression is not smart enough to recognize and translate the wildcard (*) character, and the value of the variable, including the wildcard character are used to build the search string. Since there are no records which contains values matching the specified variable (which now includes the wildcard character), the search fails, and your report shows no matching records.

The trick to getting past this is to let go of the filter expression in your report. Just delete it, and set the report details to "base report on current selection of records".

Then you have to run a query against your records before calling the report. Once the query is in place (and only the records matching the query specifications are "visible") you call the report, and it prints only those in the current view.

There are at least 2 ways to run a query against your records before calling the report.

Perhaps the easiest is to design a new form, based on the same table as your report. Include only those fields which you want the user to use in "filtering" the table. Add a button to print the report. The genie can help you with that. Use the default menu for your form, so that you are sure to have access to the Query menu items.

Then try this. Open the new form. Run a Query by Form to select only the records you want included in the report. Then push the button to run the report. Should work like a charm... and... an added bonus... the usual query operators are available to you as you specify the query. You can use the wildcard character, greater than, less than, between, and so on.

A more complex way to do the same thing could be constructed using xbasic. This would conceal the query by form functionality from the user, but would require you to build in the necessary code to parse the query operators you want to support. In this scenario you would use variables to collect the desired search parameters from the user. Your code would then parse each variable to determine if any operators are present. If so, your code would translate the variable and operator into a new combination which would be understandable to the query.filter() function. After all are parsed, a query.filter expression would be concatenated, and then the query would be run... all in xbasic. Finally, the report would be called, and since it's now set to run against the currently selected records only those matching the new query will be included in the report.

Hope this helps.

Let me know if you need an example of either technique. I could add it to your app and email it back to you.

-- tom

[Dan Blank]

RE: Wildcard filters in variables

Hi Tom:

Yeah I know I can use query by form, however, I am writing this app for a company. They want simple straight forward ways of getting their data. IF I use a query by form, I have to instruct them on how to use a between statement in the date field. That's why the variables are so perfect. It is a straightforward method that makes it easy and understandable for them.

Your xbasic method sounds the hardest of the two solutions, however, appears to be the more user friendly of the two. I guess I'll give it a go. You will probably be hearing from me again.

I Thank You sir! I appreciate you taking the time to help me (again). Hope you are doing well.

Dan

[Dan Blank]

RE: Wildcard filters in variables

Well Tom,

Call me thick headed. I reread your solution and realized that I could still use the date variables on the form and omit the description variable. Then just use the description field. Run the Query by form... fill in the dates, and fill in the description....Bingo hit the print button like you suggested....Volia There she be.

Thank You for your help. I do appreciate it.
Take care and have fun!!!!!

Dan

[Tom Cone Jr]

RE: Wildcard filters in variables

Dan, that sounds like just the ticket for your application. You're using the Description field to run the QBF, and the date variables are being used by a filter expression in your report, right? Sort of two step filter, if you see what I mean?

Also, you might want to verify that the date fields actually have values in them when the user pushes the print button. If they're blank, the user could be reminded to supply the missing data before the call to the report.

-- tom

[Robert Picard]

RE: Wildcard filters in variables

Hi Dan

Have you thought about using "substr"

May thought was that after they enter a description you could take your variable i.e.

'check for wild card
if *any (var->description,"*") then
'remove wild card from the variable
desc_filter = left(var->description,1)
'get the substring length
sub = len(desc_filter)
'use substring to find closest match

Then you filter would contain

if(*any (var->description,"*"),substr(field->description,1,val(sub))= var->desc_filter,field->description=var->description)

This is very rough but gives you an idea.

"Substr" would have the same effect as looking for only the first 3/4/5 letters before the wild card symbol.

Hope this helps

Robert

[Dan Blank]

RE: Wildcard filters in variables

Hi Robert:

Thanks for the suggestion. That's not a bad idea. However I managed to make Tom's idea work rather nicely.

On the TE form I put the Begin date and End date variables. They run a QBF using wildcards. Then fill in the date variables and press the print report button. Bingo there it is. The nice thing about doing it this way is that they can use description or part number or site codes or manufacturer wildcards or a combination of any or all of the choices at the same time. So I ended up offering more choices to the user.

It works nicely.

Thanks for the suggestion though.
Dan