7 Replies Latest reply on Dec 24, 2016 6:14 AM by dj1up

    Different Tables and Two Fields = One Answer?

    dj1up

      Well my job has once again asked me to set up a rental program for our ski shop.  So my task at hand is to calculate and script out the process of how we set ski bindings using ISO blah blah blah......zzzzzzz.  I will save you from the rest of the details as they do not pertain to this question.

       

      What i need to calculate is the skiers code (ranging from A-P), based off of two fields in my People Table and evaluate and extract the answer from my DINHeightWeight table. 

       

      Tables:

           Fields:

      People

           _id_DINwAndh_fk - Text Field

           Height - Drop down from DINHieightWeight

           Weight- Drop down from DINHieightWeight

           SkierCode - Edit Field

      DINHeightWeight

           _ID_DINHW_PK - UUID

           Height - Text Field

           Weight - Text Field

           SkierCode - Text Field

      DIN_Height_Weight_Code.jpg

      How to:

        Start with the skiers height and weight on the chart.  Look at both columns and see which value you come to first on the chart starting at the top and working your way towards the bottom.                             

       

      Height

      Weight lbs.

      Skier Code?

      Skier   A

      4’8”

      91

      G

      Skier B

      5’11”

      182

      L

      Skier C

      5’10”

      208

      K

                                  

        

      Height

      Weight lbs.

      Skier Code?

      Skier A

        4’8” 91G

      Skier B

      5’11”

      182 L

      Skier C 

      5’10”

      215

      K

      Start with the skiers height and weight on the chart. 

      Look at both columns and see which value you come to first on the chart starting at the top and working your way towards the bottom.

       

       

       

       

       

      SCRIPT STEP:

       

      DINHeight and Weight.png

      RELATIONSHIP:

      din RELATIOINSHIP TO.png

      Heres my T.O. graph setup

       

       

        • 1. Re: Different Tables and Two Fields = One Answer?
          erolst

          Simply create in your lookup table four number fields - weightFrom, weightTo, heightFrom, heightTo - and fill them in (that applies also to the codes where height is empty at the moment).

           

          From your Skier table, create two relationships where

           

          Skier::weight >= Lookup_byWeight::weightFrom

          Skier::weight <= Lookup_byWeight::weightTo

           

          Skier::height >= Lookup_byHeight::heightFrom

          Skier::height <= Lookup_byHeight::heightTo

           

          To retrieve the first matching result for either weight or height, use

           

          Min ( Lookup_byWeight::code ; Lookup_byHeight::code )

          1 of 1 people found this helpful
          • 2. Re: Different Tables and Two Fields = One Answer?
            philmodjunk

            A few additional suggestions: Don't Use Insert Calculated Result for this purpose. Set Field does the same thing and is less "brittle" as Set Field won't fail to work if the target field is later removed from the layout where "Insert..." steps will.

             

            I'd set up some calculations to take the height in feet and inches and calculate the height in total inches to make the look up process simpler. I'd do this both for the Skier's height and the Height value stored in your table.

            • 3. Re: Different Tables and Two Fields = One Answer?
              dj1up

              Thank you phill ill give it a go. keep you posted.  this s step one of a longer script. 

              • 4. Re: Different Tables and Two Fields = One Answer?
                dj1up

                Thank you for the help.  im setting up the relationships now. keep you posted.

                • 5. Re: Different Tables and Two Fields = One Answer?
                  dj1up

                  Here is the whole chart I'm working from there are no values for height until "H" of the code. will this effect your previous statement and calculation suggestion?

                   

                  ski_bindings_din_chart.jpg

                  • 6. Re: Different Tables and Two Fields = One Answer?
                    erolst

                    Allow me to quote myself:

                     

                    "[...] and fill them in (that applies also to the codes where height is empty at the moment)."

                     

                    (Emphasis not in the original.)

                     

                    That's because in a scripted Find (or a looping construct like VLookUp), empty fields/values can be taken into account and processed, but not in a relationship, where empty is not equal to empty.

                     

                    Just use

                     

                    from 0 to 58

                     

                    Do the reverse for N to P, i.e.

                     

                    from theLastValueIndicated to someHumanlyImpossibleValue

                    1 of 1 people found this helpful
                    • 7. Re: Different Tables and Two Fields = One Answer?
                      dj1up

                      Ohhhhh Ok got it.  I also have changed the height input field to two fields, HeightFeet and HeightInch.  I have a cal field now set up to convert the two height fields to inches (number field).  So guess what that means?  I have to delightful task of doing that to the other fields in the skier table.  Luckily they provided the conversions for me under the height on the chart. 

                       

                      P.S. Very nice self quote i must say.......