Alpha Video Training
Results 1 to 6 of 6

Thread: Include Parent in report if Child filter satisfied

  1. #1
    Member
    Real Name
    Jeffrey Woloshin
    Join Date
    Feb 2007
    Location
    Tucson, AZ
    Posts
    16

    Default Include Parent in report if Child filter satisfied

    My application records service calls on customers' office equipment. I need to create an "excess service calls" report that will include only records that satisfy a child-level filter.

    Parent db is 'Machines' and Child db is 'Calls'. The user would be prompted for two filter parameters, "X" and "Y". The report includes only those machines where there were at least "X" number of service calls done over the last "Y" days -- i.e. at least 3 calls in the last 30 days.

    The group breaks on a change in Machine_id and the report detail includes information about each call, but only machines that satisfy this "3 in 30" filter are included in the report. And the detail includes only those calls that happened within the "Y" parameter, i.e. within the last 30 days.

    Can anyone help me create this filtering scenario please?

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Include Parent in report if Child filter satisfied

    Assuming the report layout is presently based on a set using a one-to-many link I suggest you create a new set for the same tables, but reverse (invert) the relationships. Make the child table parent, and link to the parent table (as child) using a 1:1 linkage. Then base your report layout on the new set, filtering the "new" parent table as required.

  3. #3
    Member
    Real Name
    Jeffrey Woloshin
    Join Date
    Feb 2007
    Location
    Tucson, AZ
    Posts
    16

    Default Re: Include Parent in report if Child filter satisfied

    Quote Originally Posted by Tom Cone Jr View Post
    Assuming the report layout is presently based on a set using a one-to-many link I suggest you create a new set for the same tables, but reverse (invert) the relationships. Make the child table parent, and link to the parent table (as child) using a 1:1 linkage. Then base your report layout on the new set, filtering the "new" parent table as required.
    Thanks, Tom
    That makes a lot of sense, but I'm not sure how to create this "3 in 30" filter. The date part is easy, but including only those records for each machine_id if there are at least 3 such records in that time span? That part I'm lost on...

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Include Parent in report if Child filter satisfied

    Jeffrey,

    Consider using a filtered tablecount() equal to or greater than the specified minimum.

  5. #5
    Member
    Real Name
    Jeffrey Woloshin
    Join Date
    Feb 2007
    Location
    Tucson, AZ
    Posts
    16

    Default Re: Include Parent in report if Child filter satisfied

    Quote Originally Posted by Tom Cone Jr View Post
    Jeffrey,

    Consider using a filtered tablecount() equal to or greater than the specified minimum.
    Tom,
    I'm not a beginner at this, but I may be a bit dense. I can't visualize how to make tablecount() work, but I guess we'd have to loop through the entire Machine table, testing each machine to see if there are more than X calls in Y date range in the Calls table? Or somehow have the function return a list of each machine_id in the Calls table that satisfies the criteria? Any help writing functions that do this is greatly appreciated...

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Include Parent in report if Child filter satisfied

    Oh. mea culpa.

    I see what you mean. You could use the tablecount() function to either run or not run a report for a single machine.... but not to determine whether a particular machine should be included in a report covering all machines. Sorry.

    Give us some sample data to work with. I'm thinking of a script that loops through the machines and "marks" those which have children meeting the filter criteria. Your report, based on the inverted set I mentioned above, would then be filtered to include only the marked machine records.

    ----------------
    Later on
    ----------------

    Since the tablecount() function must open a new instance of the table each time it's called it's relatively slow. If your table is large you may want to simply open the table once, query it for the pertinent date range, then step through the filtered table (sorted by machine id) marking machine records (in the other table) as you go. Probably would be faster.
    Last edited by Tom Cone Jr; 02-20-2007 at 04:40 PM. Reason: New idea

Similar Threads

  1. Help With a daily report to include all 3 shifts
    By Timrx in forum Alpha Five Version 7
    Replies: 3
    Last Post: 03-02-2007, 11:20 PM
  2. Problem:Include record from parent table
    By Dannie Mogensen in forum Alpha Five Version 6
    Replies: 0
    Last Post: 01-19-2005, 04:42 AM
  3. Report filter on Child Table (Not in V 5)
    By Mike Becker in forum Alpha Five Version 5
    Replies: 7
    Last Post: 09-14-2002, 02:28 PM
  4. Update child recs bases on parent filter
    By Michael Humby in forum Code Archive
    Replies: 1
    Last Post: 05-05-2002, 02:33 AM
  5. Filter Parent With Child
    By Jamin Dunivan in forum Alpha Five Version 4
    Replies: 1
    Last Post: 05-31-2000, 02:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •