Author Topic: How to retrieve Customer List ID  (Read 2708 times)

rachrapid

  • Member
  • *
  • Posts: 2
How to retrieve Customer List ID
« on: July 16, 2014, 01:14:37 AM »
I've been having some problems with "Add Customer" under the "Push" tab. The error message is: "CODE: 3100
SEVERITY: Error MESSAGE: The name "xxx" of the list element is already in use."

When I try to "Add Invoice" the error message is: "You cannot create an invoice unless you have a a List ID for the customer listed above.  Add the Customer by pressing the 'Add Customer' button above."

How do I retrieve the List ID so that I can add invoice into Quickbooks?
Can anyone help with the sample code? I have  tried a lot of methods but they weren't successful.  :-\

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: How to retrieve Customer List ID
« Reply #1 on: July 16, 2014, 05:08:46 AM »
That message is saying that there's a record in QB with a CustomerName value that matches the one you're sending. To retrieve the ListID of that record, create a CustomerQuery Request. If the QB Company file does not have a Customer:Job hierarchy, pass the "xxx" value as the value of the "FullName" element. If the QB Company file has Jobs, you may need to pass it differently. In that case, I'd include the element "NameFilter::MatchCriterion" with a value of "Contains" and the element "NameFilter::Name" with a value of Contains.

Synching an existing FMP solution with an existing QB Company file can be difficult because of the differences in the way the two solutions treat hierarchies and the likelihood that QB CustomerName values will differ from the values in the corresponding FMP record. It's often best to create a QBCustomerValueList table that gets populated with the CustomerName, CompanyName, and ListID values of all QB Customer records. You can then make one or more Value Lists that can be matched to existing Customer table records.

HTH!

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

rachrapid

  • Member
  • *
  • Posts: 2
Re: How to retrieve Customer List ID
« Reply #2 on: July 17, 2014, 12:11:26 AM »
Thanks Geoffrey I understand the steps but I need help putting it into code (script). Could you give me a sample code or provide more information on how to create the CustomerQuery Request? :)

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: How to retrieve Customer List ID
« Reply #3 on: July 17, 2014, 07:37:49 AM »
I don't have sample code that would be useful--I've built a FileMaker/QuickBooks integration framework that is heavily abstracted and makes integration process very efficient for me, but which has a very steep learning curve for someone who hasn't been doing this kind of work for years.

For information on creating a CustomerQuery Request, I recommend Intuit's Onscreen Reference (OSR) found here...

https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html

...and shows what Elements can/must be included, and the sequence required of a well-formed Request. If you get stuck, questions on this forum get answered fairly quickly. And if you decide it's easier to outsource, I've been doing FMP/QB integrations for 10 plus years and would be happy to help.

HTH!

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

WillzShire

  • Member
  • *
  • Posts: 40
    • Design Data Concepts, LLC
Re: How to retrieve Customer List ID
« Reply #4 on: July 18, 2014, 09:50:10 AM »
Geoffrey, This is very helpful (thanks rachrapid for the post)
I have FMP solution and existing QB file that I need to sync up so I can push
new invoices and new purchase invoices to QB. Geting the ListID embedded in FMP seems required.
And your right about the differences - names are not all exactly the same between
FMP and QB. If I can extract QB list that you're suggesting then I can
change FMP.

scstock

  • Member
  • *
  • Posts: 10
Re: How to retrieve Customer List ID
« Reply #5 on: July 24, 2014, 03:38:02 AM »
I had to create such a Script, as all my customers already existed in QB. This script will get the List ID for a given Customer name, here are the key steps:

#Request List ID
Set Variable [ $$Result; Value:PCQB_RqNew( "CustomerQuery" ; "" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "FullName" ; CustomerTable::CustomerName ; "" ) ]
Set Variable [ $$Result; Value:PCQB_RsOpenFirstRecord ]
#Open Session
Set Variable [ $$CALL; Value:PCQB_BeginSession("";"") ]
#Execute
Set Variable [ $$Result; Value:PCQB_RqExecute ]
#Close Session
Set Variable [ $$Result2; Value:PCQB_EndSession ]
Set Field [ CustomerTable::List ID; PCQB_RsGetFirstFieldValue( "ListID" ) ]
Commit Records/Requests [ Skip data entry validation; No dialog ]

Hope this helps!

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: How to retrieve Customer List ID
« Reply #6 on: July 24, 2014, 07:20:42 AM »
That works as long as the value in CustomerTable::CustomerName exactly matches the value in QB's FullName field. It's worth noting that the value in QB's FullName field will include additional information when the Customer record is a child of another QB record, as is often the case when CustomerJobs are in use. That means that a Query may fail to return a response even when the FMP value is an exact match for a QB CustomerName value.

One other note: the "Set Variable [ $$Result; Value:PCQB_RsOpenFirstRecord ]" step can be deleted from your script--it adds nothing to the process.

HTH!

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