Calculation fields on the child (many) side of a relationship must be stored/indexed fields. By definition, calculations that reference data in other tables cannot be stored. Therefore your calculation as defined cannot be used as a match field on the child side of a relationship.
Thanks for the confirmation. I'l stop trying to make it work that way. How can it be defined so that it will work?
Not sure from your description. Can't tell exactly how you want your "filter" table to function here.
Just to clarify my first post:
MainTable::date_calc = PortalTable:: Date
will work--where your layout is based on MainTable and your portal is based on PortalTable--but the reverse will not.
The filter table holds fields for
I want to have the relationship
filter::SessiontID = SessionTable::SessionID_kprime
filter::WorkDate = SessionTable::SessionDate_calc
SessionDate_calc is define as a calculation field = DetailTable::Date
The layout is based on the filter table with a portal into SessionTable based on the SessionDate, but the SessionTable::SessionDate gets the actual date from a related table. The SessionTable is actually a "join" table connecting various details of the session, participants, billing, notes, etc. So from the filter I want to be able to get a list of all session records for a particular TreatmentID and date to show in the portal. I am certain you are correct in your original response because I get an almost identical error message from FMP when I tried to make it stored.
Can you suggest an alternative way to set up the field definition so that I can use it in a relationship like this?
I think you'll need to physically copy a date into SessionTable::SessionDate (no "calc").
You can define an auto-enter option such as Looked Up value to copy the data. The challenge here is whether the date in DetailTable:: Date is subject to change. If it doesn't/rarely changes, the looked up value option will probably work for you. The trick is that you have to script an update of the value in SessionDate any time the matching DetailTable:: Date field's value changes.
Thanks for your follow through on my question. The date will probably be changed at times and a single date is reference from many of the session records. Unfortunately, I dont see any option other than what you described, to use one version or another of something that needs to be scripted, regretably so.
If you are using FMP 10, this is a good place for a strategically placed script trigger that will perform an update script every time the date field is updated.
Ken S wrote:
Unfortunately, I dont see any option other than what you described, to use one version or another of something that needs to be scripted, regretably so.
See if this helps: