Calculation equivalent for Filtered Portal?

[bac mac]

In layout A, I can readily display the desired field value (date) from a related table (using a filtered portal where I filter to display related records where a different field (Course Type) in table B = a particular string.  The relationships involves multiple fields and is also sorted to display the desired date.  That works fine.

 

I need to use that date (obtained from table B ) in layout A/table A in other calculations, so I need to place that date in a field in Table A.

 

I'd rather not use a script for this.

 

-> Is it possible to write a calculation that would result in the same value (date in this case) that I see in the filtered portal?

 

(I can do this if I set up fields in table A that contain Course Type designators (globally stored) for each of the desired strings and then set up TOs of table B with the designator and the other parameters in the relationship.  But this seems a bit unstable on this served file.  It also seems a bit messy requiring the additional designator fields in table A.)

[Fitch]

Sounds like a possible job for ExecuteSQL.

[comment]

In general, if you need to use the result in further calculations, it's best to use a filtered relationship (that works at the data layer) rather than just filtering the portal at the layout level. I don't see why this should "seem a bit unstable".

It's possible to get around the issue by giving the field inside the filtered portal an object name, then using the GetLayoutObjectAttribute function to retrieve the value. But then your calculation depends on the presence of the filtered portal as well as being in the context of a specific layout - hardly the best practice.
 

[bac mac]

I'd be very happy with a filtered relationship, but I'm not sure how to best accomplish that.  It was easy for the portal, but I don't see a similar mechanism without the portal.  Is there?

[comment]

You just need to add a predicate to the relationship that will exclude the records that are now filtered via the portal setup. Your description is not quite clear on that - I am guessing you'll need to add something like:

 

...

AND

TableA::gCourseType = TableB::CourseType

 

to the relationship setup.

 

 

You'd probably want to set TableA::gCourseType as a drop-down/pop-up using a value list of course types.

[bac mac]

OK, that is what I thought- having to add the additional field to Table A to accomplish the relationship, where with the portal, I could specify that in the portal filter.

The instability I mentioned was due to the fact that it is a served file and I keep forgetting that globals can easily disappear.  I guess globals in this case are best set during an open script.  The value in the global Course Type in table A is going to be pretty constant and only rarely changed by the end user.

 

Thanks very much for your help.

[comment]

that is what I thought- having to add the additional field to Table A

 

I am puzzled how you accomplish the portal filtering without having this field already. Unless your course type is hard-coded in the portal setup - in which case this:

 

The value in the global Course Type in table A is going to be pretty constant and only rarely changed by the end user.

 

cannot be true, since the value can only be changed by the developer.

 

I keep forgetting that globals can easily disappear.

 

Globals do not "disappear"; they revert to the last value they held when the file was opened in single-user mode.

 

I guess globals in this case are best set during an open script.

 

That too is an option. Another one is using a calculation field to supply a default value when the global is empty.

 

[bac mac]

The filter was hard coded into the portal, but I was trying to get away from using that portal.

Good observations.

Thanks you