Custom Query

[forskare]

I created a custom Query using action scripting: Query, Run Custom Query, Chose a single table, Checked the Prompt for Parameters at Runtime check box and entered 3 parameters; Lastname, Firstname and Address. Everything else is default. When I run the query by either last or first name, rather than finding the record, it sorts all records by record number. I've recreated this 3 times and each time with the same result. I tried to locate some way to bind the search parameter to the lastname and firstname fields but there is none. It just seems to me that this is the missing link and the reason why I cannot get it to work as it should.

Ideas or suggestions please.

[Tom Cone Jr]

Re: Custom Query

The Run Custom Query genie includes space for BOTH a filter expression and an ORDER expression. Sounds like you overlooked the ORDER expression in building your query.

[forskare]

Re: Custom Query

The Run Custom Query genie includes space for BOTH a filter expression and an ORDER expression. Sounds like you overlooked the ORDER expression in building your query.

I Tom,

I'll try it again, but in one of my attempts, I did create an order using the 3 fields. Perhaps I should have wrapped in with Eval((). I'll give it a try.

Nope! That didn't work. Does the same thing. I didn't find any documentation on this subject as well.

[Stan Mathews]

Re: Custom Query

Ideas or suggestions please.

Suggestion? Attach a sample table and support files which illustrate the issue.

[forskare]

Re: Custom Query

Suggestion? Attach a sample table and support files which illustrate the issue.

Wish I could. For some reason when I click upload, it goes to never, never land. This is the step by step

On a form, I created a button using action scripting to call the query.

I chose Query,
Run Custom Query. The Script Genie Run Custom Query window pops open.
Leave the Query Records in current form/Browse as is.
Leave the Perform Query at which Level as is.
Check the Prompt for Parameters at Runtime checkbx.
Click Define Parameters
Add a Parameter, I used Lastname for the Last name field, enter the Prompt and leave text box.
Click OK.
Leave the rest as default.

As you will see, there is no way to select binding so anything that's done is done in the clouds. When the button is clicked, you will be prompted to enter a last name. Then click OK. Nothing is done except to reorder the records. Without binding, there is no way it will work.

[Al Buchholz]

Re: Custom Query

Kenn

Email the sample to me...

[Stan Mathews]

Re: Custom Query

Without binding, there is no way it will work.

Precisely.

entered 3 parameters; Lastname, Firstname and Address.

Having done that, write the filter for the query using those as if they were variables.

lastname = var->lastname .and. firstname = var->firstname .and. address = var->address

The Run Custom Query action queries records based on custom filter and order expressions. This query can include parameters prompted for at run-time. If you choose to prompt for parameters at run-time, you can customize the prompts, title, and buttons on the parameters dialog.

Procedure
1.
Make a selection in the Query records in which Form/Browse window? field. This field will be grayed out unless this action is based on a Form or Browse. The choices are:

�
"Current Form or Browse Window".

�
"Another Form or Browse Window".

2.
If you selected "Another Form or Browse Window", in the Window Name field select either:

�
"Forms"

�
"Browses"

3.
Select one of the listed forms or browses.

4.
Optionally, check Prompt for Parameters at run-time? to display the Define Parameters button. The resulting <span class=Screen>Define Parameters</span> dialog box lets you define the parameters that the user will be prompted for. When you add parameters, you are creating a dialog box to collect variables from the user at run-time. Once you have created the parameters for the query, you will then create an expression using the parameters on which the query will be based.

5.
Optionally, enter a character filter expression that selects records from the table in the Filter Expression field, or click the button to open the <span class=Screen>Filter Builder</span>.

6.
Optionally, enter an character order expression that sorts the number of records returned in the Order Expression field, or click the button to open the <span class=Screen>Order Builder</span>.

7.
Click the Query Options tab.

8.
Optionally, check the Combine filter with existing filter (if any) check box.

9.
Optionally, check the Exclude records selected by filter check box.

10.
Optionally, click the Tips on Parameters tab.

11.
Click Next >.

12.
Optionally modify the comment that describes the action.

13.
Click Finish.

[forskare]

Re: Custom Query

I sent it to your support email.

[forskare]

Re: Custom Query

Hi Stan,


I tried the filter several ways, as is, without the parameter names, and both ways wrapped with Eval(). I also included a sort order but that should have no effect on it. Anyhow, each time I got an Invalid filter or sort order every time.

I know I'm not the only one that can no longer upload files but maybe that will get fixed someday.

Thanks,

[Al Buchholz]

Re: Custom Query

I sent it to your support email.

Kenn

Check your email...

[Tom Cone Jr]

Re: Custom Query

Inquiring minds want to know how this turns out. Don't forget us!

[forskare]

Re: Custom Query

Nope.

Part of the problem with Stan's suggestion was that I inadvertently added a space between last name and Alpha created the variable as Last_Name. Once I changed than it 'almost worked. I didn't ge any error messages but the query did not work. Instead, it found nothing and showed an empty browse instead of 8 records with the last name of Smith. I did more research and tried this filter:

(lastname = var->lastname) .and. (firstname = var->firstname) .and. (address = var->address)

It produced the same results, filtering out all the records. So, it's closer but still, no cigar.

The reason it filtered out all the records was because of the .and. which meant a value had to entered into all three text boxes. I changed the .and. to .or. but this simply reorders the browse.

[forskare]

Re: Custom Query

Kenn

Check your email...

Al,

Should be in your inbox.

[Al Buchholz]

Re: Custom Query

This is what I put together for a sample from what Kenn sent me.

sample1.zip

[forskare]

Re: Custom Query

I opened the file and gave it a ride but it does nothing. I see there isn't anything in the filter so I used the genie to include the followint:

(( Var->Lastname $ lastname ) ) .or. (( Var->Firstname $ firstname ) ) .or. (( Var->Address $ bill_addr ) )

This simply resorts the filter.

Next, I changed the expression to:

Var->Lastname $ lastname

This found all records containing the characters I entered into the textbox, "smith".

Next, I tried:

Var->Firstname $ firstname but that didn't work. Why, I'm not sure but I'll work with it some more and see what gives.

This raised the question, How is this custom query supposed to work? The custom genie allows for any number of conditions to be set. In this case, I created 3, Lastname, Firstname and Address. The goal is to allow the user to search only by Lastname, First name, address or any combination. The question is, Will Alpha allow this or if there is more than one condition, will it require all conditions to be met?

+++++++++++++++++++++++++++++

I created a Custom query for each of the three conditions and it worked as advertised. However, it will only work with one field at a time. More than one condition can be set for each field but only one field can be used per query. (This is a bit redundant but I want to be clear) If you want to have one button open a custom query with more than one field to search, individually or together, it will not work. This is the filter as defined by the genie. I set the Lastname and Firstname to 'Begins with' and the address as 'Contains'. ( Var->Lastname $ lastname ) ) .or. (( Var->Firstname $ firstname ) ) .or. (( Var->Address $ bill_addr )) My testing showed the first part of the query would work on the last name part but not the Firstname or Address part. Those two merely resorted the browse. Perhaps someone might be able to rewrite the filter so that it can work with just one button.

Summary:

This is the basic idea I have in mind for which I started the other post about a much needed search for the desktop which doesn't need any code to create. Ideally, it would match the search part of a grid.