Author Topic: Matching current FMP & QB Customer info  (Read 2046 times)

rpaulh

  • Member
  • *
  • Posts: 22
Matching current FMP & QB Customer info
« on: June 22, 2011, 06:58:21 AM »
Ok, I'm am absolutely a newbie with this. I was able to modify the demo files' scripts to push customers and invoices to QB. Now, how do I match my existing customer info in each file? There is no consistent field for me to use. We have a small amount of customers with many site locations (over 100 each), so cannot use that, and the addresses are not consistent enough. I would go through by hand if I could get the QB Customer ListID number. Any help out there is appreciated?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Matching current FMP & QB Customer info
« Reply #1 on: June 22, 2011, 08:46:52 AM »
There are several ways of identifying a match between a QB Customer and an FMP Customer record, but hand matching is the simplest and probably the most appropriate when your Customer List is small.  The simplest method would involve setting up a utility table with two fields: QBListID and QBFullName. You'd populate it by making a query request to QB for all customers and then create records and parse the returned elements into their respective fields. I use the plug-in's "Z" functions almost exclusively, but I think you'd set up your request using the regular functions like this:

PCQB_RqNew( "CustomerQuery" ; "<IncludeRetElement>ListID</IncludeRetElement><IncludeRetElement>FullName</IncludeRetElement>" )

That will return a list of all Customers with just the relevant two elements: the QB List ID, and the QB Full Name. Walk the returned data and create a record for each Customer, populating the two fields with the respective data elements. Once this is done, you can create a Value List in FMP with the QBListID as the first value while showing and sorting by the second value. Attach the value list to the QBListID field in your real Customer table, and populate away.

HTH!

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

rpaulh

  • Member
  • *
  • Posts: 22
Re: Matching current FMP & QB Customer info
« Reply #2 on: June 22, 2011, 02:01:42 PM »
Thanks Geoffrey, had already so of headed another direction. That was what I was trying to convey is that none of the QB fields matches that FMP data. Makes it very hard. Think I figured out a way to write some calcs to accomplish most of it.