Finding: Related Records & List View

[javabandit]

I'm stuck due to my inexperience in relational databases.

I have a 2 layouts that display data from two related tables. Parent and Child are joined with the ParentID, and this works fine. In the first layout, the Parent fields are on the layout, and the Child records are shown in a portal. No problem.

The second layout is the trouble. It is a list view that displays some Parent fields, and some Child fields. This layout is from the perspective of the Parent, and I have the relationship set up so that it displays data from newest child record. This is just what I want in this view, until I try to use the Find command.

Let's say that I want to generate a list where the child field "Date Done" is empty. I enter Find mode, enter a * into the field, and choose Omit. I then perform the find. It generates a found set, but it's not what I expect to see.

Some records have an older child record with a "Date Done" filled in, even though in the newest child "Date Done" is empty. These records are not in my found set, but I want them to be.

I'm sure this is just my inexperience, but I'm stuck on where to go from here. Any help is appreciated.

[javabandit]

I see there have been 42 views of my post, but no replies. Maybe my post was too confusing. I'll try again:

I have two tables: Parent table & Child table, joined by a ParentID. Each parent record can have many child records. In the join relationship, I am sorting the child table by ChildID Decending.

So, in my layout, I display the Parent fields, and the child fields. Because of the sorted relationship, the child data that displays is from the newest child record. So far, so good.

Now my problem: When I do a Find on any of the Child fields, the find seems to look at -all- child records, not just the newest ones. So, the results don't come out the way I expect.

I would greatly appreciate any help in understanding this situation.

Edited March 26, 2009 by Guest

[bcooney]

What results are you expecting?

[comment]

I agree with Barbara: the description of the intended result is not quite clear. I am guessing you should enter a = sign into the searched field - this will find all parent records where at least one of the child records has an empty field.

[javabandit]

Yes... good question: What results do I expect?

Say I already know that Parent record 1 has two child records (child1 & child2). And, I know that child1 has a "date done" filled in, but child2 does not. I'm expecting my Find to return Parent 1, with the child data from child2.

I think the problem is with my find method:

I would enter find mode, enter a * in the "date done" field, choose "Omit" and Perform the find. When I did that, it would not show any parent records like the one described above, it got omitted.

Just now, I tried comment's suggestion. I entered find mode, entered an = in the "date done" field, then Perform Find. When I did that, I -did- get records like the example above!

So, I'm not sure I understand the difference. In one case, I omit anything that is filled in... in the other case, I show anything that is not filled in. But the results are different, why?

Thank you for your time helping a novice...

[comment]

The difference is this:

You search for records where at least one of the children has a value, then omit them. The result is parents with only empty children.

I search for records where at least one of the children is empty.

[javabandit]

Okay, I'm starting to wrap my mind around this.

Whenever searching on child fields, I should think in terms of finding "at least one".

Thank you for your time.

[comment]

Whenever searching on child fields, I should think in terms of finding "at least one".

Something like that. The underlying concept is that the children are an "unordered set", with no internal precedence. Say you have a parent with 3 children named "Alpha", "Bravo" and "Charlie" respectively. That parent is going to be found whether you search for "A", "B" or "C" in the Child::Name field.

[javabandit]

Thank you for your help, I continue to learn more every time I visit this forum.

[ndmcconn]

Sorry to enter a late thread, but is there another way to find date fields that are filled without

finding all the empty fields ( with =), then using an omit command?

Thanks,

ndmc

[bcooney]

Enter "*" no quotes in your date field and find. Not sure when this was introduced, and so cannot guarantee this is FM7 compatible.

Edited April 6, 2009 by Guest

[ndmcconn]

Hi!

Thanks so much, but for date fields, the find command gives me the revert window, meaning "enter a date".... So, my assumption is that fmp 10 has addressed this one? Looks like it's time to buy that Intel Macbook Pro, and say goodbye to Power PC, and upgrade.

ndmc

[comment]

1. I cannot see anything wrong with searching for = with an omit request.

2. If you insist, you can search for a range from January 1, 0001 to December 31, 4000.

3. Version 10 runs on PowerPC processors just fine.

[Vaughan]

Thanks so much, but for date fields, the find command gives me the revert window, meaning "enter a date".... So, my assumption is that fmp 10 has addressed this one?

There is nothing for FMP 10 to address.

The date field has been set up with field-level validation that requires a valid date be entered. The "revert" dialog is a a validation warning because "*" is not a valid date.

[comment]

There is nothing for FMP 10 to address, because it has been addressed in version 8 (I think). But in version 7 you'll get an error message if you enter * as the search criteria into a date field.

[Vaughan]

Indeed Comment, you are correct. I just tested "*" in FMP 9.0v3 Mac and there is no validation error. I don't have any earlier versions to test further with.