6 Replies Latest reply on Nov 27, 2012 7:02 PM by benchwrm11

    Extracting Value from List to Field in Related Table

    benchwrm11

      Hi All,

       

      I have a field that has a list which contains multiple catergory:subcategory:value, with each separated by an return. For example:

       

      Emergent: Cattail: 1

      CWD: CWD: 1

      Open: NA: 98

       

      There are 5 potential types categories, and 11 subcategories. I am trying to write a script or calculation that will extract each values to a specific field in a related table (i.e. Emergent Cattail goes to Emergent Cattail field, CWD goes to CWD field).

       

      While I'm sure the correct GetField statement would get me there, I've been struggling to find it. Any help would be greatly appreciated.

       

      Thanks!

        • 1. Re: Extracting Value from List to Field in Related Table
          ch0c0halic

          I'd probably use something like this.

           

          Set Field related::Emergent ; GetValue ( Catergory_List ; 1 )

          Set Field related::CWD ; GetValue ( Catergory_List ; 2 )

          Set Field related::Open ; GetValue ( Catergory_List ; 3 )

           

          Where "related::" is a relationship based on the unique record ID (CB: serial number) with allow creation of related records.

           

          If the first word isn't supposed to be stored in the related record then you would also remove everything up to and including the first space of the GetValue[].

          • 2. Re: Extracting Value from List to Field in Related Table
            comment

            benchwrm11 wrote:

             

            I am trying to write a script or calculation that will extract each values to a specific field in a related table (i.e. Emergent Cattail goes to Emergent Cattail field, CWD goes to CWD field).

             

            What would be the purpose of that? It sounds like you are trying to replace one bad structure with another one just as bad, if not worse. You should extract each item to a separate record, splitting it into three fields: Category, SubCategory and Value.

             

             

            You can use the GetValue() function to extract individual items. Then try something like:

             

            GetValue ( Substitute ( item ; ": " ; ¶ ) ; n )

             

            to populate the three fields - using 1, 2 or 3 as the corresponding n value.

            • 3. Re: Extracting Value from List to Field in Related Table
              PalmDBS

              Do a Set Field By Name with the Target field specified as

               

              Let([

              StringAsList = Substitute(CurrentTable::FieldWithString;":";"¶")

              ];

              "RelatedTable::" & Trim(GetValue(StringAsList;1)) & " " & Trim(GetValue(StringAsList;2))

              )

               

              and the calculated result set as

               

              Trim(GetValue(Substitute(CurrentTable::FieldWithString;":";"¶");3))

               

              ------------

              Assuming that CurrentTable::FieldWithString holds a value of "Emergent: Cattail: 1", this script step would save a value of 1 to the field RelatedTable::Emergent Cattail

               

              However, I notice in your second example, you only use one if the values ("CWD") - and not the first AND the second as you do in the first example.  Is this correct, or should it go to RelatedTable::CWD CWD?

              • 4. Re: Extracting Value from List to Field in Related Table
                PalmDBS

                If you want to collapse the second element if duplicate, do something like this...

                 

                Let([

                StringAsList = Substitute(CurrentTable::FieldWithString;":";"¶");

                Element1 = Trim(GetValue(StringAsList;1));

                Element2 = Trim(GetValue(StringAsList;2))

                ];

                 

                If(

                     Element1 = Element2;

                     "RelatedTable::" & Element1;

                     "RelatedTable::" & Element1 & " " & Element2

                 

                )

                )

                 

                Or, if you want to collapse the second element if duplicate or NA, do this...

                 

                Let([

                StringAsList = Substitute(CurrentTable::FieldWithString;":";"¶");

                Element1 = Trim(GetValue(StringAsList;1));

                Element2 = Trim(GetValue(StringAsList;2))

                ];

                 

                If(

                     Element1 = Element2 or Element2 = "NA";

                     "RelatedTable::" & Element1;

                     "RelatedTable::" & Element1 & " " & Element2

                 

                )

                )

                 

                 

                This would give you these results:

                Field value of "Emergent: Cattail: 1" saves a value of 1 to RelatedTable::Emergent Cattail

                Field value of "CWD: CWD: 1" saves a value of 1 to RelatedTable::CWD

                Field value of "Open: NA: 98" saves a value of 98 to RelatedTable::Open

                1 of 1 people found this helpful
                • 5. Re: Extracting Value from List to Field in Related Table
                  benchwrm11

                  Thanks dbmike! Your reply got me on the right track.

                  • 6. Re: Extracting Value from List to Field in Related Table
                    benchwrm11

                    Thanks all for your help!

                     

                    I ended up using dbmike's format to make a calculation for each of my fields of interest. Clunky, but it works. My apologies to the programmers out there -- your eyes will probably burn reading the below

                     

                    Let

                    (

                      [

                        StringAsList = Substitute(Structure Data Entry::Overall;":";"¶");

                    Entry1 = Trim(GetValue(StringAsList;1));

                    Entry2 = Trim(GetValue(StringAsList;4));

                    Entry3 = Trim(GetValue(StringAsList;7));        .... Up to Entry_n

                    Value1 = Trim(GetValue(StringAsList;3));

                    Value2 = Trim(GetValue(StringAsList;6));

                    Value3 = Trim(GetValue(StringAsList;9))         .....Up to Value_n

                    ];

                     

                    If(Entry1= "Open"; Value1;

                      If(Entry2= "Open"; Value2;

                          If (Entry3= "Open"; Value3;"")     up to n

                      )

                    )

                    )