Plug-ins > FM Books Connector

Finding Employee Paychecks

(1/1)

mw777rcc:
The following script is not pulling any check records.  I create a list of employees and cycle through them collecting the ListID into $ListID and then use that variable to find related checks.  I keep getting an error saying that no records match the criteria.  Any ideas?

Set Error Capture [ On ]
Allow User Abort [ Off ]
Freeze Window
#Find existing Check records for and delete
Go to Layout [ “CHK__Checks” (CHK__Checks) ]
Show All Records
Delete All Records
    [ No dialog ]
#-
# Get full list of Providers through which to cycle.
Perform Script [ “14101 QuickBooks: Get Provider List” ]
#-
#Loop through all Provider records and get their checks
Go to Record/Request/Page
    [ First ]
Loop
    Set Variable [ $ListID; Value:PRVQ__Providers_QuickBooks::ListID ]
    Go to Layout [ “CHK__Checks” (CHK__Checks) ]
    #Connect to QB File and Open Session
    Set Variable [ $result; Value:PCQB_EndSession ]
    Set Variable [ $result; Value:PCQB_BeginSession("";"") ]
    #Query Customers in QB (Request)
    Set Variable [ $result; Value:PCQB_RqNew ( "CheckQuery" ; "" ) ]
    #-
    ############# Is the problem here? #################
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue ( "EntityFilter::ListID" ; $ListID ) ]
    #############################################
    #-
    Set Variable [ $result; Value:PCQB_RqExecute ]
    If [ GetAsNumber ( GetValue ( PCQB_SGetStatus ; 1 ) ) <> 1 ]
        If [ GetAsNumber ( GetValue ( PCQB_SGetStatus ; 1 ) ) > 0 ]
            Perform Script [ “14201 Quickbooks: Check for Errors” ]
        End If
        Set Variable [ $result; Value:PCQB_EndSession ]
        #Create Customers in FileMaker (Response)
        Set Variable [ $result; Value:PCQB_RsOpenFirstRecord ]
        Set Variable [ $counter; Value:1 ]
        Loop
            Exit Loop If [ $result = "!!ERROR!!" or $result = "End" or $result = "?"
                // Used to limit found count during testing
                or $counter > 100 ]
            New Record/Request
            Set Field [ CHK__Checks::DateCreated_text; PCQB_RsGetFirstFieldValue("CheckRet::TimeCreated") ]
            Set Field [ CHK__Checks::Payee_ListID; PCQB_RsGetFirstFieldValue("PayeeEntityRef::ListID") ]
            Set Field [ CHK__Checks::Payee_FullName; PCQB_RsGetFirstFieldValue("PayeeEntityRef::FullName") ]
            Set Field [ CHK__Checks::Amount; PCQB_RsGetFirstFieldValue("Amount") ]
            Set Variable [ $result; Value:PCQB_RsOpenNextRecord ]
            Set Variable [ $counter; Value:$counter + 1 ]
        End Loop
    End If
    Go to Layout [ “PRVQ__Providers_QuickBooks” (PRVQ__Providers_QuickBooks) ]
    Go to Record/Request/Page
        [ Next; Exit after last ]
End Loop

Melinda DePalma:
After further phone discussion, it appears that you are trying to query Payroll checks. According to the OSR "BillPaymentChecks, payroll checks, and liability checks are not returned" when performing a CheckQuery. Alternatively with respects to payroll you can perform the following:
- PayrollDetailReportQuery
- PayrollSummaryReportQuery
- PayrollItemNonWageQuery
- PayrollItemWageQuery.

You can query your employee payroll checks by querying payroll report instead.
 

mw777rcc:
Ok, I am closer now but I am not sure how to pull the response because the format is a lot different on a report than on other querys.  I get no errors all the way through but it doesnt return anything on the set field. 

As a secondary note the line that is commented out that sets the report dates uses the date format requested in the OSR CCCC-MM-DD but gets an error that it cannot parse 2009-2007-01 even though it is set to 2009-07-01.

This is what I have so far, any help would be greatly appreciated:


--- Code: ---Set Error Capture [ On ]
Allow User Abort [ Off ]
Freeze Window
#Find existing Check records and delete
Go to Layout [ “CHK__Checks” (CHK__Checks) ]
Show All Records
Delete All Records
    [ No dialog ]
#Connect to QB File and Open Session
Set Variable [ $result; Value:PCQB_BeginSession("";"") ]
#Checks and Sets the QuickBooks Version. This allows us to identify a US or International version of QuickBooks based on the QuickBooks file
that is opened.
Set Variable [ $version; Value:PCQB_GetCurrentFileVersion ]
#Query Payroll Report in QB (Request)
Set Variable [ $result; Value:PCQB_RqNew ( "PayrollDetailReportQuery" ; "" ) ]
If [ GetAsNumber(PCQB_GetCurrentFileVersion) >= 4 ]
    #The area below limits the returned response to FullName, TotalBalance and ListID for better performance.
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue ( "PayrollDetailReportType" ; "PayrollItemDetail" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue ( "ReportDateMacro" ; "ThisQuarter" ) ]
    // Set Variable [ $result; Value:PCQB_RqAddFieldWithValue ( "ReportPeriod::FromReportDate" ; "2009-07-01" ) ]
    // Set Variable [ $result; Value:PCQB_RqAddFieldWithValue ( "ReportPeriod::FromReportDate" ; "2009-12-31" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "ReportDetailLevelFilter" ; "AllExceptSummary" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Amount" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Date" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Memo" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "Name" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "PayrollItem" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "RefNumber" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeColumn" ; "SSNOrTaxID" ) ]
    Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeAccounts" ; "InUse" ) ]
End If
Set Variable [ $result; Value:PCQB_RqExecute ]
Set Variable [ $result; Value:PCQB_EndSession ]
#Create Paychecks in FileMaker (Response)
Set Variable [ $result; Value:PCQB_RsOpenFirstRecord ]
Set Variable [ $counter; Value:1 ]
Loop
    Exit Loop If [ $result = "!!ERROR!!" or $result = "End" or $result = "?"
        // Used to limit found count during testing
        // or $counter > 100 ]
    New Record/Request
    #-
    #### Pretty sure that the problem is in this line ####
    Set Field [ CHK__Checks::Payee_FullName; PCQB_RsGetFirstFieldValue("ReportData::DataRow") ]
    #-
    Set Variable [ $result; Value:PCQB_RsOpenNextRecord ]
    Set Variable [ $counter; Value:$counter + 1 ]
End Loop
--- End code ---

Navigation

[0] Message Index

Go to full version