6 Replies Latest reply on Mar 19, 2017 11:08 PM by PowerSlave

    Source Fields missing when using Calculated SQL text with Import Records

    lkingsella

      When using the Import Records script step with an ODBC data source and calculated SQL text, no Source Fields are listed in the Import Field Mapping dialog. The target fields are listed, and the data does import, but I have to refer to my query to know the field order. If I use the SQL Text option, the Import Fields are shown, but I have date fields that change, so I have to use Calculated SQL text for my query. Am I doing something wrong? Thank you!

        • 1. Re: Source Fields missing when using Calculated SQL text with Import Records
          beverly

          If you print the script (to PDF) what do you see?

          Sometimes the print is revealing. Can you post here (just that step, if necessary) & perhaps your calculation (even if it varies)?

           

          Sent from miPhone

          • 2. Re: Source Fields missing when using Calculated SQL text with Import Records
            lkingsella

            Hi Beverly,

             

            Here is the script:

            Go to Layout [ "fm_test" (fm_test) ]

            Set Variable [ $startStamp ; Value: ToSQLDate ( Get ( CurrentDate ) ) & " 00:00:00" ]

            Set Variable [ $endStamp ; Value: ToSQLDate ( Get ( CurrentDate ) ) & " 23:59:59" ]

            Import Records [ With Dialog: Off ; DSN: sql_test; Calculated SQL Text: "SELECT..." ; Update matching; Mac Roman ]

             

            Here is the Calculated SQL text:

            "SELECT

              DISTINCT(od.client_id) AS `Account_Number`,

              TRIM(CONCAT(TRIM(cdd.bill_first_name), ' ', TRIM(cdd.bill_last_name))) AS `Bill_Name`,

              cdd.bill_company AS `Bill_Company`,

              UPPER(cdd.bill_address1) AS `Bill_Address`,

              cdd.bill_city AS `Bill_City`,

              cdd.bill_state AS `Bill_State`,

              cdd.bill_zip AS `Bill_Postal_Code`,

              cdd.bill_country AS `Bill_Country`

            FROM order_data AS od

            LEFT JOIN client_default_data AS cdd ON cdd.client_id = od.client_id

            WHERE od.stamp >= '" & $startStamp & "' AND od.stamp <= '" & $endStamp & "'"

             

            Here is a screen shot of the Import Field Mapping dialog (Note the missing Source Field names)

            Import Field Mapping (dialog).png

             

            The Source Fields are present and import correctly, they just don't show in the Source Fields column. This makes it very difficult for long or complex queries. Is this the way the dialog should present with Calculated SQL text, or is this a bug, or am I doing something wrong?

             

            Thanks!

            • 3. Re: Source Fields missing when using Calculated SQL text with Import Records
              beverly

              I don't know for sure. I have always seen the fields/columns align.

              Perhaps it's because you are using "Update matching" (which perfectly ok).

               

              for TS (who may have insight on this):

              • what is the ODBC driver being used?

              • what version of SQL?

              • what version and OS of FileMaker?

               

              to debug, I might duplicate the script and change the update to just Add new records to see if there is something.

               

              Calculated SQL is how I always used this type of Import and not the 'SQL Text option', so don't think that's the problem.

              • 4. Re: Source Fields missing when using Calculated SQL text with Import Records
                user19752

                You use $variable in SQL that is not defined when you are making the script.

                 

                Filepath can have 2nd line for that case, but what is the best for this?

                Using dummy value?

                 

                Let ( [

                dummy = "comment out further settings after you set the order of fields" ;

                $var = "example value" ;

                ...

                ] ;

                "SELECT ..." & $var ...

                )

                • 5. Re: Source Fields missing when using Calculated SQL text with Import Records
                  lkingsella

                  Windows:

                  Filemaker Pro Advanced 15.0.3.305 (64 bit)

                  ODBC Driver: MySQL ODBC Connector 5.3.7 (64 bit)

                  Operating System: Windows 7 Ultimate / Service Pack 1 (64 bit)

                   

                   

                  Mac:

                  Filemaker Pro Advanced 15.0.3.305

                  ODBC Driver: Actual ODBC Driver for Open Source Databases 4.0.6

                  Operating System: macOS 10.12.3 (Sierra)

                   

                   

                  MySQL:

                  Server 1: 5.5.47-0ubuntu0.14.04.1

                  Server 2: 5.1.73-1+deb6u1-log

                   

                  All of the possible combinations produce the same problem with missing Source Fields.

                   

                  Beverly, I tried just adding new records, but the Source Fields were still missing...

                  • 6. Re: Source Fields missing when using Calculated SQL text with Import Records
                    PowerSlave

                    It's always been that way with FileMaker since the introduction of calculated text queries via ODBC. Yes it's a pain but there is no other way to do it unfortunately.

                    Set up your mapping with the literal text query, save script, change back to calculated query, save script and you're done.