help with quickbooks integration

[lmarini]

I�ve created a passive link table in A5 v9 using a template In Qbuilduilder which utilizes felds in existing quickbooks reports. It was working very nicely until I upgraded to a more recent patch at which time I get an error that the syntax is incorrect.

The syntax is as follows:

sp_report PayrollItemDetail show Amount, Blank, Date, Name, NameAccountNumber, PayrollItem, SourceName, WageBase Parameters DateFrom = {d'2008-01-01'}

The error message is as follows:
Error parsing sql statement��� line 1.1 expecting �select�, found �sp_report�

It works in qbuildbuilder just fine. It would be very time consuming to find which tables these fields are derived from.

Any suggestions would be appreciated.

[Al Buchholz]

Re: help with quickbooks integration

The error message is as follows:
Error parsing sql statement��� line 1.1 expecting �select�, found �sp_report�

Lou

Make sure that the choice for type of SQL statement type is stored procedure.

[lmarini]

Re: help with quickbooks integration

Thank you for the help. Being very new to sql statements, I am unable to filter a specfic date when I refresh the data. The "date from" filter doesn't appear to work. If I add an date argument, it doesn't appear to work either. Any suggestions.

[Al Buchholz]

Re: help with quickbooks integration

The syntax is as follows:

sp_report PayrollItemDetail show Amount, Blank, Date, Name, NameAccountNumber, PayrollItem, SourceName, WageBase Parameters DateFrom = {d'2008-01-01'}

Thank you for the help. Being very new to sql statements, I am unable to filter a specfic date when I refresh the data. The "date from" filter doesn't appear to work. If I add an date argument, it doesn't appear to work either. Any suggestions.

Lou

You need to have the date format look like your first example. So a simple date argument won' work. I've used an xbasic dialog to prompt for a date and then format another variable's value into {d'CCYY-MM-DD'} and then use that as a character argument.

Hope That Helps.

[lmarini]

Re: help with quickbooks integration

I fabricated the following stored procedure. I created an x-dialog date box as you described and a character variable based on that date. I placed it in the argument section but it does not filter. Quickdate2 is the character variable for date variable. Please help.

[Al Buchholz]

Re: help with quickbooks integration

Lou

My fault. I forgot that the sp_report procedure doesn't allow a select statement to have a where clause. The where clause is where the filtering is done.

I've gotten around that by using the DAO import to create the passive link table and built my own select statement with the argument already resolved.

Something like this is the important part....

sql_statement=<<%sql%
sp_report PayrollItemDetail show Amount, Blank, Date, Name, NameAccountNumber, PayrollItem, SourceName, WageBase Parameters DateFrom = :argDateFrom
%sql%

dim args as sql::arguments
args.add("argDateFrom",vcDateFrom)   ' replace your variable in the 2nd position with the date in the format {d'CCYY-MM-DD'}

Final_SQL_Statement=replace_arguments_in_string(sql_statement,args)

..... Use this code along with the xbasic code that DAO import provides.