Find all parent records with no related records that comply with a condition

[bluearrow]

Hi,

Finding all related records within a date interval is easy, assuming you are searching inside a single date field in the related table. You just specify StartDate...EndDate.

I need something more difficult: I have 2 related tables: 2 date fields in the child database (StartDate and EndDate). A one to many relationship. Every child record has a date interval.

I need to find all parent records that, given a search date (single date )or interval (StartDate...EndDate), have no child records in which that date or interval is present. And I mean parent records: the search result must show parent records.

All searches are scripted and carried out from the parent table. The user might select other search parameter that are not relevant to this issue but might be part of the search conditions. There is no way this can be changed (I cannot script just to search for this interval, for instance, going to the child table, searching into the dates fields and then returning to the parent table). It must be a direct search.

An example:

Child records for Parent record 1

Record 1: StartDate 03/March/2008 EndDate: 15/March/2008

Record 2: StartDate: 07/October/2008 EndDate: 09/October/2008

Child records for Parent record 2

Record 1: StartDate: 05/April/2008 EndDate: 16/April/2008

Record 2: StartDate: 07/October/2008 EndDate: 12/October/2008

Date searched: 08/October/2008

Expected search result: No records founds

Date searched: 09/March/2008

Expected search result: Parent record 2

Please let me know if I have not explained clearly what I need.

Thanks

[Søren Dyhr]

Go to the related records table and make the intervals seach, and then make a GTRR(FS) ...and finally a show omitted only - according to the headers description.

But why is this a requirement?

All searches are scripted and carried out from the parent table

seems a strange requirement to me at least? It can however be done by facilitate your graph with the above shown structure, and then make a GTRR(SO) within the red in-circulated, to prevent leaving the table at any point

--sd

[Vaughan]

I need to find all parent records that, given a search date (single date )or interval (StartDate...EndDate), have no child records in which that date or interval is present. And I mean parent records: the search result must show parent records.

I'd start off by solving the problem for a single search date first. Get that working then move on to date ranges.

The single search date needs to entered into the related start and end date fields along with an operator:

start date: >=date

end date: <=date

Since the find process is scripted there will be no problems building this into the process.

Doing a range is too complex for me to think about at the moment. Partly because I'm not sure exactly what searching a date range with a date range means.

[bluearrow]

Go to the related records table and make the intervals seach, and then make a GTRR(FS) ...and finally a show omitted only - according to the headers description.

But why is this a requirement?

Hi Søren. Thanks for your reply. Sadly, I cannot user what you propose. Showing omitted will display omitted child records that comply with the date criteria, not parent records. This could be solved and show the related parent records, but: what about other search criteria?

To give you a better picture:

- The user is looking for Contacts.

- The search is started from the Contacts table.

- The date range is a holiday period

- A contact might have several different holiday periods a year (or over the years). Obsolete periods are not deleted.

- the user might request contacts whose name start with letter S, or contacts from a specific client, or contacts whose name start with letter S, from a specific client, and which are not currently taking holidays (this are just examples, the real search screen includes many more search fields).

What I want to clarify with this is that the holiday search is not an isolated search (and cannot be), but part of a much more complex search. There are many related tables that could be queried.

It is simply not possible to go to the related records and look for a date or a date range because there might be other criteria in the search and this criteria is not part (neither is present) in the Holiday table. It is the Contacts table the one that holds all relationship, not the Holiday table.

Does it give you a better view of the problem?

Edited September 24, 2008 by Guest

[bluearrow]

...I'm not sure exactly what searching a date range with a date range means.

Thank you Vaughan. That is indeed the second part of the problem: how to search for a date range using a date range (really, looking for records that do not match the searched date range). I do not have any idea how to do this.

But the first part is how to include the date search into a more complex search, as described in my reply to Søren.

Edited September 24, 2008 by Guest

[Søren Dyhr]

It is simply not possible to go to the related records and look for a date or a date range because there might be other criteria in the search and this criteria is not part (neither is present) in the Holiday table

I think it is - it escapes me why it can't? First find the child records matching the date intervals, and then go backwards again over the relational connection with a GTRR(Foundset) ...

Then is it time to constrain the found set via the other criteria your search might have.

Read up upon:

http://www.filemaker.com/help/Script-Steps19.html

and

http://www.filemaker.com/help/Script-Steps63.html

It is the Contacts table the one that holds all relationship, not the Holiday table.

How is this possible the relation goes in both directions even in Anchor Bouy, only an unstored calc as primary key removes this option.

Alright I played along with your approach not getting into the child records at all! Look at attachment!

--sd

Edited September 24, 2008 by Guest

[comment]

I agree with Søren's first suggestion. First, find the child records that overlap the given range. Then use GTRR (match found set) to find their parents. Then show omitted records in the parent table.

The same could be achieved by searching directly from the parent layout in related child fields.

The overlapping records can be found by defining the criteria as:

StartDate: ≤ RangeEnd

EndDate: ≥ RangeStart

Note this is a single request (i.e. an AND search).

[bluearrow]

Look at attachment!

For some reason, I am getting an error while trying to download your attachments. It seems a site error, though: "You have requested a resource that does not exist. The above information has been logged."

Anyway, are you talking about a scripted constrain? Because the users do not have access to any of FileMaker menus. Just a custom search dialog with search fields an a Find button. I can see it is possible to use the GTRR, but it seems to me very complex. I will have to include in the search script a conditional line in case the user request a holiday restriction. After that, go to the child table, then GTRR to return to the parent records, then omit, then constrain the search with the rest of the search request. I was thinking in something much, much simpler like treating this date range search like the rest of search fields, if possible at all (I am lazy and I like simple coding, I recognize it). Maybe your file contains that kind of solution.

Initially, I thought of creating an "inverse" relationship between the parent and the child table based on the date fields (in the child table) and a new field in the parent table, but it did not work. Then I thought of storing all child ranges in a field in the parent table (not a good idea, I know) as carriage return separated numeric values, to avoid searching in the child table. Not sure how to best accomplish it, anyway...

Edited September 24, 2008 by Guest

[Søren Dyhr]

I had a template attempted uploaded to this thread, but some sort of hiccup prevented it from happening - meanwhile have I by Dolenski been asked to re-upload the files I were in charge of ... however have I missed the uploaded image in the first of my comments, but the second one is intact - enjoy!

--sd

Vacations.zip