Author Topic: Pulling a Customer Detail Transaction report  (Read 2556 times)

AdvancedFileMaker

  • Member
  • *
  • Posts: 3
Pulling a Customer Detail Transaction report
« on: October 20, 2011, 11:53:11 AM »
I'm trying to pull a Customer detail transaction report using the following script, I'm trying to bring the datainto FileMaker to make the statement more presentable to the customer. I have a script that makes multiple calls to the QB file to get all the transactions but it is having to make multiple calls and it's getting a large number of transactions for a last quarter statement.

Here is the script and below is the error I'm getting...

Perform Script[“Register Plug-in”]
#Pull Customer Details Report
Set Variable [$$QBListID; Value:Employee::_QBListID]
#Query Customer in QB
Set Field [Employee::_QBError; PCQB_BeginSession( Employee_Sys::QuickBooks_FileName ; "" )]
Set Field [Employee::_QBError; PCQB_RqNew( "CustomDetailReportQuery"  )]
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "CustomDetailReportType" ; "CustomTxnDetail"  )]
Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "DisplayReport" ; 1 )]
Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "ReportEntityFilter::ListID" ; Employee::_QBListID )]
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "ReportItemFilter::ItemTypeFilter" ; "AllExceptFixedAsset" )]
Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "ReportTxnTypeFilter::TxnTypeFilter" ; "All" )]
Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "SummarizeRowsBy" ; "Customer" )]
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Account, Amount, Credit, Date, Debit,  Item, Memo, OriginalAmount, PaidAmount, RefNumber, TxnID, TxnNumber, TxnType, UnitPrice" )]
Set Field [Employee::_QBError; PCQB_RqExecute] Set Field [Employee::_QBError; PCQB_SGetStatus]



ERROR
CODE: -1000
SEVERITY: failed validation
MESSAGE: Error at IncludeColumn
Reason:   'Account, Amount, Credit, Date, Debit,  Item, Memo, OriginalAmount, PaidAmount, RefNumber, TxnID, TxnNumber, TxnType, UnitPrice' violates enumeration constraint of 'Account Aging Amount AmountDifference AverageCost BilledDate BillingStatus CalculatedAmount Class ClearedStatus CostPrice Cr...'.
The element 'IncludeColumn' with value 'Account, Amount, Credit, Date, Debit,  Item, Memo, OriginalAmount, PaidAmount, RefNumber, TxnID, TxnNumber, TxnType, UnitPrce' failed to parse.

Field Value:   Account, Amount, Credit, Date, Debit,  Item, Memo, OriginalAmount, PaidAmount, RefNumber, TxnID, TxnNumber, TxnType, UnitPrice

Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: Pulling a Customer Detail Transaction report
« Reply #1 on: October 20, 2011, 03:24:12 PM »
Try using multiple PCQB_RqAddFieldWithValue( "IncludeColumn"; .... ) calls.  Place each column name in each of the calls.  for example

...
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Account" ) ]
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Amount" ) ]
Set Field[Employee::_QBError; PCQB_RqAddFieldWithValue( "IncludeColumn" ; ....
...


Chris

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling a Customer Detail Transaction report
« Reply #2 on: October 21, 2011, 08:05:08 AM »
Not sure what happened to your original post and my reply to it earlier this week, but additional problems include...

Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "DisplayReport" ; 1 )]

...which should have a value of either "true" or "false" (not 1 or 0), and this line...

Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "ReportEntityFilter::ListID" ; Employee::_QBListID )]

...should come after this line (not included in the snippet you posted)...

Set Field [Employee::_QBError; PCQB_RqAddFieldWithValue( "ReportEntityFilter::EntityTypeFilter" ; "Employee" )]


HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852
« Last Edit: October 21, 2011, 10:26:32 AM by Geoffrey Gerhard »

AdvancedFileMaker

  • Member
  • *
  • Posts: 3
Re: Pulling a Customer Detail Transaction report
« Reply #3 on: October 21, 2011, 09:47:34 AM »
This is great and I'm not getting errors anymore - now just working through getting the date range working and bringing the data into my FileMaker report table.  

Awesome advice and great help...

Bob Harrington
Advanced FileMaker
435-657-1080
« Last Edit: October 21, 2011, 09:52:43 AM by AdvancedFileMaker »

AdvancedFileMaker

  • Member
  • *
  • Posts: 3
Re: Pulling a Customer Detail Transaction report
« Reply #4 on: October 21, 2011, 12:24:00 PM »
OK this is not working to pull the data from the report to the FileMaker

Can you give me some ideas...


Set Error Capture [On]
Allow User Abort [On]
#Create Report Records in FileMaker
Set Variable [$$Result; Value:PCQB_RsOpenFirstRecord]
Set Variable [$Count; Value:0]
Loop
Exit Loop If[$$Result = "End" or $$Result = "?" or  $$Result = "!!ERROR!!"]
Go to Layout [“Employee Statment Entry” (Employee)]
Go to Field [Employee_Invoice_all::Date]
Go to Portal Row [Select; Last]
Set Field [Employee_Invoice_all::TransNum; PCQB_RsGetFirstFieldValue("RefNumber")]
Set Field [Employee_Invoice_all::RefNum; PCQB_RsGetFirstFieldValue("TxnID")]
Set Field [Employee_Invoice_all::Date; PCQB_RsGetFirstFieldValue( "Date" )]
Set Field [Employee_Invoice_all::Credit; PCQB_RsGetFirstFieldValue( "Credit" )]
Set Field [Employee_Invoice_all::Paid; PCQB_RsGetFirstFieldValue( "Debit" )]
Set Field [Employee_Invoice_all::Memo; PCQB_RsGetFirstFieldValue( "Memo" )]
Set Field [Employee_Invoice_all::TransType; PCQB_RsGetFirstFieldValue( "TxnType" )]
Set Variable [$$Result; Value:PCQB_RsCloseRelatedRecord]
Set Variable [$$Result; Value:PCQB_RsOpenNextRecord]
Set Variable[$Count; Value:$Count + 1]
End Loop
Go to Layout [original layout]
#End Session


Bob Harrington
435-657-1080
Advanced FileMaker
« Last Edit: October 24, 2011, 06:39:05 AM by AdvancedFileMaker »

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling a Customer Detail Transaction report
« Reply #5 on: October 24, 2011, 08:02:38 AM »
I don't think this is supported in the current version of the plug-in. The "FM Books Connector Functions Guide" pdf lists two functions that you'd use for this on page 11, but both are identified as "**Still under development**".

It's possible to extract the values using XPath, but the syntax can be difficult to master and requires a good understanding of XML. I've provided consulting to several FileMaker Developers who decided it was a better use of their resources to outsource the extraction of report data.

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852