10 Replies Latest reply on Apr 11, 2016 5:26 PM by user19752

    How do we pull related values from value list?

    GoodS

      I have a value list containing ID from "Colors" table.  “Colors" table has content such as:

       

      IDColorShort
      1RedRd
      2GreenGr
      3BlueBu
      4MagentaMa
      5YellowYw
      6BananaBa

       

      Our value list is generated through a script/ calculation and stored in a related table (through a join table).  Value list field is called “color list”, stored in table called “Color Set”.  Content of “color list” is selected ID’s from “Colors” table, as they are obtained through join table “Color set join”. 

       

      Current script is:

      Set Field [Color set::color list; ValueList ( Color set join::_kf_color )]

      Commit Records/Requests [No Dialog]

       

      “Color set” table primary key is stored in join table “Color set join”, so that sequence and color selection are stored as a Return delimited list of “Colors::ID” values, stored in “Color set join::_kf_color”.

       

      Brief:

      We require “Colors::Color” from this process for a priority list used to build layered garments, with primary (first item), separated by a space, then remaining values from “Colors::Short” field.  A process the current work flow participants are expecting.

       

      Example result:

      “Red BuYwBa”

       

      Currently, value list contains only Return delimited ID numbers.  We need Color and Short field content to be concatenated into a string, taken from ID’s taken from “Colors” table two steps away..  These principles apply to other processes we are trying to implement, such as build lists, forensic steps, work flows etc., where we select values stored in a portal, though a related join table.

       

      I have seen the ExecuteSQL suggested solution (simple and brilliant) to a similar problem posted recently, but after a whole day trying to figure it out, could a resident genius provide another clue for either script, calculation, or headache tablet?

       

      Is this type of conversion possible?  I could not find the way to script the parse via the list of IDs and then lookup their corresponding color value to concatenate and store.  So close, but no banana.

        • 1. Re: How do we pull related values from value list?
          erolst

          Assuming your structure is

           

          Colour --< ColourInSet >-- ColourSet

           

          do this: create a value list “ColoursForSet_c” of short names per set by using field: Colour::shortName, related values, starting from ColourSet

           

          In ColourSet, create a calculation field, type text, as cColourNamesShort:

           

          Let ( [

            itemList = ValueListItems ( "" ; "ColoursForSet_c" ) ;

            itemCount = ValueCount ( itemList )

            ] ; 

            Case (

              itemCount ;

              GetValue ( itemList ; 1 ) &

              Case (

                itemCount > 1 ;

                " " & Substitute ( RightValues ( itemList ; itemCount - 1 ) ; ¶ ; "" )

              )

            )

          )

           

          Finally, create your value list:

          using field values from ColourSet; 1. field: primary key, 2. field: cColourNamesShort; show second field only.

          • 2. Re: How do we pull related values from value list?
            user19752

            What is the "primary"?

             

            Using 2 SQLs

             

            Let ( [

              keys = List ( Color set join::_kf_color ) ;

              pri = GetValue ( keys ; 1 ) ; // first value in relation

              rest = RightValues ( keys ; ValueCount ( keys ) - 1 ) ;

              rest = Replace ( Substitute ( rest ; Char(13) ; "," ) ; Length ( rest ) ; 1 ; "" ) // remove last ,

            ] ;

            Case ( pri <> "" ; ExecuteSQL ( "SELECT Color FROM Colors WHERE ID=?" ; "" ; "" ; pri ) ) &

            Case ( rest <> "" ; " " & Substitute ( ExecuteSQL ( "SELECT \"Short\" FROM Colors WHERE ID IN (" & rest & ")" ; "" ; "" ) ; Char(13) ; "" ) )

            )

             

            not tested.

            • 3. Re: How do we pull related values from value list?
              GoodS

              Thank you

               

              Primary in this context is ‘most important’, or first in a list.

              Example Ferrari car has primary colour of Red.. Black interior, Grey stitching..

               

              The second, third colours are taken from the portal in the order they are selected.

              • 4. Re: How do we pull related values from value list?
                user19752

                So, you need some flag field for primary if you want to resolve using one SQL.

                • 5. Re: How do we pull related values from value list?
                  erolst

                  You need a flag field for primary any which way – in my method, the relationship must be sorted – unless you want to rely on the primary always having been entered first into the join table.

                  • 6. Re: How do we pull related values from value list?
                    GoodS

                    The ExecuteSQL function is my preferred type of resolving the related record issue.

                    Unfortunately, after three twelve hour days, the only result I have been able to achieve through FM is "?"

                     

                    Read, reread and researched SQL as applied to FM and so frustrating.. I have over twenty variations of code to lookup related table values, but nothing has worked.

                    I would have thought we could obtain field content through a tables unique key, but the FM ExecuteSQL function continuously produces "?".

                     

                    One result I could obtain, was through leaving out any WHERE statement, which produces a CR delimited list..

                    I expected ExecuteSQL to return a comma separated list.  Where to now?  Who knows.  Unresolved.

                    • 7. Re: How do we pull related values from value list?
                      erolst

                      GoodS wrote:

                       

                      The ExecuteSQL function is my preferred type of resolving the related record issue.

                       

                      Why? You have been presented with a perfectly working non-ExecuteSQL() solution.

                       

                      GoodS wrote:

                      Unfortunately, after three twelve hour days, the only result I have been able to achieve through FM is "?"

                       

                      Read, reread and researched SQL as applied to FM and so frustrating.. I have over twenty variations of code to lookup related table values, but nothing has worked.

                       

                      After 36 hrs, you could have taken another 2 minutes of your time and presented a few code attempts for us to inspect …

                      • 8. Re: How do we pull related values from value list?
                        GoodS

                        erolst

                         

                        I have realised the title of "How do we pull related values from value list?" could be taken to mean something other than what I am trying to achieve.

                         

                        I appreciate your incredibly fast response to my initial post and also following up your posting to assure I implement your recommendation.

                         

                        The problems I had with using a Value List to create a string, is that the generated string contains only one value from each of the combination of values obtained through a portal. I commenced a new new thread: ExecuteSQL to obtain related field content which I have uploaded a file, so that my problem can be better described and illustrated.  When I used the code segment you recommended, the result was a Text value which contained an ordered list of unique values from the Value list, which is sorted alphabetically.  I either made a mistake is setting up the Value list you recommended, or the result obtained is not what I required.

                         

                        I sincerely appreciated your response.  I just endeavoured to deepen my knowledge and further understand the process by which ExecuteSQL works, but more specifically, how I could use LEFT OUTER JOIN to control search results through SQL queries.  Regards Rob

                        • 9. Re: How do we pull related values from value list?
                          GoodS

                          Make sure ALL keys in SQL are strictly type: Number, or the SQL formula will not work.

                          I have attached the working file.  Many thanks to @19752 for his clear ExecuteSQL calculations, which ultimately power the solution.

                          • 10. Re: How do we pull related values from value list?
                            user19752

                            I'm glad for your solution works, but saying "Make sure ALL keys in SQL are strictly type: Number, or the SQL formula will not work." may give incorrect information for beginner readers. Other types can be key, using correct syntax of formula.