Whew. You're talking to people here, not machines - have mercy and use less abstract terms. For example:
I have a TargetField in the MainTable. I want it to lookup the value from the Values table, but if there is no match, it should lookup from the AltValues table.
This can be done by nesting the Lookup() function (the field should be set to auto-enter calculated value, replacing existing data).
can I do it as a realtime lookup so that the data in field M in table 1 reflects changes in field M in tables 2 or 3?
No. The target of a lookup does not change when the source value changes. That's the entire point of a lookup. You can, however, have a calculation field along the lines of:
Case ( not IsEmpty ( Values::MatchField ) ; Values::SourceValue ; AltValues::SourceValue )
Thanks for that. I have used other relational databases but not filemaker so I find the syntax difficult.
What is the Matchfield in the calculation you sent me - as I have 2 fields that have to match for the relationship between MainTable and Values?
When I use just the first of these 2 fields as the "MatchField" in the calculation, the data from "Values" appears correctly in TargetField, but not that from "AltValues". Also, if I change the SourceValue data in Values, it does not change the TargetField value. Does Filemaker have an option where there is a real-time calculation in a field each time it is accessed, not just when a new record is entered?
The MatchField is the field used in the definition of the relationship. If you have several matchfields, you can use any one of them for this calculation. Actually, you could use ANY field from the related table, as long as it cannot be empty - for example, the primary key of the related table.
if I change the SourceValue data in Values, it does not change the TargetField value.
Make sure the 'local' field is a Calculation field - NOT a field with auto-entered calculated value. Note also that after changing the related value you must commit the related record, and the screen needs to be refreshed for the change to become visible in the calculation field.
Thanks - it works!!!