6 Replies Latest reply on Apr 12, 2016 9:55 AM by erolst

    Using a Variable in Fieldname Query

    marty72

      I have an iPad solution for data collection (including tree species and heights). At the beginning of a project you set a minimum height for each tree species that may be assessed. This occurs in a simple table (MAIN_REGEN_HT) that contains a field for each species where you enter the minimum height for the tree species.

      Later in the solution in a related child table people are collecting information about tree species and collecting actual heights for these trees.In the Layout for this child table (REGEN) I have a button with a script that is to be clicked when the actual height for a tree is over the minimum height for the tree species (the Tree is considered Free to Grow when it is above a certain minimum height). In this script I want to check that the actual height is greater than the minimum height that was specified in the related table at the beginning of the project. [This check is important in case the data collector forgets the minimum height for the species].

       

      I tried using a variable to replace the tree species (PW) in this query but obviously FM doesn't like variables in field names:

      REGEN::HT > MAIN_REGEN_HT::PW_HT

      e.g. REGEN::HT > MAIN_REGEN_HT::$$ht_pw_HT


      I also tried Get (ScriptParameter) which is set when the tree species is selected before they would press the button with this script:

      REGEN::HT > MAIN_REGEN_HT::PW_HT

      e.g. REGEN::HT > MAIN_REGEN_HT::(Get (ScriptParameter))_HT

      It didn't like this either.


      I am obviously headed down the wrong road so any advice would be most welcome.

      Thanx

      Marty

        • 1. Re: Using a Variable in Fieldname Query
          mikebeargie

          Check out the GetField function to get the contents of a field based on a name that you can calculate.

          • 2. Re: Using a Variable in Fieldname Query
            Mike_Mitchell

            I'm not really certain what you're talking about here, but you can evaluate the value in a field based on a variable using the Evaluate function.

             

            The question is, why is the name of your field changing? I suspect, based on your writeup, that you have a separate field for each species of tree?

            • 3. Re: Using a Variable in Fieldname Query
              erolst

              marty72 wrote:

              REGEN::HT > MAIN_REGEN_HT::PW_HT

              e.g. REGEN::HT > MAIN_REGEN_HT::$$ht_pw_HT

               

              That would be

               

              GetField ( "MAIN_REGEN_HT::" & $$ht_pw_HT )

              or

              GetField ( "MAIN_REGEN_HT::" & Get ( ScriptParameter ) )

              or, generically

              GetField ( fieldNameAsTextExpression )


              however you (syntactically valid) construct that text expression. (You could also use Evaluate() instead of GetField() )

               

              But you're barking at the wrong tree heading in the wrong direction doing this:

              marty72 wrote:

              This occurs in a simple table (MAIN_REGEN_HT) that contains a field for each species where you enter the minimum height for the tree species.

              This table should even be simpler in that it only holds an id, a species ID and a single height field; then you add one record per species, and create a relationship between

               

              REGEN:id_species = MAIN_REGEN_HT_bySpecies::id_species

               

              and simply check if

               

              REGEN::HT > MAIN_REGEN_HT_bySpecies::allowedHT

               

              btw: any reason to name your tables and fields in allcaps?

              • 4. Re: Using a Variable in Fieldname Query
                marty72

                Yes the table has about 20 tree species fields (SPC1_HT, SPC2_HT, ...) with a minimum height for each species. I want to check the height in my child table against the height in this table.

                I will have a look at GetField as well.

                Thanx

                • 5. Re: Using a Variable in Fieldname Query
                  marty72

                  Barking up the wrong tree - good one

                  I see what you mean about making a simpler table for the heights by species. I will give that whirl along with the GetField suggestion.

                  Thanx to all for the advice.

                   

                  The all caps for table names is just a carryover from my coding in AML in ArcInfo - bad idea?

                  • 6. Re: Using a Variable in Fieldname Query
                    erolst

                    marty72 wrote:

                    I see what you mean about making a simpler table for the heights by species. I will give that whirl along with the GetField suggestion.

                     

                    Good idea, but if you implement that, you don't need GetField() anymore – now there is only one field to check against, and the relationship will automagically point at the correct record for the current species.

                     

                    marty72 wrote:

                    The all caps for table names is just a carryover from my coding in AML in ArcInfo - bad idea?

                     

                    Not programmatically; but I think it's visual noise, and it robs you of the dynamic range that lower- and uppercase provide  (think of Piano Forte …).

                     

                    But then again, it's your db.