Calculated Field

[jjb]

I've got four related tables:

tblModel, tblAttend, tblClass, tblClassType

tblModel:

- modelID

tblAttend

- modelID

- classID

tblClass

- classID

- classDate

- classTypeID

tblType

- classTypeID

- classCategoryID

The tables all relate on the ID fields. What I'm trying to do is create a calculated field in tblModel which shows the details from the last related tblClass record for classes which occurred in the past and which are for classTypeID's in a specific category.

I'm able to do the date restriction no problem by adding a global date filter in tblAttend. However, restricting the classTypeID to a specific category is proving more difficult.

I found I was able to do this if I had an auto-enter field for the classCategory in tblClass, but this would break if the category changed for a specific classType. If I tried to use a calculation field for the classCategory in tblClass, it just didn't seem to work.

Any advice? I'm attaching a sample file with how far I've got. I'm able to do the date filter, but not the category.

test4.zip

[Ender]

Why not use a global ClassCategory filter in Class as well?

Note that these intermediary global filters will require a Refesh Window [ Flush cached join results ] script step in order for the portals to update.

[jjb]

I've tried doing that, but it didn't seem to work. I applied it to the relationship from tblClass->tblType. It didn't filter the resulting calculated field in the tblModel table.

The calculation there is basically:

last(tblClass::classID)

This would work in a portal if I based the portal on the appropriate TO for tblType, but not in the calculated field.

The only thing I've been able to get to work is doing an auto-enter field in tblClass for the category and filtering on that from tblAttend. The same filter wouldn't work if I used a calculated field instead.

[Ender]

I read you.

It looks like you need to make the destination portal from a TO of Class on the other side of the Category table. Move the filters around a little, and voila!

See the attached file.

test4v2.fp7.zip

[jjb]

That's it! Thanks so much!

[jjb]

Hmm, maybe not. I'm still not quite able to get the calculated field correct. I'd like to have a calc field which shows only the last (or next) chronological record from class for which there is a modelID->classID correspondence. The specific classID seems to get lost in this scenario and I wind up filtering down to any class with the appropriate classCategory and Date, not exactly what I want...

[jjb]

Actually, not quite.

I'm still not able to get the calculated field correct. I'd like to have a calc field which shows only the last (or next) chronological record from class for which there is a modelID->classID correspondence. The specific classID seems to get lost in this scenario and I wind up filtering down to any class with the appropriate classCategory and Date, not exactly what I want.

It looks like this type of filtering relationship doesn't really work in FM and that I'll have to use the auto-enter field within the tblClass.