7 Replies Latest reply on Jul 2, 2011 8:06 AM by LaRetta_1

    getting field value from another table in a calculation

    JoeEgg

      Title

      getting field value from another table in a calculation

      Post

      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.

      Thanks.

      **************

      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.

      Thanks.

        • 1. Re: getting field value from another table in a calculation
          LaRetta_1

          We need clarification.  There would be no reason to grab values from Table B unless it *is* related to Table A in some way.  Since it *is* related to Table A, they should be related.  But using abstracts makes it difficult to even make suggestions since it doesn't provide us with the context or purpose.  Why not use describe a bit more specifically what you are attempting? :^)

          • 2. Re: getting field value from another table in a calculation
            JoeEgg

            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 activites for each particular member.

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

            Thanks.

            • 3. Re: getting field value from another table in a calculation
              LaRetta_1

              Well, instead of focusing on a single calculation which, from the Members table, can't see all the activities, I would suggest that you use a relationship to display a portal of activities on the member layout which they would quality for.

              Something like the attached may suit your purpose:  http://www.4shared.com/file/0FIiANdT/activities.html

              I made a few assumptions on putting this file together but I think you'll see that using a relationship might be the best way to go. 

              • 4. Re: getting field value from another table in a calculation
                JoeEgg

                Thanks LaRetta, that works pretty nicely.

                I have just a couple issues left with this. First one I think I solved, which was that a min/max value had to exist for each activity (so I put ones in). For curiosity, would there be a way to ignore (not compare values) on fields that are empty?

                More importantly, there is another criteria for which I'm now stuck anew. For each activity when completed, a grade is given to the member for that activity. This grade which comes from a value list and is stored in a field in the member table. Each activity has its own such field. I need to check for the existence of a grade on each activity (along with the min/max checks you've solved for me) to determine if this activity should still be displayed. If there is a grade, then it should not be displayed, and vice versa (basically building a list of activities that still need to be completed for which each member fits the criteria).

                I tried to set the filters for the portal, by checking the grade fields (isEmpty check) but it doesn't seem to work. As soon as I put that check in, the activity isn't displayed no matter what.

                Again, thanks for your help.

                • 5. Re: getting field value from another table in a calculation
                  LaRetta_1

                  "For curiosity, would there be a way to ignore (not compare values) on fields that are empty?"

                  You can change the relational filters to one-side <= or the other but it would work across the board ... you couldn't restrict if there IS a max value but not restrict if there is not; at least not at the relationship level.  You could instead use portal filters (if vs. 11) to restrict accoding to min and max (and drop it from the relationship).  However, it is far easier (and it will be faster) if you entered max or min values.  You can set it to 9999999 for instance.  You can even have the activity min and max fields auto-enter 0 (for min) and 9999999 (for max) and then just change the figures if you wish to restrict further.

                  "For each activity when completed, a grade is given to the member  for that activity. This grade which comes from a value list and is  stored in a field in the member table. Each activity has its own such  field."

                  Hold on ... are you saying the Activities table holds a Member ID?  How can you have (see bold above) a FIELD in members which holds a grade for an activity when there are MANY activities?  I believe that you are missing a table.  You should have Members, Activities (which says what activities are available and the min/max figures for that specific activity) and a MemberActivities table (which holds the MemberID and ActivityID).  I hope you have auto-enter, FM-generated serial in Members called MemberID and also one in Activities called ActivityID.

                  Before we can continue, we must be it straight on what you have.  Can you upload your file somewhere and provide a link to it here?

                  • 6. Re: getting field value from another table in a calculation
                    JoeEgg

                    I was pulled off this project (temporarily put on hold while I need to focus on something more pressing), but I'd like to continue getting your help so I know how to continue when I get back to it. Just to give a bit of added background, I was handed this db and asked to just get this one aspect working. So the member's table was already there, as well the activities grades (which I'll discuss more below), but nothing as far as the activities min/max for which I created a new table.

                    "Hold on ... are you saying the Activities table holds a Member ID?  How can you have (see bold above) a FIELD in members which holds a grade for an activity when there are MANY activities?"

                    No, the activities min/max table does not hold a member id. It has nothing more to it than how you set yours up in the sample you provided for me. There are many activities indeed, but the activities themselves really never change, just the min/max values for them. When I was handed this db, there were already activity-specific grade fields in the member table for each activity. I know where you're coming from in questioning this, and this is not how I would have designed this either, but again, I just inherited this to add this one functionality to an existing db that was created by someone who knows little about DBs, but has been able to make FM work for him up to this point.

                    So, where we're at now is that with your help of creating the relationships, I can now successfully display what activities each member qualifies for based on the min/max values. But, I need to make sure that the member hasn't already been graded for an activity if it is to be displayed. I understand that seeing what I have could help a great deal, but there's too much to it for me to clean it out to get it to you right now. If moving forward is not possible without this, then once I get put back on this project to finish it out, I'll get you a version of what I have.

                    Thanks again, your help has been great.

                    • 7. Re: getting field value from another table in a calculation
                      LaRetta_1

                      With your current (highly incorrect) structure, it is not possible to provide an easy answer and certainly not without seeing your file.  I am currently on vacation and only have access to computer every few weeks.  I will not be available to assist again probably for another 3-4 months while on vacation since this web login system doesn't even have FM on it.

                      Others will have to step in and assist.