Import using SQL

[George]

FMP 5.5

I have this SQL import code:

 SELECT `HEADER`.`BAL_TOTAL`, `HEADER`.`CHARGE_AMT`, `HEADER`.`CREDITCARD`, `HEADER`.`CUST_ID`, `HEADER`.`DATE`, `HEADER`.`DUE_TOTAL`, `HEADER`.`EDIT`, `HEADER`.`EXP`, `HEADER`.`INV_NO`, `HEADER`.`LAST_NAME`, `HEADER`.`PAID_AMT`, `HEADER`.`PAID_OUT`, `HEADER`.`ROA`, `HEADER`.`SALES_TIME`, `HEADER`.`SALESMAN`, `HEADER`.`STATUS`, `HEADER`.`TAX_TOTAL`, `HEADER`.`TAXAB_TOT`, `HEADER`.`TAXRATE`, `HEADER`.`TERMS`

FROM `HEADER`

WHERE `HEADER`.`DATE` >=  

I would like to add a global field value of g_lastupdate after the >= in my code. I want to build a script step that sets the g_lastupdate value to the current date when ran; this lets the user know when the last update was performed and whether they feel they need to update the FM file. Also this greatly reduces the import time of 65000+ records. How can I pass this field value variable to the SQL argument?

Thanks

[BobWeaver]

Put this code into a global field called gSQLtemplate:

SELECT `HEADER`.`BAL_TOTAL`, `HEADER`.`CHARGE_AMT`, `HEADER`.`CREDITCARD`, `HEADER`.`CUST_ID`, `HEADER`.`DATE`, `HEADER`.`DUE_TOTAL`, `HEADER`.`EDIT`, `HEADER`.`EXP`, `HEADER`.`INV_NO`, `HEADER`.`LAST_NAME`, `HEADER`.`PAID_AMT`, `HEADER`.`PAID_OUT`, `HEADER`.`ROA`, `HEADER`.`SALES_TIME`, `HEADER`.`SALESMAN`, `HEADER`.`STATUS`, `HEADER`.`TAX_TOTAL`, `HEADER`.`TAXAB_TOT`, `HEADER`.`TAXRATE`, `HEADER`.`TERMS`

FROM `HEADER`

WHERE `HEADER`.`DATE` >= <<gLastUpdate>>

Then make a calculated field cSQLcode with this formula:

Substitute(gSQLtemplate,"<<gLastUpdate>>",gLastUpdate)

Then, in your Execute SQL script step, use field value from cSQLcode.

[George]

Thanks Bob, sorry about the horizontal post.

I eventually did think to use the asterick for my columns like in the following SQL statement

<<<

SELECT *

FROM `HEADER`

WHERE `HEADER`.`DATE` >= 2001-01-12

>>>

problem now is the WHERE does not seem to make a successful find even though the data exists to satisfy the argument. Above is the statement that FM SQL Query builder is creating for the WHERE argument. I have tried the curly brackets around the date but no help.

Last problem is, what should I use on the "import field mapping" screen? Add, Replace or "Update and add remaining"? I suspect add, provided the >= date argument is working properly in my query.

My whole purpose in all of this is to hopefully speed up the import process by getting only the non existing records.

Thanks

[George]

I could not get the SQL to work with the date value so I decided to use another field to choose my "new" records to import. What I have is a Calc field SQLquery("SELECT * FROM `HEADER` WHERE `HEADER`.`INV_NO` >=" & lastupdate) and a Global field (lastupdate,Text). I then created a script that first sorts by Inv_no then goes to the last record and sets field(lastupdate, "'"&Inv_no&"'") then executes the execute sql by using field value of SQLquery. The script runs and goes through the DSN prompts but does not add the records. To test my sql statement, I created a text field and created a script to set this fields value to the calc field SQLquery. I can then copy this value and begin to do a manual ODBC import and paste this value in and get a sucessfull SQL result and the correct records are added to FM. What could I check as to why the script can't pull in these records?

[BobWeaver]

As you say, if the date thing works properly then you would use the add option.

As for getting the rest of the SQL to work, I have no experience with SQL syntax, but someone else reading this thread might be able to help.

[George]

I did get the date argument to work with the following:

-----------

"SELECT * FROM HEADER WHERE `HEADER`.`DATE`

>= " & "#" &Year(lastupdate)& "-" & Month(lastupdate)& "-" & Day(lastupdate)& "#"

----------