Nested sorts/filters using form controls

[seaken64]

I searched the forum but can't seem to find any discussions that touch on this problem.

I can't tell if the behavior in A5v7 is normal or not. I am unable to complete more than three query/sort operations, one on top of the other, using buttons on a form with an embedded browse. Let me explain:

I created a set with two tables - Transhist-----CUSTOMERS
I have about five indexes, Trans_Num, Cust_Num, Make, Brand, Model
I created a form with an embedded browse.

I start with the form showing all records in the set, sorted by Trans_Num. There are about 5371 records.
I want to narrow the list of records down by sorting on any of several category fields.
I created several controls on the form that, when clicked, will run a saved query/sort. I created the query's using the "Button" control and the query genie and have each one set to run the query on top of the current query, if one exists.

This works fine for about three or four button clicks and then I get an error and can't go any further. For instance:

Start with full 5371 records. Click on "Limit by Date" button, enter two dates for the range, and then the query shows only the records withing the date range. Lets say there are now only 3000 records selected. Next, click on "Limit by Brand" and enter a brand code. The query runs and narrows the selection to only the brand I typed in, choosing only from the 3000 records in the current query. The result is now down to 1000 records. Next, click on "Limit to service calls" and the sort narrows the list down to 300 records. Next, I click on "Remove Gas Appliances" and I will get an error. This last button was the fourth search in a row. It doesn't seem to matter in what order I select the sort buttons. The first three seem to always work fine, the fourth gets an error.

This leads me to believe the form only supports three of these types of buttons in a row. Not sure why. Maybe I have to use Xbasic to go deeper than three or four nested sorts?

Is there a way to run more than three or four nested querys/filters using form controls?

Thanks,
Sean Kennedy
weekend a5 developer

[MoGrace]

Re: Nested sorts/filters using form controls

Sean,
If you use table.query_create() to run the query you have a query option M to always create a new query. The problem is that Alpha only keeps 4 queries in the stack and unless one is dropped, the 4th query doesn't happen. The loading of the form itself creates the first query if there is an order or filter defined for the form. I have played with this ad nauseum but the option M does seem to help. Also using query_create() allows you to Show All whereas some of the other query methods do not. I have an example - I will try to zip it up. As I understand it the query.filter and query.order are global system variables which persist after the query is run. But there are other query methods with do not use these variables.

[seaken64]

Re: Nested sorts/filters using form controls

Thanks Robin. If only I understood how to use "query_create". How do I enter that command on a button control?

I have been using the genies to create my query. When I put the button on the form the genie starts up and I follow it through. I think this is Action Scripting?

Is the "query_create" command part of Xbasic? I've never tried using it. Can I enter this command with the genie?

I checked the online manual and noticed that there are three choices at the bottom of the Define Button dialog - Use Action Script, Use Xbasic, or Run a Script. Is this where I can use this "query_create command? I've never tried it yet. I'll experiment with it and see what happens.

My problem is that I am trying to run a store and contracting business and don't have as much time as I would like to develop my database. I tend to stick to what is quick and easy. But I know I could do more if I took the time to learn Xbasic. I'm still a hold out from the days when we used the "recording" application in Alpha Four to build menus. This scripting stuff is new for me.

Thanks,
Sean

[MoGrace]

Re: Nested sorts/filters using form controls

Sean,
Here's my example and a zip. I started the queries with the Xbasic genie and then tweaked the code a bit. Mind you I am NOT a programmer...

This example uses queryrun() and table.order() to run and release the queries during searches. The Show All button uses form methods to restore the form to its original state. The queries are run on the primary table (but not in a set); if you use a set to run on the child tables then the queries must be scripted differently - I have another example if you want to see it.

(Thanks to Dr. Bruce who let me mess with his tables)

[seaken64]

Re: Nested sorts/filters using form controls

You have answered my question as to whether the behavior I am seeing is normal - it is, since the stack stops at four queries.

You have also indicated that I can use a different method to accomplish what I am trying to do. I looked at the sample form and can see it working. But when I look at the Xbasic I can't really follow how to apply it to my situation.

When I created my sort buttons I used the Action Scripting genie. When I go in to edit the buttons the genie restarts and I can go through the menus and make different selections to change how the button works. In this example form you sent me the Xbasic editor comes up automatically, and there is sogn of a genie. This leads me to believe there are different modes within which to do work in a5.

I get that Action Scripting creates Xbasic code behinds the scenes. But how do I go back to the genie once the Xbasic is created and in use on the form? Or, do I have to now edit any changes in only the Xbasic code?

Can I apply the "queryrun() and table.order() to run and release the queries" technique using an Action Script genie? If so, what selections do I make in the genie menus to create this kind of code?

Thanks for the tips. At least I know what can be done. Now I just have to discipline myself to branch out beyond the simple point and click creation mode I am in.

Thanks,
Sean

[seaken64]

Re: Nested sorts/filters using form controls

When going through your sample form I noticed that the A-Z buttons limit the selection to only the records starting with the letter on the button. If the Show All filter is set first the selection is made from the entire table. If a Group filter is set first the selection is made from the group.

This is essentially what I am trying to do. The difference is that I start with "Show All", then "Group", then "A through M", then "C thru G", then "C only". Whittling the selection down step by step.

In my case:

Show All
--Limit to Date range
----Limit to Maker
------Limit to Model
--------Limit to Units Sold (no service calls)

Five queries in all.

So, I would want to "reset" only to the previous filter. Right? In other words, before running the Limit to Model query I would reset the stack to Limit by Maker, and so on.

I think I grasp the concept. I just don't know what I am doing in Xbasic.

Sean

[MoGrace]

Re: Nested sorts/filters using form controls

Sean can you zip the tables involved and share them with me?

When you are in the Code Editor - even with an Action Script, there is a toolbar button that will let you look at the code the genie created. It looks like little eyeglasses.

As I understand your purpose in limiting your list with these queries, is it to find a group of particular units sold and then locate the customer(s) who bought them? Even with Action Scripts this would be difficult as you have to consider the way the user might use your buttons - like what if he doesn't use them in the sequence you have designed? Also how many elements do you expect in each of the groups you have - is it limited or unlimited? For instance if MAKE is only 5 types and Models maxs out at 10 each, that is a more manageable list than say 100 Makes and however many models for each. Also I am curious what is supposed to happen once the units have been found?

I'd love to help if I can (perhaps others will join in) - it sounds like an interesting challenge!

Oh, and there is a genie button on the toolbar in the xbasic editor too which works like the Action Script genie only generates the code for you to edit and/ or copy into your script.

[seaken64]

Re: Nested sorts/filters using form controls

Yeah, I can do that. I don't have access to the database right now so I'll prepare the .zip later.

I took a closer look and now I understand that once I convert an action to Xbasic I can't go back and use the genie to edit it. It's a one-way deal. So, I am going to have to start getting more familiar with how to edit an Xbasic script.

Yes, the button are designed to narrow the records down to a managable size for printing labels or mail-merge. Also, I may want to contact only a certain group of customers, say, XYZ brand only. And out of that brand I may want to send a letter only to customers who use a gas stove, not wood or pellet stoves.

In the table I have set up fields to help categorize each transaction. Category fields are MAKE, BRAND, MODEL, JOBTYPE, FUELTYPE, UNITTYPE, VENTTYPE. These seven fields can have one of several categories entered from a drop down list or lookup table.

I set the tables up this way because I thought it would help me sort out the data for reporting. But now I am stuck on how to actually create the forms and reports. I think it is going to work fine once I get past my lack of knowledge. This is the first report/form I have attempted. I intend to create some reports that also order the records by these categories in a cascading fashion.

Yeah, it's an interesting challenge. I actually enjoy database work. Have been dabbling with it for years, starting with A4v2. But my main job is managing this business from day to day and I really don't have the time to do a good job of developing this database. And we never seem to have the money to pay someone else to do it. I'm closer now than I've ever been in getting a working customer database that my employees can use. But mostly, I set thing up so that I can use them and tend to forget that it needs to be easier to understand so that my workmates can use it too.

I'll get that .zip file together for you. It should illustrate what I have going better than I've managed so far.

Thanks,
Sean

[seaken64]

Re: Nested sorts/filters using form controls

I deleted most of the data and seeded with fake data, then zipped it up. It's still over 3.5MB. I hope it attaches ok.

Here goes,

[Tom Cone Jr]

Re: Nested sorts/filters using form controls

Sean,

How about giving us a break and telling us the steps to follow in order to see the problem you're having? Use specific form & object names, and mark the trail sufficiently so we can follow you even without knowing anything about your database. i.e. suggest actual field values we can enter as search or filter criteria.

[MoGrace]

Re: Nested sorts/filters using form controls

Sean,
Thanks for the zip, but I don't see the form that does the searching you described. Some of the tables listed in the database were not included - am I missing that form?

[MikeC]

Re: Nested sorts/filters using form controls

Robin,

Main Menu-->Reports-->view by models-->under the browse are the buttons.

I used the following without ANY errors.

sort by date---limit by date(01/01/2007 to 06/06/2007)---limit by maker(CFM)---limit by brand(VC)---limit by model(ENC)---limit by date(02/01/2007 to 02/07/2007).

[seaken64]

Re: Nested sorts/filters using form controls

Oops, sorry about that. I did forget to explain the routine. Mike figured it out.

Strangely enough I am able to use more than the four sorts with this sample data set. In the real database with over 5000 records I cannot go deeper that three sort buttons before I get an error. Now, with this small data set I do not get the error.

To illustrate the type of sort I want to accomplish:

Start with Show All (23 records)
--Limit by Date, 02/01/2007 to 06/01/2007 (15 records)
----Limit By Make=CFM (9 records)
------Remove Parts (5 records)
--------Service Out (3 records)
----------Limit by Fuel=Pellet *Error*

In the real database I will get the error when I try clicking on Service Out, in this example.

If I redo the search without the Limit by Date button I am able to get the list of pellet stoves only.

There does seem to be a limit to the number of buttons that will work in succession but it seems to have changed from when I was working with the 5000+ records to now when I am using less than 25.

I have one button named "Remove non-Units" that causes the error to happen quicker. If I use this button first in the sequence I will get the error on the second button. That button is set to sift out several categories using three "actions" from the genie.

Now, how can I go in and change the scripts for these buttons so that they work like the alphabetical sort in the sample you posted for me?

Thanks,
Sean

[seaken64]

Re: Nested sorts/filters using form controls

Sean,
Thanks for the zip, but I don't see the form that does the searching you described. Some of the tables listed in the database were not included - am I missing that form?

I removed some tables because they were not being used for this exercise. They were only being used while I was converting this database over from Access and Excel files.

The form is called Models_Brief. It is accessed from the Reports menu, then View by Model. I originally started the form with the intention of finding records by the Model code. Then it morphed into this drill down sort routine so that I could quickly access records limited by criteria other than model. It's a work in progress.

[seaken64]

Re: Nested sorts/filters using form controls

Robin,

Main Menu-->Reports-->view by models-->under the browse are the buttons.

I used the following without ANY errors.

sort by date---limit by date(01/01/2007 to 06/06/2007)---limit by maker(CFM)---limit by brand(VC)---limit by model(ENC)---limit by date(02/01/2007 to 02/07/2007).

Yes, interesting. I noticed that also. But if I try it differently, as noted below, I get the error. Maybe it is related to the button on the second row only. I get the error when I remove parts. But not when using only Date, Make, Brand, and Model. I'm going to try this on my large database and see what happens.

I don't normally use all three Make, Brand, and Model. Its usually one or the other. But it might give me a clue.

Thanks,
Sean