Child Table Browse Filter

[Mortie]

I am sure this is simple but I still have racked my mind to figure it out.

master table has field called ID
child table has link to master via same ID field

child table has field called Sales_Year

I want to filter the child table based on Sales Year and use a DDB with a list of years to determine the year upon which to filter the child table.

There is no year field in the master table

The records in the child table will be multiple results across multiple years.

HOw can I do this or am I trying something that should be done a different way?

Thanks

[Tom Cone Jr]

Re: Child Table Browse Filter

I always chuckle when someone says "I'm sure this is simple..." Thanks for the grin!

There are multiple steps required to implement your idea. Are you hung up on any particular aspect, or are you just looking for general design suggestions?

It occurs to me that you could use the Tablemin() and Tablemax() functions to retrieve the earliest and latest Sales_Year from the child table. These could be used to populate an array of sales years beginning with the earliest and ending with the latest. The array could be displayed on your form using a listbox (or other) control. Doing it this way you would not need to store years in a second table or file.

The fact that the master (parent) table doesn't have a Sales_year field is a matter of no consequence. You can run a query against only the current instance of the child table.

-- tom

[Mortie]

Re: Child Table Browse Filter

Tom, humor this time a year is almost always mandatory!!!!

I am hung up because I have tried creating a variable called SalesYear, attaching an onchange event to the variable, followed by a QuickFilter on the variable.

I have tried running a Custom Query with the same variable and OnChange event also to no avail.

The result of the above efforts consistently cause a malfunction of the browse.

When I use the OnChange to run a saved query the browse does not change to the requested year; it leaves all the years visible.

When I am on a row in the browse and highlighting the year field and then do a Quick Filter with the QuickFilter Icon provided by Alpha, it works perfect. This may be my best option but I was hoping to use the variable versus working from the browse

To create the list I was simply using the DDB as a record list on the child table.

As to the master table, I just mentioned that because I was thinking someone might ask if I could link on the year, which I could see no logical reason to do anyway.

Thanks

[Tom Cone Jr]

Re: Child Table Browse Filter

Here's an example. Unzip to an empty folder.

-- tom

[Mortie]

Re: Child Table Browse Filter

Thanks Tom. It works like a charm.

I am comparing your X-basic scripting with that generated by what I did with Action Scripting. I am trying to understand why yours functions so nice and smooth and mine works like a piece of garbage. I understand the high level prinicples of x-basic but never have taken the time to learn how to use it. The life of a salesman leaves something to be desired as a coder. My hunch is that the Action Script I have designed seems logical on the surface while the actual script is not doing what I think it should do.

Here is the code generated by Action Script. I used to conditional formats......one if variable says All and the other if not equal to All

'Conditional code follows. Executes only if the condition expression is True.
IF a5_eval_expression("=Var->vc_curr_yr=\"All\"") THEN
'Turn off query in in current form.
topparent.show_all()

END IF

'Conditional code follows. Executes only if the condition expression is True.
IF a5_eval_expression("=Var->vc_curr_yr<>\"All\"") THEN
'Default comment text.
DIM records_found as N
records_found = topparent.queryrun("Sales->Sales_year = Var->vc_curr_yr","","","No","<Cross Level>",.f.)
END IF

As compared to your X-Basic use

DIM records_found as N
if vc_Curr_yr = "All" then
records_found = parentform.queryrun(".T.","ID","","No","Sales",.f.)
else
records_found = parentform.queryrun("Sales_year = '" + var->vc_curr_yr + "'","id","","No","Sales",.f.)
end if
statusbar.set_text(ltrim(str(records_found)))

[Tom Cone Jr]

Re: Child Table Browse Filter

The key differences are these:

a) you omitted an "order" expression from your query definition; and

b) you told Alpha Five to do a cross-level query, and I told it to query the Sales table only.

In A5v7 you should always specify an "order" expression when using <form_ptr>.queryrun().

"Cross Level" queries are explained reasonably well in the help file.

Querying the records in a set based form can involve at least 3 very different situations:

a) the filter is applied to the parent table restricting the display of parents, but not restricting the display of their linked children; or

b) the filter is applied to both parent and child tables, showing only those parents who have children that meet the search criteria, and only showing the children who meet the search criteria; or

c) the filter is applied solely to the child table, showing all parent records regardless if they have children that meet the search criteria, and only showing the children who meet the search criteria.

Your Cross Level query was trying to do #2, while I did #3.

Hope this helps. --- tom

[Mortie]

Re: Child Table Browse Filter

THANK YOU TOM!

I have been an Action Scripter since Moby Dick was a minnow and I have enver done anything but a Cross Level. This is also the first time what I wanted to do didn't work so stupid me never knew that was possible. I made the change and it works like it is supposed to.

I am always in awe what I can learn from the experts as I try to develop an application.

Thanks again for taking the holiday time to help!

[Tim Kiebert]

Re: Child Table Browse Filter

Tom,

Please forgive an impertinent student questioning the teacher:) but shouldn't your filter type b) instead of this

b) the filter is applied to both parent and child tables, showing only those parents who have children that meet the search criteria, and only showing the children who meet the search criteria;

read thus

b) the filter is applied to both parent and child tables, showing only those parents who have children that meet the search criteria, and showing all the children for the parent;

or would that be a d) type.

Just going by what I've seen and used and read (FLATTENQUERY() and CROSSLEVEL() Compared)

[Tom Cone Jr]

Re: Child Table Browse Filter

Tim, I think that would be a d) type, depending upon whether fields in the child are used in the filter expression to identify the parents (only) or to identify both parents and children to be shown. It's difficult to discuss these in generalities.

It would be great if you took my example and extended it to show all 4 types for Bob (and others).

[Tim Kiebert]

Re: Child Table Browse Filter

Tim, I think that would be a d) type, depending upon whether fields in the child are used in the filter expression to identify the parents (only) or to identify both parents and children to be shown.

It's difficult to discuss these in generalities. Agreed

It would be great if you took my example and extended it to show all 4 types for Bob (and others). I will see what I can do. How soon will depend on if I get any new toys for Christmas. (not to mention we already have v8 to play with)