Making a filter dynamically

[Mike Wilson]

I'm trying to build a filter dynamically from other variables and values. The code I've been working with is below. The filter expression if expressly written is between the %c% delineators. I've tried multiple combinations including a host of variations with eval() and evaluate_template() and I just can't get to the endpoint. I'm actually not certain it's doable. If anyone know the solution, I'd appreciate having it. Thank you.

dim vfilter_p1 as C
dim vseq_no as C="1"
dim vproj_no as C="PRJ.00833"
dim vfilter_var_name as C
dim vfilter_val as C
dim vproj_name as C

<<%c%
vfilter_p1="proj_id="+quote(vproj_no)
%c%

vfilter_var_name="vfilter_p"+vseq_no
vfilter_val="proj_id"+"="+quote(vproj_no)

'=========  WHAT TO DO HERE TO MAKE THE FILTER FROM PARTS ==================

vtemp=vfilter_var_name+"="+vfilter_val
msgbox(vtemp)

eval(vtemp)
msgbox("21",vtemp+crlf()+vfilter_p1)

'===========================================================================

'APPLY THE FILTER
vproj_name=lookup("projects",vfilter_p1,"proj_name")
msgbox("27",vproj_name)

[Mike Wilson]

I figured it out.

dim vseq_no as C="1"
dim vproj_id as C="PRJ.00833"
dim vproj_name as C

<<%c%
WHAT THE FILTER SHOULD LOOK LIKE
vfilter_p1="proj_id="+quote(vproj_id)
%c%

'BUILD THE FILTER VARIABLE
evaluate_template("dim vfilter_p"+vseq_no+" as C="+chr(34)+"proj_id="+chr(34)+chr(43)+"quote(vproj_id)")
msgbox("Line 20",vfilter_p1)

'APPLY THE FILTER
vproj_name=lookup("projects",vfilter_p1,"proj_name")
msgbox("Line 24",vproj_name)

[Mike Wilson]

I figured it out.

dim vseq_no as C="1"
dim vproj_id as C="PRJ.00833"
dim vproj_name as C

<<%c%
WHAT THE FILTER SHOULD LOOK LIKE
vfilter_p1="proj_id="+quote(vproj_id)
%c%

'BUILD THE FILTER VARIABLE
evaluate_template("dim vfilter_p"+vseq_no+" as C="+chr(34)+"proj_id="+chr(34)+chr(43)+"quote(vproj_id)")
msgbox("Line 20",vfilter_p1)

'APPLY THE FILTER
vproj_name=lookup("projects",vfilter_p1,"proj_name")
msgbox("Line 24",vproj_name)

[CraigSchumacker]

Mike:

I created a PROJECTS.DBF with 2 character fields PROJ_ID and PROJ_NAME.

Something about LOOKUP() is behaving peculiar by me... On 2 different versions of AA.

Rem out lines 20 and 21 in your code above..

These two lines:

eval(vtemp)
msgbox("21",vtemp+crlf()+vfilter_p1)

Run your code with projects.dbf with no open forms or browses. Your LOOKUP() fails, right? Not an open index error?

Now, open projects.dbf default browse.

Run your code again. Does the LOOKUPC return the PROJ_ID?

Attached is MY PROJECTS.DBF. Be careful not to overwrite yours IF you need it.

If your test results are the same, it may be LOOKUP() is buggy. I almost never use it, and it may work in other scenarios.

I did try a few other filters, all of which worked when the default browse was open, but failed if it was not.

(Honestly, I open a table, create a query, and fetch as needed - for better or worse...)

At any rate, you can create a dynamic filter, but LOOKUP() may be off the table.

[Ted Giles]

Mike, I use a Quick filter button.
it's a drill down based on a Browse.
Click the field, filter on that with the button.

Say you want a transaction on a specific date. Then a type of transaction.
Click date - filters on the date chosen
Click transaction type - filters on date AND type.
all in user control.
Then I have a "Print what you see" button.

[Ronald Anusiewicz]

Your example on line 23 is mis-spelled:

vfilter_var_name="vfilter_p"+vseq_no I believe should be "vfilter_pl

Not sure what you want to do but I built a table defined below.
My example works fine.

'projects.dbf
'
'Proj_Id Character 16 0
'Proj_Name Character 16 0
'
'record 1
'PRJ.00833
'Test1


dim vfilter_p1 as C
dim vseq_no as C="1"
dim vproj_no as C="PRJ.00833"
dim vfilter_var_name as C
dim vfilter_val as C
dim vproj_name as C

<<%c%
vfilter_p1="proj_id="+quote(vproj_no)
%c%

vfilter_var_name="vfilter_pl"+vseq_no
vfilter_val="proj_id"+"="+quote(vproj_no)

'========= WHAT TO DO HERE TO MAKE THE FILTER FROM PARTS ==================

vtemp=vfilter_var_name+"="+vfilter_val
msgbox("29",vtemp)

'eval(vtemp)
'msgbox("21",vtemp+crlf()+vfilter_p1)

'===========================================================================

'APPLY THE FILTER
vproj_name=lookup("projects","proj_id="+s_quote(vproj_no),"proj_name")
msgbox("38",vproj_name)

vFilter = "proj_id="+s_quote(vproj_no)

vproj_name=lookup("projects",vFilter,"proj_name")
msgbox("43",vproj_name)

[Al Buchholz]

Mike

What are you trying to do in your application that moves you in the direction of building a filter dynamically?

Call me confused. And when I'm confused I step back and wonder....

[Tom Cone Jr]

I share Al's confusion. Why is another layer of 'indirection' needed? Are you trying to give the user the ability to create their own query filter expressions in a runtime environment?

[Mike Wilson]

Hi all,
Yes Tom, that is what my goal was, which I have achieved.
We are a Project centered business that uses crowdsourcing for field service workers to perform 3 different work types -audit retail stores, install products in stores, and remove products in stores. I receive a daily reports of the work orders with the Projects, work type, work status...etc from the field services web site. I have developed a method to data mine the daily downloads giving the user the capabilities of selecting three search fields, narrowing the selections with prior selections, to generate a narrowed filter. Works well. I just needed the capabilities to build the filters for secondary; fields and field choices as the user selections were made. I got it worked out. I'll see if I can reduce this to a postable example for others to see.

[Mike Wilson]

Here is the example I promised.

[Al Buchholz]

Here is the example I promised.

Mke

I'm not seeing the example. Did the post to the board actually work?

[Ted Giles]

I downloaded it OK, Al.
It's a nice piece of work. I would have taken the easier option;
https://www.screencast.com/t/Kz1l7rrY5K

[Al Buchholz]

I downloaded it OK, Al.
It's a nice piece of work. I would have taken the easier option;
https://www.screencast.com/t/Kz1l7rrY5K

Interestingly, if I log out and then view the posts I see many more things. signatures, attachments, colors. My settings must be really messed up...

[Ted Giles]

The log in process seems to have changed, Al.
Do you want me to mail it to you?

[Al Buchholz]

The log in process seems to have changed, Al.
Do you want me to mail it to you?

I have the video now. Got the issues cleared up by pressing the Posts tab instead of the recent activity tab