Building a list of related records via a portal based on a calc

[James Gill]

Say I have a database with 3 tables; A, B, and C.  Table A is the parent table to table B, and table B is the parent child to table C.

 

So, A -> B -> C

 

If I wanted to evaluate from Table A whether a child record from Table B has a sub-child record in Table C and then capture the primary key value from Table B via a portal, how would I do this?  On the surface it seems fairly trivial to write a calculation that that says ‘Case ( not isValid ( a>b>c::id ) ; a>b::id )’.  The obvious problem is that calculation will only evaluate the first related record from Table B that is displayed in the portal.  Encapsulating the entire Case statement inside of a List function results in the calculation returning a ‘?’ .

 

I can think of several ways to get around this problem (Write an ExecuteSQL function, create a calc’d field in Table B, etc) but I feel like I’m missing something trivial here.

[comment]

If I wanted to evaluate from Table A whether a child record from Table B has a sub-child record in Table C and then capture the primary key value from Table B via a portal, how would I do this?

 

I am not sure what do you mean by "via a portal". A portal is a layout object - your question seems to be concerned with the data layer.

 

 

From the context of A, you can tell that some record in B has a related record in C by testing for =

not IsEmpty ( C::ID )

To capture the primary keys of all such B records, you can do:

List ( C:_ID )

--

P.S. Please don't use abstract names like A, B and C. They are very difficult to follow. If you want to keep this generic, use the names Parent, Child and Grandchild..

[James Gill]

I am not sure what do you mean by "via a portal". A portal is a layout object - your question seems to be concerned with the data layer.

 

 

From the context of A, you can tell that some record in B has a related record in C by testing for =

not IsEmpty ( C::ID )

To capture the primary keys of all such B records, you can do:

List ( C:_ID )

 

In my case I need exactly opposite of this;  I need B::ID when C: _ID does not exist as evaluated from records in A.  Or in "non-abstract" terms, I need the primary key of all the child records when a grandchild record does not exist (as evaluated from the Parent record that is being displayed in a portal).

 

I can think of about 5 different ways to get this value that would work but in my mind they are workarounds to what, in my mind, should be able to be calculated through the portal that is being used to display the Parent records.

 

I made a demo file and attached it so hopefully it clarifies what I'm trying to do.

Test.fmp12.zip

[LaRetta]

Hi James,

Your child keys in the grandchild table are incorrect.  You can tell this by placing a grandchild portal and a few fields on your child layout.   None of the child records show any grandchildren.  To fix the problem, check 'allow creation' in your graph (temporarily) between child and grandchild on the grandchild side and create some test records.

 

Now you can go to your parent layout's child portal and filter the portal via:

IsEmpty ( List ( GrandChild::id ) )

As you realize, there are a few other ways of approaching the issue, such as calculation with list of ids using 'not equal'.  

 

The reason it is good to know the true table names is it gives us some perspective as to the purpose and potential size of the records involved.  If we are talking about grandchild records for each child being more than a few thousand, and if we are talking about using on iPad, the answer might be different than using a portal filter.

[LaRetta]

Here is a modified file.  Go to layout called Parent Copy.

TestMOD.fmp12.zip

[comment]

I need the primary key of all the child records when a grandchild record does not exist 

 

I am afraid I cannot open .fmp12 files, but my suggestion would be to define a calculation field in the Child Table =

Case  ( IsEmpty ( Grandchild::ChildID ) ; ChildID )

and then, from the Parent table, use the List () function on this field.

 

 

This is assuming you want a "live' unstored calculation - which will get slower as the number of records increases. For a scripted solution, you could do:

Go to Related Record [ Show only related records; From table: “Child” ]
Enter Find Mode [  ]
Set Field [ Grandchild::ChildID; "*" ]
Omit Record
Constrain Found Set [  ]

Now you have a found set of the child records you were looking for, and you can either loop among them or use a summary field to collect their IDs. There's probably an alternative, using the ExecuteSQL() function straight from the parent record.

 

 

I still don't see what a portal has to do with this; this method should work without depending on the presence of a portal.

[LaRetta]

Oh!  I thought the desire was to display them in the portal.

[comment]

Oh!  I thought the desire was to display them in the portal.

 

Perhaps, but this is a strange way to express such desire:

 

 

I need the primary key of all the child records when a grandchild record does not exist (as evaluated from the Parent record that is being displayed in a portal).

[LaRetta]

I was confused as well until I saw the file.  However, I still may have missed the point. :-)

[comment]

In any case, to show them (child records with no grandchild) in a portal is easy once you have a list of their IDs.

 

 

Another option is to filter the existing portal to Child by =

IsEmpty ( Grandchild::ChildID )

This requires no calculation fields or scripts - but may exhibit poor performance with a large number of records. It's hard to steer towards the optimal solution when working with a generic example.

[James Gill]

 I appreciate all of the replies, I'll try to address this as best as I'm able.

 

Here is a modified file.  Go to layout called Parent Copy.

 

While this is definitely the easiest and most straightforward way to solve the issue in Filemaker, I'm trying to avoid having to add more un-stored calcs to the solution.  I realize it's a perfectly viable alternative, it's just me being leery of adding an un-stored field for such a mundane, one-off need.

 

This is assuming you want a "live' unstored calculation - which will get slower as the number of records increases. For a scripted solution, you could do:

Go to Related Record [ Show only related records; From table: “Child” ]
Enter Find Mode [  ]
Set Field [ Grandchild::ChildID; "*" ]
Omit Record
Constrain Found Set [  ]

Now you have a found set of the child records you were looking for, and you can either loop among them or use a summary field to collect their IDs. There's probably an alternative, using the ExecuteSQL() function straight from the parent record.

 

 

I still don't see what a portal has to do with this; this method should work without depending on the presence of a portal.

 

That's definitely one way to solve the problem at hand, but my one question would be how the overhead of GTTR and performing a find (albeit on an indexed field) compares to an ExecuteSQL method.

 

In any case, to show them (child records with no grandchild) in a portal is easy once you have a list of their IDs.

 

 

Another option is to filter the existing portal to Child by =

IsEmpty ( Grandchild::ChildID )

This requires no calculation fields or scripts - but may exhibit poor performance with a large number of records. It's hard to steer towards the optimal solution when working with a generic example.

 

This is actually the method I settled on.  Because the users need to see all contents of the portal regardless of whether they have grandchildren I had to set the filter based on a scripted set of conditions once the user clicks a button.  It seems to work fine and the performance shouldn't be an issue since none of the records will have more than 20 or so children and this is on a LAN connection.

[comment]

  my one question would be how the overhead of GTTR and performing a find (albeit on an indexed field) compares to an ExecuteSQL method.

 

I don't know. You would have to perform a test. I suspect you will need to have a very large amount of records before seeing a significant difference - but that's just a guess.

[James Gill]

Welp, I hate to dredge this topic up again but it seems as if I'm experiencing some unexpected results using the Filtered Portal method.  I have a cartesian join on a portal that is displaying a list of employers.  I'm filtering that list using an Employer Name filter which works on the portal just fine.  However, when I set a variable List ( EmployerPortal::id ) I get the entire sub-set of related records ~(649) instead of the what the Filtered Portal is showing (2 records).

 

Any thoughts?

[comment]

Is this related to your earlier question? If it is, I fail to see the connection.

In general, portal filtering works at the layout level. A calculation such as List ( related::field ) works at the data level; it does not use any portal filtering you might have. In fact, it will work the same even if you don't have any portal at all. Or if you have 20 portals, each filtered in a different way.