finding records w/empty portals

[ggh]

I need to omit records that have no records showing in a portal. So far I've been able to isolate records with specific data in the portal, but can seem to do a find for "=" to isolate empty portals. Is there a way to do this?

[ggh]

Found still another part of the problem. I had "do not evaluate if all referenced fields are empty" checked in the calculation - it didn't not consistently find the empty records. Unchecking this seems to solve it.

[Vaughan]

Make an unstored calc field in the parent record

Count( relationship::primary key )

This will display the number of related records each parent record has. Search for Count = 0 to find those records with no related records.

Note that if you only want to find records with no related records you could have used the calc

IsEmpty( relationship::primary key )

... which could even be faster. However I find the Count() field more useful, since it can be used to find records that have, say, only 1 related record.

[fabriceN]

I'm afraid this means searching an unstored calculation, which is extremely slow.

I would rather exclude records that have related records (type "*" in a related field and check the omit check box in the status area)

HTH,

Fabrice

Edited August 31, 2008 by Guest

[Søren Dyhr]

What about GTRR(FS) twice ....back and forth over the relation, where the last one is Show Omitted Only'ied?

Go to Related Record [ From table: “Portalized”; Using layout: “Child” (Portalized) ] [ Show only related records; Match found set ] 

Go to Related Record [ From table: “Main”; Using layout: “Parent” (Main) ] [ Show only related records; Match found set ] 

Show Omitted Only 

--sd

[fabriceN]

I think GTRR matching foundset would be really slow.

And I don't use it anymore becore error trapping is so buggy.

[Søren Dyhr]

I have thought about that as well, this is why I think that this must solve the error trapping issue:

Go to Layout [ “Portalized” (Portalized) ] 

Show All Records 

If [ Get ( FoundCount ) ] 

       Go to Related Record [ From table: “Main”; Using layout: “Main” (Main) ] [ Show only related records; Match found set ] 

       Show Omitted Only 

End If 

--sd

[fabriceN]

Nice, but... why ? ;-)

Isn't this much simpler ? You don't even need a script do do that (not to mention a layout, a table occurrence, a relationship)

PortalFind.fp7.zip

[fabriceN]

But as we are talking about portal finds, I have to point out that FileMaker 9 introduced what I think is one of the most important bugs (they say it's a fix! no kidding)

This has to do with multi-predicates joints and empty values.

This file works perfectly with FileMaker 8, not with FileMaker 9 (remember that finds are performed on the host). By not working, I mean that you are not able to find related records that you can see in browse mode.

So be careful with old style booleans (1/0 or empty), they must really be booleans (1/0).

PortalFindOn9.fp7.zip

[comment]

you are not able to find related records that you can see in browse mode.

By what criteria?

[fabriceN]

Sorry, I don't understand.

Any criteria would fail (e.g. *). Some records (with an empty key) are just not findable.

[comment]

I still don't understand what am I supposed to look for, and from which context. So far, everything I have tried returns the same results in all versions. Could you provide specific search criteria, or perhaps attach a script to your file?

[fabriceN]

Here you are

... errr.. no, it now fails on 8.5 too... sorry, I'm lost here. I have made consistent tests earlier, and it was working with 8.5 and not with 9.

Was there a change in the latest upgrade of 8.5 ?

PortalFindOn9.fp7.zip

Edited September 3, 2008 by Guest

[comment]

I get the same results with version 7 as I get with 9.

[fabriceN]

Sorry then. The test case is not correct.

I'll try to find back the sample file I have that really illustrates the change between 8 and 9 (because I'm sure there is one !)

Fabrice