siroos12

If the list is stored in a field of the same table, it means that it stores the list on each and every record, therefore performing a find would results showing all records.

Hi Wickerman, Is re-login script set to run script with full access privileges? If that is the case, "Get(CurrentPrivilegeSetName)" returns [Full Access]. That is why it does not set the custom menu. I'd suggest to set a variable for "Get(CurrentPrivilegeSetName)" and see what it returns using data viewer and script debugger.

Awesome advise.will apply that to my script. And yes, the TxnID was empty. my bad!

Hi Geoffrey, I am trying to do an InvoiceQueryRq for the TxnID and IncludeLinkedTxns = true and it freezes and then FileMaker crashes. any ideas?

Chris, I have another situation. I have an invoice in FM which is pushed to QB successfully. After that, a payment has been made for that invoice and it is recorded in FM but not pushed to QB yet. A user made a mistake and manually added the payment to QB. The next user comes in and tries to push the payment to QB while it is already added to QB manually. We need to query that payment before pushing it to QB to make sure that it does not exists in QB. If the payment was added to QB using the plugin, we would have a Reference number and TxnID for it so, we could easily search based on those. But, as it is manually added to QB, we are stocked. Any Idea of how we can find that payment in QB without having a Reference number or TxnID? Can we use a combination of invoice reference number and the payment amount? If yes, how do we do that?

Hi all, I have this clients that doesn't exists in QB but It keeps returning below error when I try to add it to QB: CODE: 3100 SEVERITY: Error MESSAGE: The name "Monica Talas" of the list element is already in use. Can any one suggest what am I doing wrong here? So, "Monica Talas" does not exists in QB as a customer, We have a vendor called "Monica Talas AB". When ,my script run a query for "Monica Talas", it cannot find it. Then the script attempts to add it to QB using "PCQB_RqNew("CustomerAdd" ; "" )". After setting up customer's variables and run "PCQB_RqExecute", I receive above error. Here is how I structured mt script: #Query customers in QB (Request) Set Variable [ $$Result; Value:PCQB_RqNew("CustomerQuery" ; "" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "NameFilter::MatchCriterion" ; "StartsWith") ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "NameFilter::Name" ; globals_CUSTOMERS::CustomerName)] Set Variable [ $$Result; Value: PCQB_RqExecute ] Set Variable [ $$Status; Value: PCQB_SGetStatus ] If [ $$Result = 0 ] #Customer found, set the ListID and EditSequesnce Set Variable [ $$Result; Value:PCQB_RsOpenFirstRecord ] Set Field [ globals_CUSTOMERS::_ID_QBCustomerListID; PCQB_RsGetFirstFieldValue("ListID") ] Set Field [ globals_CUSTOMERS::QBEditSequence; PCQB_RsGetFirstFieldValue("EditSequence") ] Set Variable [ $$Result; Value:PCQB_RqNew("CustomerMod" ; "" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "ListID" ; globals_CUSTOMERS::_ID_QBCustomerListID) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; globals_CUSTOMERS::QBEditSequence) ] Else #Customer does not exists on QB so, push it to QB Set Variable [ $$Result; Value:PCQB_RqNew("CustomerAdd" ; "" ) ] End If Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "Name" ; globals_CUSTOMERS::CustomerName) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IsActive" ; 1) ] #Legal company name for billing Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "CompanyName" ; globals_CUSTOMERS::CustomerName) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "FirstName" ; customers_CONTACTS||PrimaryContact::FirstName)] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "LastName" ; customers_CONTACTS||PrimaryContact::LastName)] #Execute Set Variable [ $$Result; Value: PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] If [ $$result = 0 ] Set Variable [ $success; Value:True ] Else Set Variable [ $message; Value:"Customer was not synced to QuickBooks: ¶¶Status: " & $$Status & " Customer: " & globals_CUSTOMERS::CustomerName & " Order: " & ORDERS::OrderNumber & "¶¶" & GetErrorEnvironment ] Go to Layout [ original layout ] Exit Script [ Result: #SetParam("Success" ; $success) & #SetParam("Message" ;$message) ] End If

Awesome, Thanks a million.

Thanks Chris, How and when I can retrieve TxnLineID? Is it generated after the PCQB_RqExecute command or before it? And how do I get it for each line item? On the other hand, If I specify a TxnLineID of "-1" for each invoice line item, What happens if I have more line items on modified FM invoice than what already exists on QB? Sorry if it is a silly question but this is my first time working with QB and FM Connector.

Hi, Another issue for me 😀. I wonder if anyone can help: Below is the script I use to add an invoice to QB, It adds the invoice if it is not already in QB with no problems. Now after modifying that invoice in FM,I need to set the mode to MOD and push modified data to QB (Either data of invoice itself or its line items). But, My problem is that I do not have any "EditSequence" for line items. How do I capture "EditSequence" of each line item and store it so I can use them when modifying an invoice line item? #Check if order exists on QB Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; ORDERS::OrderNumber) ] Set Variable [ $$Result; Value:PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] If [ $$Result = 0 ] #Order exists on QB Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceMod" ; "" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; ORDERS::_ID_QBTxnID) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; ORDERS::QBEditSequence) ] Else #Order does not exists on QB so, push it to QB Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceAdd" ; "") ] End If Set Variable [ $$Result; Value:"List ID result: " & PCQB_RqAddFieldWithValue( "CustomerRef::ListID" ; CUSTOMERS:: _ID_QBCustomerListID) ] Set Variable [ $$Result; Value:$$Result & ¶ & "TxnDate Result: " & PCQB_RqAddFieldWithValue( "TxnDate"; Get(CurrentDate) ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "RefNumber Result: " & PCQB_RqAddFieldWithValue( "RefNumber" ; ORDERS:: OrderNumber ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "PONumber Result: " & PCQB_RqAddFieldWithValue( "PONumber"; ORDERS:: PONumber ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "DueDate Result: " & PCQB_RqAddFieldWithValue( "DueDate"; Get(CurrentDate) + 30) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "SalesRepRef::FullName"; Orders_USERS||_ID_USER::Initials ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "SalesTaxRef Result: " & PCQB_RqAddFieldWithValue( "ItemSalesTaxRef::FullName"; " Sales Tax" )] #Prep Line Items (Request) Set Variable [ $orderId; Value:ORDERS::_ID_ORDER ] Go to Layout [ “OrderItemBlank” (ORDERITEMS) ] Enter Find Mode [ ] Set Field [ ORDERITEMS::IDOrder; $orderId ] Perform Find [ ] Go to Record/Request/Page [ First ] Loop Set Variable [ $$Result; Value:"InvoiceLineAdd Result: " & PCQB_RqAddRelatedRecord( "InvoiceLineAdd" ; "" ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "ItemName Result: " & PCQB_RqAddFieldWithValue( "ItemRef::FullName" ; ORDERITEMS::Category) ] Set Variable [ $$Result; Value:$$Result & ¶ & "Desc Result: " & PCQB_RqAddFieldWithValue( "Desc" ; ORDERITEMS:: Description ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "Qty Result: " & PCQB_RqAddFieldWithValue( "Quantity" ; ORDERITEMS:: Quantity ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "Rate Result: " & PCQB_RqAddFieldWithValue( "Rate" ; ORDERITEMS::Price ) ] #The tax code for this particular order line Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "SalesTaxCodeRef::FullName" ; Case ( not IsEmpty(ORDERITEMS::TaxCode) ; ORDERITEMS::TaxCode ; not IsEmpty (orderItems_orders_CUSTOMERS::SalesTaxCode) ; orderItems_orders_CUSTOMERS::SalesTaxCode ; not IsEmpty(orderItems_CATALOGS::SalesTaxCode) ;orderItems_CATALOGS::SalesTaxCode ; "TAX" ) ) ] Set Variable [ $$Result; Value:$$Result & ¶ & "Close Result: " & PCQB_RqCloseRelatedRecord ] Go to Record/Request/Page [ Next; Exit after last ] End Loop #Execute Set Variable [ $$Result; Value: PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ]

Chris, Thanks for quick response. Indeed,I do capture EditSequence properly and use it when editing a record. My problem was setting "AppliedToTxnAdd::TxnID" and "AppliedToTxnAdd::PaymentAmount" while modifying an existing record. Those are not supported propertied for MOD mode. After modifying the script as below, it works like a charm: #Check if payment exists on QB Set Variable [ $$Result; Value:PCQB_RqNew( "ReceivePaymentQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; PAYMENTS::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] If [ $$Result = 0 ] #Payment exists on QB Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentMod" ; "") ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; PAYMENTS::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; PAYMENTS::QBEditSequence)] Else #Payment does not exists on QB so, push it to QB Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentAdd" ; "") ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::TxnID"; payments_ORDERS:: _ID_QBTxnID) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::PaymentAmount"; PAYMENTS::Amount)] End If Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "CustomerRef::ListID"; CUSTOMERS:: _ID_QBCustomerListID ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnDate"; PAYMENTS::Date )] If [ PAYMENTS::PaymentType = "Check"] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::CheckNumber)] Else If [ PAYMENTS::PaymentType = "Credit" ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::AuthNumber)] End If Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TotalAmount" ; PAYMENTS::Amount )] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "PaymentMethodRef::FullName"; If(PAYMENTS:: PaymentType = "Credit" ; PAYMENTS::CardType ; PAYMENTS::PaymentType))] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "DepositToAccountRef::FullName"; Case ( PAYMENTS:: PaymentType = "Cash" ; "Cash in register" ; PAYMENTS::PaymentType = "Check" ; "Checks for deposit" ; PAYMENTS::PaymentType = "Credit" ; "CC Deposits" ) )] #Execute Set Variable [ $$Result; Value: PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ]

Hi all, I am trying to modify an existing payment on QB from FM with no luck. Here is part of my script which sets the mode to ADD or MOD based on query ran on payments on QB. So the script would check if the payment exists on QB, then sets the mode to MOD if not sets it to ADD: #Check if payment exists on QB Set Variable [ $$Result; Value:PCQB_RqNew( "ReceivePaymentQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; PAYMENTS::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] If [ $$Result = 0 ] #Payment exists on QB Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentMod" ; "") ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; PAYMENTS::_ID_QBTxnID)] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; PAYMENTS::QBEditSequence)] Else #Payment does not exists on QB so, push it to QB Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentAdd" ; "") ] End If Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "CustomerRef::ListID"; CUSTOMERS:: _ID_QBCustomerListID ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnDate"; PAYMENTS::Date )] If [ PAYMENTS::PaymentType = "Check"] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::CheckNumber)] Else If [ PAYMENTS::PaymentType = "Credit" ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::AuthNumber)] End If Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TotalAmount" ; PAYMENTS::Amount )] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "PaymentMethodRef::FullName"; If(PAYMENTS:: PaymentType = "Credit" ; PAYMENTS::CardType ; PAYMENTS::PaymentType))] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "DepositToAccountRef::FullName"; Case ( PAYMENTS:: PaymentType = "Cash" ; "Cash in register" ; PAYMENTS::PaymentType = "Check" ; "Checks for deposit" ; PAYMENTS::PaymentType = "Credit" ; "CC Deposits" ) )] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::TxnID"; payments_ORDERS:: _ID_QBTxnID) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::PaymentAmount"; PAYMENTS::Amount)] #Execute Set Variable [ $$Result; Value: PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] This script adds the payment successfully into QB on the first attempt when there is no such a payment on QB. But if users modify the payment record and try to push the modified payment to QB, they get no error while that Payment on QB remains the same as its initial state. Any advise would be appreciated.

Hi all, I am trying to modify an existing invoice on QB from FM with no luck. Here is part of my script which sets the mode to ADD or MOD based on query ran on invoices on QB. So the script would search for an invoice on QB and if invoice exists, it sets the mode to MOD and if not it sets the mode to ADD. #Check if order exists on QB Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; ORDERS::OrderNumber) ] Set Variable [ $$Result; Value:PCQB_RqExecute ] Set Variable [ $$Status; Value:PCQB_SGetStatus ] If [ $$Result = 0 ] #Order exists on QB Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceMod" ; "" ) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; ORDERS::_ID_QBTxnID) ] Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; ORDERS::QBEditSequence) ] Else #Order does not exists on QB so, push it to QB Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceAdd" ; "") ] End If And then the rest process would follow but each time I set the mode to MOD and execute the plugin, it adds a new invoice instead of editing the existing one. Any advise would be appreciated.

Awesome, Thank you!

Hi, I have the customer ListID in FM and wanna make sure that this customer does exist in QB. This is my approach: PCQB_ZQuery("CustomerQueryRs/*/ListID";"Customer";"<ListID>" & $QBCustomerListID & "</ListID>";"") Then I use "PatternCount" to search for the target ID. Can anyone suggest any neater approach?

You could import them into "accounts payable" table and use "replace field content" or loop through imported records and set their Foreign-keys if needed. Or, you can make a relationship between the 2 tables and allow "recurring expenses" table to create records in "accounts payable" table. Then loop through records of "recurring expenses" table and create records in "accounts payable" table using a portal. You can set the fields values in the loop script using "set field" script step, or you may use "lookup" or "auto enter calc" on "accounts payable" table fields to pull values from "recurring expenses" table.