Author Topic: Pulling custom field data  (Read 5789 times)

kurchy

  • Member
  • *
  • Posts: 17
Pulling custom field data
« on: December 02, 2010, 10:43:07 AM »
Hi, I have a very simple filemaker script setup to simply pull customer data from quickbooks into filemaker and works great.  However in quickbooks on the edit customer screen there is a tab called Additional Info, and in that tab there is a spot were you can define a few custom fields.  Anyone know how i might pull that info?

All other fields i simply do a set field in FM using something like this.  PCQB_RsGetFirstFieldValue("FullName")
However if i simply replace FullName with the name of a custom field it doesn't work.  I know when pulling something like the address you need to prefix it the table name something like this ("BillAddress::Addr1") 

So my question I guess is there just a table name i need to include before my custom filed name to make this work or is it not that simple?
Any help or suggestions would be great!!

Thanks

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #1 on: January 18, 2011, 02:58:10 PM »
I'm still stuck on this.  :(  Anyone have any ideas or suggestions how I might be able to access these custom fields in quickbooks?
Any help would be great.
Thanks again

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #2 on: January 21, 2011, 10:10:55 AM »
Your QB query request must include the OwnerID element with a value of 0 (zero) if you want the query to return the custom fields. Or are you getting the custom field elements (<DataExtRet> nodes) but having trouble extracting the values from one or more of those nodes?

Custom fields are returned as related records, so you'll access them like this:

PCQB_RsOpenFirstRelatedRecord( "DataExtRet" )
PCQB_RsGetFirstFieldValue("DataExtName") // Returns the name of the Custom Field in QB
PCQB_RsGetFirstFieldValue("DataExtValue") // Returns the Custom Field's value
PCQB_RsOpenNextRelatedRecord

Put these in a loop in your FMP script and set your FMP fields when the current DataExtName is relevant to your solution.


HTH!

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

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #3 on: January 25, 2011, 03:33:01 PM »
Hi, thanks for the reply, however I tried what you said but somehow I must not fully understand as I'm still having no luck.  Basically all my current script does is query quickbooks for all active customers. I then loop through the found quickbooks contacts and set each field in filemaker to match, only I'm missing the couple fields that are user defined.  I guess I'm confused as to where to put your steps.  Part of my current loop or does it need to be separate?  Or am I just missing something and going about the whole thing wrong?  Being a noob is so fun :)  Thanks again!

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #4 on: January 26, 2011, 03:05:36 PM »
Just realized that page 14 of the FMBooksConnector_Developers_Guide.pdf provides a more complete explanation than the one I provided earlier. It says:

==============
Retrieving the contents of a custom field requires querying for the parent object and including the OwnerID field in the query, which is normally one of the last fields to be added to the request. Obtaining the DataExt values in the Response to a query objects (contacts, invoices,etc...) requires that the request contain the following PCQB_RqAddFieldWithValue( "OwnerID" ; "0" ).

The above function adds the OwnerID field to the request, and populates it with a 0. This causes QuickBooks to return the public data extensions (custom fields) with the response. (Advanced users can cause QuickBooks to return private data extensions by passing the GUID instead of '0, but this is only for advanced users). When QuickBooks returns the DataExt (custom fields) in the response, the plug-in user can access the information in the data extension. The following script demonstrates accessing the custom fields in a response:

If [ 0 = PCQB_RsOpenFirstRelatedRecord( "DataExtRet" ) ]
Loop
#the name of the custom field
Set Field[ N_Field ; PCQB_RsGetFirstFieldValue( "DateExtName" ) ]
#the value of the custom field
Set Field[ D_Field ; PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]
#get next custom field/exit if there are no more
Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ]
End Loop Set Field[ SomeField ; PCQB_RsCloseRelatedRecord ]
End If ...

Since custom fields are more advanced and can be quite complex, we are available for hire to assist with this development.
==============

HTH!

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

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #5 on: January 27, 2011, 08:24:14 AM »
Unfortunately that's what I started with, and while it did return data it seems to only give me 1 record and it doesn't even match up.  Meaning the custom field data it gave me was from a diff customer than the one it populated in filemaker.  Also only 1 record in filemaker had custom field data, and i did create the loop they describe. :( 
I guess its a lot more complex than I though and might have to pay for the answer.  Just seems like they would work like any other related field though.  Like pulling address data related to a customer.  Doing that is pretty straight forward and easy just let it know the table the related data is in.  So figured getting related custom field data would be pretty much the same thing but it seems not.

Thanks again for trying to help.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #6 on: January 28, 2011, 02:22:08 PM »
It might be useful to look at the block of xml you're getting in response to your request. After executing the PCQB_RqExecute function, try setting the PCQB_SGetXML( "Response" ; "" ) to a global field or--if you're using FMP Advanced--a $$variable and paste the xml code here. Processing DataExt values can be a challenge, and I suspect that looking at the entire block that's returned will provide the necessary clues to solve the problem.

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

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #7 on: March 25, 2011, 02:31:27 PM »
Coming back to this thread because I finally found the XPATH syntax that makes extracting the value from a specific QuickBooks Custom Field easy as long as you know its name. XPATH makes it way simpler than looping through all DataExt elements to find the one with the Custom Field name you're looking for and then using PCQB_RsGetFirstFieldValue ( "DataExtValue" ).

Suppose there are several custom fields defined in the Customer List in QuickBooks, one of which is called QBCustomFieldName. If all you need is the data in QBCustomFieldName for a particular Customer, you'd construct your query and follow the PCQB_RqExecute step with this...

PCQB_ZParseXML ( PCQB_SGetXML( "Response" ; "" ) ; "" ; ".//CustomerRet/DataExtRet[DataExtName=\"QBCustomFieldName\"]/DataExtValue/text()" )

...to get the custom field's value. Remember to call PCQB_RqAddFieldWithValue( "OwnerID" ; "0" ) as the last function before calling the PCQB_RqExecute function.

Hope somebody finds it useful.

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852
« Last Edit: March 29, 2011, 07:01:10 AM by Geoffrey Gerhard »

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #8 on: May 31, 2011, 12:23:13 PM »
Hey Geoffrey, thanks for the update.  I got pulled away from that project and am just now getting back to that.  I just tried your XPATH method but seem to be missing something.
My current FM script does a query and then I have a loop with a bunch of PCQB_RsGetFirstFieldValue statements setting each FM filed with matching quickbooks data.
I added your statement into my loop and I do get the custom field data into FM but its pulling the custom field data from the first customer and putting that same info in for each customer.  Sorry to be a pain but do you have any idea what I might be missing?

Thanks

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #9 on: May 31, 2011, 01:34:55 PM »
The XPATH I provided is targeting the first repetition--it's the default when no repetition is identified. If you don't already have a "Customer Node Counter" value, you'll need one that increments before processing each Customer. Instantiate and increment a "$custNode" variable, and then reference it in the XPATH as follows...


PCQB_ZParseXML ( PCQB_SGetXML( "Response" ; "" ) ; "" ; ".//CustomerRet[" & $custNode & "]/DataExtRet[DataExtName=\"QBCustomFieldName\"]/DataExtValue/text()" )

...so that you're targeting the right "CustomerRet" node.

HTH!

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

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #10 on: May 31, 2011, 02:45:47 PM »
I think I follow you, however.  Do i need to set that variable to something specific?  I made a $custNode variable and set it to 1 incrementing it by 1 each time but that seems to give me know results.  I would think 1 would represent repetition 1 and so on but that don't seem to be the case.  Thanks again.

kurchy

  • Member
  • *
  • Posts: 17
Re: Pulling custom field data
« Reply #11 on: May 31, 2011, 03:15:00 PM »
Opps sorry my bad I forgot to replace "QBCustomFieldName" with my field name :)

Looks like its finally working!!!  Thanks a million, I've been pulling my hair out on this forever.  It was so easy to pull normal fields I couldn't believe there were so many hoops to jump though to getting custom fields.  However you just made custom fields almost as easy as normal ones with this.  Perfect!!
This solution should be made into a sticky as I sure there will be many others in the future looking for a simple way to pull custom field data.

Thanks again, If you ever make it out to IL I owe you a drink. :)

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Pulling custom field data
« Reply #12 on: June 01, 2011, 01:30:36 PM »
Although I'd read several different web sites that explained the uses for XPATH, I had only seen numeric parameters or attribute comparisons used to identify a node. That wasn't useful in this context, and I've been walking the nodes manually until I realized--on March 25--that you could identify a node by matching the value of one of its elements. I posted it here thinking that it wasn't obvious and hoped others might benefit.

Glad you found it useful!

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