Self Join Portal

[emncwundy]

Hi there I'm trying to set up a self join portal to show active orders only on an iPad (those classified as either overdue or ordered). I created a global value of 1 for the parent side g.ActiveStatus is set to = 1 and then on the child side I matched to a calculation field c.OrderStatus where records with "ordered" = 1 or "overdue" = 1. Calculation result is a number (global also is a number). However the related records will not show in the portal. To check my thought process, I removed the calculation on the child side and manually entered 1's, and the portal works. But with the calculation it does not. I tried to activate indexing, thinking maybe this has something to do with it but get an error message about the field not being index-able because it references a related field, a summary field, an unstored calculation field or a field with a global storage. So now I'm feeling stuck. Does anyone have any suggestions?

[rwoods]

Hello

Relationships need indexable (or global) fields on both sides of the relationship, so the problem will be that c.OrderStatus is not indexed.

And you can't index it, because it is a non-stored calculation, probably because it references fields from a relationship itself.

You need to make c.OrderStatus a stored field. Do this by either :-

1) Making it an Auto-Enter calculation (rather than a calculated field) as this can be indexed. FileMaker will not update this Auto-Enter calc automatically whenever the related fields it references change their value, so you will need some sort of trigger in there to force it to update.

2) Maintain the value of c.OrderStatus in your scripts (or layout/field Script Triggers), don't have it be calculated at all.

It is a pain that you can't make relationships be based on unstored calculations, but I suppose it is for performance reasons. Relationships need to work fast, and unstored calcs can be very slow.

Edited May 7, 2020 by rwoods

[emncwundy]

Thanks so much for your advice @rwoods. I get the first suggestion. But I’m a bit confused by the second. What do you mean by ‘maintain the value’? Do you mean manually maintain?? Cheers Ellen 

[comment]

10 hours ago, emncwundy said:

and then on the child side I matched to a calculation field c.OrderStatus where records with "ordered" = 1 or "overdue" = 1.

Can you explain exactly what this calculation does? Often a solution can be found by moving the "unstored" part to the other side of the relationship. For example, instead of calculating if an order's date is after the current date (which must be unstored), make the relationship match orders whose date is after the current date, with the current date being calculated on the requesting side of the relationship:

SomeTO::cToday > Orders::OrderDate

Such relationship will allow you to show orders whose date has passed in a portal placed on a layout of SomeTO.

 

1 hour ago, rwoods said:

Relationships need indexable (or global) fields on both sides of the relationship

Not quite. A relationship needs an indexable match field on the "right" side of the relationship - that is the side that retrieves the records. Such field cannot be global. On the "left" side (the side that requests the records) the match field can be a global field or an unstored calculation field. 

As a rule, Filemaker's relationships are bi-directional, but if one of the match fields is unindexable, the relationship will work only in one direction.

 

[emncwundy]

Thanks again for your help and suggestions. I have solved with the use of an auto enter calculation (as suggested),  which is triggered by a script when moving from a form to list view of all active orders.