AnsweredAssumed Answered

getting field value from another table in a calculation

Question asked by JoeEgg on Jun 17, 2011
Latest reply on Jul 2, 2011 by LaRetta_1


getting field value from another table in a calculation


sry, FM newbie here.

using FM Pro 11 Advanced

I have two separate tables. in table 'A' I have a field of type calculation which results in text. Within the calculation I want to retrieve the contents of a certain field of a certain record found in a seperate table 'B' (with no existing relationships, but in the same db).

I have a text string which corresponds to a value of a field in one of the records of table 'B', and I want to retrieve and return the value of another field from that same record.

This is obviously simple and basic (or so I'd hoped), but I've spent just way too many hours on this and am now pulling out my hair. I'd greatly appreciate any help or even just getting pointed in the right direction.

Please let me know if any clarification on my part is needed.



not sure I should have posted the clarification as a reply, so adding it here


will attempt to clarify.

Table A has information on members, some fields of which are age, weight, height.
The calculation field in this table needs to compare the content of such  fields to min/max allowed values in order to determine eligibility for a  bunch of activities.

Table B contains the allowed values for each such activity. These  change frequently (the min/max values, not the activities), so it has  it's own table, and layout. Each record in table B has the activity  name, and min/max values for things like age, weight, height.

The calculation field's purpose is to display a text list of  activities a member is eligible for. So, I am trying to get a value from  table B and I know the activity name.

Simple example:
Activity in question is "Running"
Table B has a single record which has "Running" as the activity, and there is a min age value of 16 in that record.
Within the calculation field in table A I want to find out what the  content of the min age field in table B is of the record that has  "Running" as its activity.
Based on the return, I can display eligibility status of this member for this activity by comparing min age to the member's age.

I don't know how to get the content of the min age field from table B  (or any other field of a specific record in table B) within the  calculation field in table A.
My plan, once I figured this part out, was then to extend this  calculation to do comparisons for all the activities in order to display  a list of eligible activities for each particular member.

Please let me know if I am going about this all wrong. Any/all help greatly appreciated.