4 Replies Latest reply on Nov 21, 2014 12:33 PM by gregdc

    Alternative to using ExecuteSQL in a Table definition

    gregdc

      Title

      Alternative to using ExecuteSQL in a Table definition

      Post

      I am getting data from a system where the structure to their table looks like this:

      ClientID

      FieldID

      TextFld

      So If you want to know the zipcode for a Client you need to look at FieldID = 6 for that ClientID.   I would like to translate this into a more "normal" table for with Client_ID, Address1, Address2, City, State, ZipCode, Country, etc.   I know that I could do this with an ExecuteSQL in the field definition, but I am wondering if there is a better design with FM13Pro.  Would anyone like to make a suggestion?

        • 1. Re: Alternative to using ExecuteSQL in a Table definition
          philmodjunk

          I am not sure exactly what you are trying to produce. I suggest providing a bit more detail describing the end result that you want. Are you trying to define a new table with the data restructured, Are you attempting to present a "table like" view of the data on a layout from the existing design or do you have some other goal in mind?

          • 2. Re: Alternative to using ExecuteSQL in a Table definition
            gregdc

            Sorry for being vague.  Maybe this will help.   I have an incoming table with the three fields I described.   I have a second native FM table that I want to look at that incoming table and populate its fields from.  So if the incoming table is ExtTable and the FM table is ClientInfo, I want to define the fields in ClientInfo like this:

            ClientAddress1 = ExtTable where the ClientInfo.Client_ID = ExtTable.ClientID and ExtTable.FieldID = 1,

            ClientAddress2 = ExtTable where the ClientInfo.Client_ID = ExtTable.ClientID and ExtTable.FieldID = 2,

            ClientCity = ExtTable where the ClientInfo.Client_ID = ExtTable.ClientID and ExtTable.FieldID = 3,

            etc, etc for the 14 fields that are in ExtTable.    Once I have the ClientInfo table setup and populated I will use it with links to other FM tables and in scripts.

            Does that unmuddy the waters a little?   I think that I could do this with each ClientTable field being a calculated field using an ExecuteSQL statement, but I believe that would be very slow to process.  So is there a better way?   The ExtTable is changing all the time, so a ClientTable loading script would only be a snapshot and I am hoping for something a little more dynamic.    

            • 3. Re: Alternative to using ExecuteSQL in a Table definition
              philmodjunk

              All of these answers assume that you have used one method or another to populate the FileMaker table with the ClientID that matches to the set of records in ExtTable containing the same ClientID and you have the needed relationship defined in Manage | Database...

              If you used ExecuteSQL in auto-enter calculations to copy the data over into the new table, the process might be a bit slow, but only during the original import of your data by importing the ClientID value only into the FileMaker table's ClientID with auto-enter options enabled during the import. Replace Field Contents might also be used with ExecuteSQL expressions as a "one time per import" method to move the data into the FileMaker table immediately after the import. Either way, you get a slow import followed by no delays thereafter when working with the Filemaker table.

              Another non SQL method is just to place filtered one row portals to the original table on your layout where the portal filter specifies the value of FieldID for the record you want to supply data to that portal row.

              • 4. Re: Alternative to using ExecuteSQL in a Table definition
                gregdc

                Ok, you have pointed me in an interesting direction.   I am not sure how to use ExecuteSQL in auto-enter calc to bring in the new data, but it gives me something to look into.   Thanks.

                While I like the second idea, there is a lot more manipulation and linking to other tables that ClientInfo needs to do make the reports that I need to see.

                Thanks for the ideas!