Order/Filter based on Child Table

[Art Cordaro]

Hello,
I want to capture and use a date field in a child table so I can use it in my Parent form to do a , sort, query or place a Order / Filter on the form.

My child table is for notes on a client accounts.
The fields are Line # , Date , Time and Comment.

What I want to do is capture the Maximum date and post it to a field in the parent table so I can sort or query and or filter on.

I want to be able to see the client account that has the latest date. So They are followed up by my office personnel.

Any help would be appreciated. Oh I did try to look through the treads and could'nt find what I needed.

Thanks.

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Art,

How do you define 'Maximum' ? Oldest? Latest?

-- tom

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Art,

In more general terms, A5V4 doesn't handle cross-level queries well. If you are trying to find primary table records based on criteria in link child table field you must either use flattenquery() or you must build an inverted set.

Personally, I prefer the inverted set. The original child table becomes primary and is linked one to one to the former primary table. In this inverted arrangement you can easily filter the records to find the latest or earliest dates in your set.

-- tom

[Art Cordaro]

RE: Order/Filter based on Child Table

Thanks Tom for the help.

I placed a calculated field on the Parent Form

maximum(Collection_Notes->Date,GRP->DEBTOR_CARD)

However I can not Sort or Query on it.

Art

[Art Cordaro]

RE: Order/Filter based on Child Table

How do I use a flattenquery().

I tried the inverted way, however my child table is one to many and it did not work out.

Thanks again

Art

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Art,

Re-visit the inverted set.

I'll try to give you better instructions.

Assume you have a set that contains two tables, Invoice_header and Invoice_lines. The link is one to many based on a unique invoice number.

To invert this arrangement simply create a NEW set, giving it a new name. In this set the Invoice_lines table is primary, and Invoice_header table is child. AND the link is one to one, not one to many. Link field is the same, invoice number.

This arrangement is very very useful for reports or labels. Less so for forms.

Flattenquery is explained in the xbasic reference manual.

-- tom

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Art,

Shouldn't it be:

Maximum(your_date_field,GRP->Your_Primary_table,Grp->Your_line_item_table)

??

i.e. passing three arguments to the function... per the example in the xbasic reference?

-- tom

[Art Cordaro]

RE: Order/Filter based on Child Table

Tom

I'll give it a try and let you know how I did.

Thank you

Art

[Art Cordaro]

RE: Order/Filter based on Child Table

Tom,

Tried the inverted set. It worked but it's not what I am trying to accomplish. I need to be able to order my parent form based on the child table.

I created a collection agency database and I want to be able to have accounts that have been worked by the earliest date to the oldest date. This date is held in the child table which is embedded in the Parent Form.

I hope I'm explaining this better than before. I am reading on the Flattenquery() pages 214-217 in the Xbasic

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Art,

I think your best course would be to post the last transaction date in the header table each time a transaction record is entered or changed. The post field rule permits one to 'replace' an existing value.

If your header table had the date of the most recent transaction in it already, it would be a simple matter to sort the set on that field, in descending order, to present the records in sequence with the one which has been outstanding the longest on top.

By the way for most, I'd argue that 'earliest' and 'oldest' are the same date. This language stuff gets slippery when describing date ranges.

It would be possible to build an elaborate script to accomplish the same thing, without adding another field to the header table, but it will take a lot of work and would not be updated automatically. There may be more elegant solutions, but a brute force approach is all that occurs to me:

a) sort the transaction table in by account number and transaction date (descending).
b) Fetch through the table filling an array with the most recent transaction date for each collection of account transaction records.
c) close the transaction table when the array is populated. It now contains one element for each account, and it contains the last transaction date.
d) Sort the array in order by date, descending
e) Open the header table.
f) Step through the array and insert the element position number in each corresponding record in the header table (would require a new field of course, to hold the record's current rank position)
g) Sort the header table on the rank field and present the records to the user for further processing.

All in all, I think I'd rather keep a bit of duplicative data in the header table.

-- tom

-- tom

[Tom Cone Jr]

RE: Order/Filter based on Child Table

Darn, this date stuff IS slippery.

If you follow my recommendation there's no need to sort the last transaction date field descending order. Ascending will show oldest (earliest) first.

-- tom

[Art Cordaro]

RE: Order/Filter based on Child Table

Tom,

Will give it a try. Thanks again
Art

[Art Cordaro]

RE: Order/Filter based on Child Table

got it