selecting parent based on child??

[jon2004]

i have a document that has several date related child documents, using a doc id as a key,

the parent document is created say on the 10th, child documents are created on the 11th and 12th, i would like to make a report of the parent document, and all documents on a given date, say the 12th..., but not all the child documents.

how do i do this, its driving me nuts!

[Jim McKee]

hi jon ...

You have a relationship between docID in the Parent file and docID in the Child file (call it: "docID_Child_docID").

The trickiest part of this is a smooth way to pass the desired date to the Child file.

1. Create Number type calculation fields (fieldname: "constant") in both files, with simply the value 1 entered into the calculation definition. Name both fields "constant"

2. Create a relationship in the Child file between the these two fields. Call it: "constant_Parent_constant". This is a frequently used method in FileMaker of passing globals between files.

3. Create a global date field in the Parent file ("g_date"). The user will enter the desired date into this global. How this is done depends on the version of FileMaker you're using. If version 6, a Custom Dialog could be used for user input into the "g_date" field. This would be incorporated into the main script in step #5. Otherwise, you'll need to put the g_date on a layout somewhere in the Parent file.

4. Create a subscript in the Child file (let's call it: "sub_getrecords") along the lines of:

Enter Find Mode[] -- (be sure to uncheck the Pause and Restore Find Requests options)

Set Field [date, constant_Parent_constant::g_date]

Perform Find [Constrain Found Set]

If [not Status(CurrentRecordCount)]

Show Message ["No records for this client were found for the specified date.", Buttons: "OK"]

Exit Script

Else

Go to layout [a List type layout]

Halt Script

End If

5. In the Parent file, use a button to call a script (call it "Get Records") something like:

If [not IsValid(docID_Child_docID::constant)]

Show Message ["There are no Child records for this docID", Buttons, "OK"]

Exit Script

Else

Go To Related Record [docID_docID, Show only related]

Perform Script [External, Child.fp5, "sub_getrecords"]

End If

What happens is:

1. User enters desired date into the global field g_date in the Parents file

2. User activates the script in Parents that assembles a found set in the Child file consisting of only the related child records. If none exist, the user is alerted and the script is exited.

3. If related child records exist, the subscript in the Child file performs a find using the g_date value in the Parent file. Note the: "Constrain Found Set" option in the Perform Find script step.

4. If no records are found for the specified date, the user is alerted, the script exits, and the user is returned to the original layout in the Parents file.

5. If records are found for the specified date, the user is taken to a list layout in the Child file, and the script halts.

Hope this helps.