Filtering Records

[Eric Frierson]

Hello, All

I have a report that is do yesterday. My only hold is, that I need a special filter. This filter must limit my record selection in a set based on the number of child records. e.g. my boss only wants to see invoices that have 2 or more lien-items. How can I filter these record when the count of line-items has not actulay taken place yet?

[JerryBrightbill]

RE: Filtering Records

Eric

Someone always wants the impossible yesterday. One method might be to add a numeric field to the parent which contains the number of line items (child records). To keep this up to date, you will need a numeric field in the child also. The child field would be calculated with a value of 1. Whenever a child is saved, use a posting rule to take the value in the child field (1) to add to the value in the parent lineitems field. Once you have a value in the parent that shows line items, building a filter on that field is easy.

Of course, the question is how to populate this field for existing records. Attached is one method using an xbasic script. It counts each child record and updates the line items value in the parent.

Jerry

[Tom Cone Jr]

RE: Filtering Records

Eric,

Building on what Jerry outlines for you, a similar approach could be used to populate a temporary table (or set of tables if that's easier for you to conceptualize), containing only the invoices that have two or more line items. You could then base your report on the temporary table.

The sequence would go like this:

1) Setup: You'd define the table(s) and add them to your database; leaving the empty.

2) The temp tables would be populated by a script which steps through your real tables, adding all qualified records to the temp table(s).

3) The report would be run

4) The temp tables would be emptied.


This approach would not involve changing the structure of your existing tables, but would run more slowly, since the records being printed have to be found and then entered into the temp tables each time.

-- tom

[JohnZaleski]

RE: Filtering Records

Along the same basic idea as Tom, create a summary operation that summarizes the line items by the invoice number and also do a count by invoice number.Then add a link to your set with a one to one from the invoice record to the summary record. Filter on something like:
scanning("linecount") .and. linecount->count_invoice > 1

[Eric Frierson]

RE: Filtering Records

Sorry for being rude. Thanks that worked. How did you get so good at this scripting thing?