Unstored field as basis for portal?

[K1200]

I have a situation with a parent and child records where all child records of a certain type are displayed in a portal, based on a user-specified code (text). Previously, the relationship was defined as _gViewCode = Child::Code

But some child records do not have an embedded code, so I need to apply a Parent::Code as a temporary default. Since I want to leave the Child::Code blank for subsequent entry by the user, I set up a separate unstored calculated text field upon which to base the portal display:

Child::NType = Case ( IsEmpty (Type) ; Parent::Type ; Type )

However, once I change the relationship to _gViewCode = Child::NType, no records display in the portal. It's as if the NType field hasn't been calculated at the time the portal contents are fetched.

But if I set up a tabular view of the child records using that same TO, the appropriate records are displayed. And if I change the relationship so that all records display in the portal, the NType field shows up fine in my original portal.

I've tried every combination I can think of to get the portal to recognize the requested records. There are posts that confirm that an unstored, calculated field can be used in this manner -- so I must be missing something.

Any help will be appreciated.

[Fenton]

There are posts that confirm that an unstored, calculated field can be used in this manner

There are posts that describe how you can use an unstored calculation field to produce those results, but never using the unstored calculation as the target of a relationship. That will not work.

There is a method we call "Ugo's method" which would do what you want. But, before going into that, which is a fair amount of trouble (and which you could also search for and find), we have to ask. Why not just lookup the Parent value as a default value at creation time? Or auto-enter a default generic text value?

Corollary question: Are people adding/changing the Parent type after creating children?

Another method, I'd call "Søren's method" would allow you to show all the child records (for a parent?) if the _gViewCode was blank. So someone could "back up" if none showed for any particular type they'd entered. This would involve creating an unstored calculation for the Parent side of the relationship.

In other words, what's the total situation here?

Edited March 12, 2008 by Guest

[K1200]

Since I want to leave the Child::Code blank for subsequent entry by the user

No, the parent type never changes -- it just serves as a default. What I want to do for a requested type is bring up (in the portal) all that are already assigned to that type plus all that are not yet formally assigned (and hence have the parent type as the default).

I must have misinterpreted those prior posts. They pertained to calculated date fields, but I couldn't spot them in a quick search.

Anyway, thanks for setting me straight on using (i.e., not using) unstored calculated fields in relationships.

[Fenton]

(i.e., not using) unstored calculated fields in relationships.

Unstored calculations can certainly be used as the originating field of a relationship, but not as the target field of a relationship.

Then use a calculation in the parent table, _cgViewCode =

Case ( not IsEmpty; _gViewCode; _gViewCode; Parent::Code ]

Use this field as the originating side of the relationship. This will allow you to see either _gViewCode or those matching the Parent Code (unchanged). I think this would be best. I don't see why you'd want to see _gViewCode AND Parent::Code at once. To flip the view, just clear the _gViewCode field.

Edited March 12, 2008 by Guest

[K1200]

Fenton, I've tried to understand what you're suggesting, but I just can't see how it will accomplish what I need. Here's an example of typical records -- and of the result I need:

Record#, Parent::Type, Child::Type

1, B, _

2, A, _

3, A, A

4, A, B

5, B, A

6, B, B

With ViewCode = B, the portal then needs to display:

Record 1

Record 4

Record 6

They're all the ones that either have B as the Child::Type or -- if and only if Child::Type is blank -- have B as the Parent::Type. (The Parent::Type is never blank.)

For a moment I had resolved that I would simply store my calculated NType and be on my way. But FileMaker won't store a calculation containing a related field, so I'm back to square one.

I appreciate your suggestions and hope you might be able to clarify this for me with a word explanation of the logic behind the calculation of the new global field. In other words, how can it result in records 1, 4 and 6?

[mr_vodka]

How about something like this?

sample.zip

[K1200]

I'm working on understanding what you've suggested. The complicating factor is that where you have parent_info as a TO (of "data"), I have a Parent Table in between "interface" and "data" -- and the ParentType field only exists in the Parent Table, not in "data".

Can you describe the approach in words? It seems to use a calculated field to flag those child records with empty type fields -- and then somehow matches those against a calculated list of Parent IDs and Child IDs. But concept is just a little beyond of my grasp. Am I close?

[mr_vodka]

Its almost the same concept except that along with getting all the IDs in the child table that matches your criteria, you want to add the IDs of the children records that are related to the parent record with the same selected value but that which the child type is empty.

sample2.zip

[K1200]

I almost have this. But one more question: In some TOs, you prefixed the Parent and Child IDs. What do the "pk" and fk" prefixes stand for? I'm just trying to make every aspect fit together.

Thanks.

[mr_vodka]

Primary Key, Foreign Key

[K1200]

Since it's taken me several attempts to figure out the details of this method, I'm providing this explanation for others who might need to use it.

For those accustomed to using what I would call Calculated Lists, this might be a common and easily understood technique. For me, it was not -- so here's my summary:

child::cEmpty is a calculation used to flag any child record that doesn't already have a Type

child_direct is the set of all child records that have a defined Type that matches the selected Type (cSelect)

parent::cEmptyChild is a calculated (at runtime) list of all of the flagged child records for an individual parent record

interface::cKey is the calculated (at runtime) composite of flagged and (already) matching child records

child_show is then the set of all records that match the IDs in cKey

Regarding the fact that most fields are set to "Indexed", I have not determined if the setting is essential for this type of list-based technique -- or just recommended. Nor can I say why two of the calculated fields are unstored, but not child::cEmpty. But the technique works in my application, as is.

Thanks again for the help.

[mr_vodka]

As Fenton had mentioned to you earlier, an unstored calc can not be used on the child side of a relationship. However, the parent side CAN have an unstored calc as part of the relationship key. So you would have to figure out how to give you the same results by looking at what the parent side calc should do to do it.

The child::cEmpty calc is stored because it references a stored field within the same table and does not reference a related field. Anytime a related field is referenced in a calc, it will try and make it unstored.

Hope this clears some stuff up.

[K1200]

Thanks for trying. Unfortunately, these are exactly the kind of points I get hung up on:

"an unstored calc cannot be used on the child side of a relationship"

"Anytime a related field is referenced in a calc, it will try and make it unstored"

Why are these things so? Surely they're not arbitrary rules by the FileMaker developers. What problem would be encountered, for example, if an unstored calc were allowed on the child side? I ask in the spirit of "I wish I understood half of what I know."