Is this really a relationship or a portal filter expression?
Your example shows using the cartesian join operator and then lists several expressions that compare values.
I think you are using a relationship, but have used the expressions in place of the calcualtion fields used in it to explain the function of the relationship, but would like to be sure.
But if this were a filter expression, the limitation on indexed fields does not apply. Thus, you might be able to use a filtered portal with a portal filter expression that compares these values instead of defining it in the relationships.
Another option that may work is to set up an auto-entered calculation to compute the date. This field will be indexable, but you may need to take extra steps to be sure the field updates corrrectly should a field from a different table be changed as auto-entered calculations fail to update automatically when the field that changes value is from a different table or record. (And this won't work at all if the calculation refers to get ( CurrentDate ). )
And unindexed fields CAN be used in some relationships. It depends on the context. In a one to many relationship, both global and unstored fields can be used on the "one" side of the relationship but not the "many" side.
So I've rejigged my relationships/design to make this work without the calculation fields but was forced to use a portal filter to only show all the full-time and part-time employees. Is there a way to have this built right into the relationship? As long as the employement type matches one in a list of approved types the link will be made? this way my calculation count fields will work properly, right now it counts the records that have been filtered out as well.
Sorry, immediately after I posted I thought of a way to make this work but it's not an easy solution. I realized I could have a constant fields on the one table of all the job terms I want filterd out and then have
cJobTerm (not equal to) Employement History::Type
for this case it will certainly work but I'm still curious to know if there another way in case I stumble on this again someday and the list of wants is one of say 5 or 10 and the list of do not wants is in the hundreds, my method would be horribly tedious.
With FileMaker 12, you have the option of useing the ExecuteSQL function which provides options not possible in earlier versions.
You could use this function to produce a return separated list of ID numbers and then you could use it as the match field in your portal's relationship.
For certain "read-only" applications, the calculation field could take the place of the portal all together if you use returns as the record separator and tabs as the field separator. you can give the field a scroll bar and use the Appearance tab in the Inspector to position tab stops to get nice columns of data. The result will look very similar to a portal, but you can't edit any of the data.