9 Replies Latest reply on Apr 11, 2016 12:26 AM by user19752

    ExecuteSQL to obtain related field content


      Assuming a structure of:

      Colour --< ColourInSet >-- ColourSet

      We have all constructed a join table, to select Records from a related table.  Benefiting from controlling data consistency, compressing character overhead, through utilising key field referencing, is the reason we first adopted Filemaker.

      The challenge occurs when we require field values from the referenced record other than the Primary key.

      When selecting the primary keys through a portal, the order of their selection and the values themselves are extremely important.

      Typical application:

      Values selected in English, but upon language change to Chinese, or other symbol based references, another value needs to be calculated from the portal keys selected and in the sequence they were selected.

      Values selected through long descriptions, although concise compilations of abbreviations are often needed for everyday use.

      Build sheets require symbols to be adopted for everyday use, whereas the symbol names are selected, by their long description (short description, or other string, based upon the context) as part of the specific workflow process.

      While we can store Chinese character, container filed, symbol, short name, abbreviation, or long name in the original Record, under the primary key, the simple concatenation of required fields from the primary key values selected through a portal is proving much more difficult to compile.


      Handmade garments [or car/ bicycle/ yacht.. ] are easily described by their color combinations:

      Main color/ overlay/ beading/ crystals..

      Ivory/ Ivory/ Silver/ Clear

      Short Color description: Iv ISCl

      Each Color has three  [English] words, e.g. Ivory, Iv, I and White, Wh, W or Clear, Cl, Cl or Cafe, Ca, Ca

      Note some colors have the same 'double' letter and 'single' letter abbreviation, whereas others have different values.  This requirement is specific to the application I have been commissioned to comply with.  Basically, value in field01, field02 ..


      After we select a series of colors via a portal, how to construct Text field, such as: "Iv ISCl" (in ColourSet::c_Combo)

      IE Double letter, space, Single letter..


      After obtaining some assistance, I have been unable to obtain any calculated value from ExecuteSQL, other than "?", all records.

      If we focus on value lists, only a single reference to a color is possible and sorting via PK in join table is also not possible, although in reality: Iv/ Iv/ Si/ Cl <> Iv/ Si/ Cl <> Cl/ Iv/ Si..  Imagine ordering a garment in "Ivory", only to have it supplied "Clear"


      Message was edited by: Robert Lakelin Thank you Mike.  I have attached a file to clarify my question.

        • 1. Re: ExecuteSQL to obtain related field content

          It's still not super clear what you're trying to GET out of a calculation.


          Post a sample file with a bit of sample data populated in the structure you've outlined, as well as some of the ExecuteSQL() functions you have tried. Then we'll most likely be able to point out any errors in your data structure, data keys, or calculation syntax.


          The ? returned by ExecuteSQL() is indicative of calculation errors. Seeing your calcs would be especially important.

          • 2. Re: ExecuteSQL to obtain related field content

            Your question is changed in details, but rougthly my "2 SQL answer" may be applicable.

            How do we pull related values from value list?

            As erolst mentioned in there, you need field to sort the records in portal if the order is important. If it is creation order (same as unsorted), you can use calculation field Get(RecordId) or created time stamp.

            In old thread, there was only "primary and others", so I got first one as primary from unsorted list in FM side GetValue() function. I don't do it in SQL.

            • 3. Re: ExecuteSQL to obtain related field content


              Thank you. I was unable to make your code work in the file.

              I posted the file to the topic several minutes ago.

              I have been trying for several days to get your code to work, looking in forums, searching SQL coding, missing manuals etc, but still no result.  Can you please look at the script Test_1-19752 in the file, to see where I am going wrong?

              • 4. Re: ExecuteSQL to obtain related field content

                This calc looks properly formatted but sometimes you need to escape quote things for it to work right.


                ExecuteSQL ( "SELECT Char_02 FROM Color WHERE _kp_Color = ?" ; "" ; "" ; primary )

                Try this...

                ExecuteSQL ( "SELECT \"Char_02\" FROM Color WHERE \"_kp_Color\" = ?" ; "" ; "" ; primary )

                The next calc might fail due to the IN ( ) part.

                IN ( ) requires specific syntax that is not easy to reproduce in Fm... IN ( 'MI','IN','OH')

                Text must be a comma delimited list but each value must also be enclosed in single quotes.

                The contents of your Other field must conform to the requirements of IN ( ) .


                ExecuteSQL ( "SELECT Char_01 FROM Color WHERE _kp_Color IN (" & Other & ")" ; "" ; "" ) ; Char(13) ; "," )

                Another option is to nest a SELECT within the IN ( ).. select always produces a conforming IN ( argument ) ...

                i.e ExecuteSQL ( "SELECT Char_01 FROM Color WHERE _kp_Color IN ( SELECT whatevercol FROM whatevertable WHERE whatrevereconditions ) )" ; "" ; "" ) ; Char(13) ; "," )

                • 5. Re: ExecuteSQL to obtain related field content

                  As coherentkris mentioned, you need quote for table/column names if special char / reserved word is used. Names  starting with under bar are special.


                  I prefer number type field if your id have really number value, simple and faster than text.


                  This is not problem but "Other" is a reserved word in FM calculation. Without assigning, it has value 10.

                  • 6. Re: ExecuteSQL to obtain related field content

                    Your suggestions are INVALUABLE.

                    Thank you so much.

                    Quoted the variables AND changed field types of ID to Number..  YES


                    coherentKris thank you also.

                    A five day breakthrough from a couple of simple changes

                    • 7. Re: ExecuteSQL to obtain related field content

                      In old thread first you wrote "similar problem posted recently", but it was not correct as you know now, the "recent problem" uses checkbox for selecting values then there never be duplicated value in selection.


                      It should be able to compose complex 1 SQL, but I first made 2 SQL answer.


                      I add order_in_set field to join table, to sort relation.


                      Let ( [

                        primary = Color_set_join::_kf_color ;     // first value in relation

                        shortValues = ExecuteSQL ( "SELECT c.Char_01 FROM Color_set_join AS j, Color AS c WHERE j.\"_kf_color\"=c.\"_kp_Color\" AND j.\"_kf_color_set\"=? ORDER BY j.order_in_set" ; "" ; "" ; Color set::_kp_Color_set )

                      ] ;

                      Case ( primary <> "" ; ExecuteSQL ( "SELECT Char_02 FROM Color WHERE \"_kp_Color\" = ?" ; "" ; "" ; primary ) ) &

                      Case ( Count ( Color_set_join::_kf_color ) > 1 ; " " & Substitute ( RightValues ( shortValues ; ValueCount ( shortValues ) - 1 ) ; Char(13) ; "" ) )


                      • 8. Re: ExecuteSQL to obtain related field content

                        Nice.  I was working on a LEFT OUTER JOIN for an ORDER BY kpColor_join (ID for Color_set_join table), as a sort for the “shortValues” step.


                        Your solution will work nicely, once I understand the format of:




                        What is best practice to feed the field:  Use a script to increment and Set Field ( Color_set_join::order_in_set ) each time a row in the portal is added, or removed?


                        Could the value of order_in_set simply be kpColor_join (ID for Color_set_join table)?

                        I tried to replace order_in_set in your function with kpColor_join (ID for Color_set_join table), but then no values show for the “shortValues” steps.



                        Notes:  As a result of your feedback, I have updated all kp notation to simply ID.  I have also updated all kf.. to simply ..ID.  e.g. kfColor is now ColorID.

                        Migrating from a Ruby background, I feel it best to adopt simplest practices while in FM.  Thank you.

                        • 9. Re: ExecuteSQL to obtain related field content

                          ID for Color_set_join table is good for the purpose, while you use delete/add to change the order in portal.

                          I made for test, simple number field with auto-enter, then "Replace field contents" to fill existent records. This is almost same value of ID in the join table.


                          The value in order_in_set may be better if it start at 1 in each set,with auto-enter calculation, if user see it.

                          Renumbering after user change may be done in onRecordCommit trigger.