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
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:
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)
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?
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.
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 ...
Filemaker Pro Advanced 18.104.22.1685 (64 bit)
ODBC Driver: MySQL ODBC Connector 5.3.7 (64 bit)
Operating System: Windows 7 Ultimate / Service Pack 1 (64 bit)
Filemaker Pro Advanced 22.214.171.1245
ODBC Driver: Actual ODBC Driver for Open Source Databases 4.0.6
Operating System: macOS 10.12.3 (Sierra)
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...
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.