Author Topic: Query Item Custom Fields  (Read 417 times)

smcgovern

  • Member
  • *
  • Posts: 5
Query Item Custom Fields
« on: May 25, 2017, 06:21:51 AM »
All,

I'm new to the Quickbooks Connector and I have read the documentation along with the sample scripts and the demo scripts as well.  However, I'm continuing to struggle how to Query the custom fields of an item.  I want to simply provide a lookup screen in filmmaker populated with the inventory item, description and a custom field we call bin.  I've had no issue with the first part of the request (I utilized the scripts available in the demo), but when I try to add the script loop for the DataExtRet portion, my loop errors out.  If someone would be so kind as to show a sample of how they have performed this, I would be extremely appreciative.

Thank you,

Shawn

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Query Item Custom Fields
« Reply #1 on: May 25, 2017, 07:33:54 AM »
Not sure where you're stuck...

Does your Query Request include the OwnerID element with a value of 0? That's needed to return QB's User defined Custom Field values.

Or are you having trouble parsing the values of particular Custom Fields? Each DataExtRet node identifies the Custom Field's name and its value, but I believe only those Custom Fields that contain a value are returned.

Looking at the Query Response might be helpful. You can get it by calling the...

PCQB_SGetXML( "Response" ; "" )

...function after the RqExecute.

HTH!

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

smcgovern

  • Member
  • *
  • Posts: 5
Re: Query Item Custom Fields
« Reply #2 on: May 25, 2017, 11:02:16 AM »
I've copied my script below:

# --------------------------------------------------------------------------------------------------------------------------------------------
# Name: Pull All QB Items
# History: SPM
# Last modified: 5/23/2017
# Purpose: This script pulls the Item Master in QuickBooks.
# Parameters: None
# Notes: None
# --------------------------------------------------------------------------------------------------------------------------------------------
# Setup
Set Error Capture [ On ]
Allow User Abort [ Off ]

Freeze Window
Set Variable [ $Tab ; Value: Case (  GetLayoutObjectAttribute ( "Setup"; "isFrontTabPanel" ) ; "Setup" ;  GetLayoutObjectAttribute ( "Pull"; "isFrontTabPanel" ) and GetLayoutObjectAttribute ("Invoice"; "isFrontTabPanel"); "Invoice" ;  GetLayoutObjectAttribute ( "Pull"; "isFrontTabPa… ]

# This will ensure that the plug-in has been registered either as a demo or full version before continuing.

# Find existing QB Items records for and delete (this simply cleans up the screen to avoid confusion).
Go to Layout [ “QBItems” (QBItems) ]
Show All Records
Delete All Records [ No dialog ]

# Query Customers in QB (Request)
Set Variable [ $$Result ; Value: <Function Missing>("ItemInventoryQuery" ; ""  ) ]
If [ $$Result ≠ 0 ]
   Halt Script
End If
If [ GetAsNumber(<Function Missing>) >= 4 ]
   # The area below limits the returned response to FullName, TotalBalance and ListID for better performance.
   Set Variable [ $$Result ; Value: <Function Missing>( "IncludeRetElement" ; "ListID" ) ]
   Set Variable [ $$Result ; Value: <Function Missing>( "IncludeRetElement" ; "Name" ) ]
   Set Variable [ $$Result ; Value: <Function Missing>( "IncludeRetElement" ; "SalesDesc" ) ]
   Set Variable [ $$Result ; Value: <Function Missing>( "IncludeRetElement" ; "ManufacturerPartNumber" ) ]
   Set Variable [ $$Result ; Value: <Function Missing>( "IncludeRetElement" ; "IsActive" ) ]
   Set Variable [ $$Result ; Value: <Function Missing>( "OwnerID" ; "0" ) ]


Any help would again be appreciated

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Query Item Custom Fields
« Reply #3 on: May 25, 2017, 01:16:37 PM »
I believe that, because you are including at least one "IncludeRetElement"  filter, you need to add one more "IncludeRetElement" specifying "DataExtRet" as its value.

Try adding the additional line to your Query Request, executing it, and looking at the Response generated by PCQB_SGetXML( "Response" ; "" ).

HTH!

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

smcgovern

  • Member
  • *
  • Posts: 5
Re: Query Item Custom Fields
« Reply #4 on: May 26, 2017, 10:06:16 AM »
I think I'm getting closer.

I've been able to get the fields to populate the XML and I can see the values now.  When I try to loop through the addd fields, it is now causing Filemaker to crash.

I've attached a screenshot of my code for the loop to parse the XML.  Again any help is appreciated.



Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Query Item Custom Fields
« Reply #5 on: May 26, 2017, 12:09:35 PM »
Hard to guess why FMP crashes, but the Set Field By Name is the likely candidate--it's not pointing at a valid field name. Change "DateExtName" to "DataExtName" and see if that stops the crash. (It might not; Are there additional Custom Fields that are returned in the Query Response?)

HTH!

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

smcgovern

  • Member
  • *
  • Posts: 5
Re: Query Item Custom Fields
« Reply #6 on: May 30, 2017, 06:22:27 AM »
Geoffrey,

Thanks for catching my typo there.  Unfortunately that still did not catch the issue.  I'm copying below the XML response for one record being retrieved:

<ItemInventoryRet>
            <ListID>80001074-1393001953</ListID>
            <TimeCreated>2014-02-21T11:59:13-05:00</TimeCreated>
            <TimeModified>2017-05-17T11:54:41-05:00</TimeModified>
            <EditSequence>1484928145</EditSequence>
            <Name>AE-0001</Name>
            <FullName>AE-0001</FullName>
            <BarCodeValue>AE-0001</BarCodeValue>
            <IsActive>true</IsActive>
            <ClassRef>
               <ListID>80000012-1482244336</ListID>
               <FullName>ACES</FullName>
            </ClassRef>
            <Sublevel>0</Sublevel>
            <ManufacturerPartNumber>1110-007</ManufacturerPartNumber>
            <SalesTaxCodeRef>
               <ListID>80000001-1302541402</ListID>
               <FullName>tax</FullName>
            </SalesTaxCodeRef>
            <SalesDesc>Balcrank - PUMP, 3:1, TRANSFER, LYNX</SalesDesc>
            <SalesPrice>355.57</SalesPrice>
            <IncomeAccountRef>
               <ListID>8000012A-1392986853</ListID>
               <FullName>SALES:Sales - Automotive Equipment</FullName>
            </IncomeAccountRef>
            <PurchaseDesc>Balcrank - PUMP, 3:1, TRANSFER, LYNX</PurchaseDesc>
            <PurchaseCost>197.54</PurchaseCost>
            <COGSAccountRef>
               <ListID>8000012C-1392987211</ListID>
               <FullName>COST OF SALES:COS - Automotive Equipment</FullName>
            </COGSAccountRef>
            <PrefVendorRef>
               <ListID>800005D0-1391461881</ListID>
               <FullName>Balcrank</FullName>
            </PrefVendorRef>
            <AssetAccountRef>
               <ListID>80000128-1392986630</ListID>
               <FullName>Inventory Auto Equipment - ACES</FullName>
            </AssetAccountRef>
            <ReorderPoint>1</ReorderPoint>
            <Max>4</Max>
            <QuantityOnHand>0</QuantityOnHand>
            <AverageCost>174.71</AverageCost>
            <QuantityOnOrder>0</QuantityOnOrder>
            <QuantityOnSalesOrder>1</QuantityOnSalesOrder>
            <DataExtRet>
               <OwnerID>0</OwnerID>
               <DataExtName>Bin</DataExtName>
               <DataExtType>STR255TYPE</DataExtType>
               <DataExtValue>07-10-01-03</DataExtValue>
            </DataExtRet>
            <DataExtRet>
               <OwnerID>0</OwnerID>
               <DataExtName>Category</DataExtName>
               <DataExtType>STR255TYPE</DataExtType>
               <DataExtValue>General Auto Equipment</DataExtValue>
            </DataExtRet>
            <DataExtRet>
               <OwnerID>0</OwnerID>
               <DataExtName>Manufacturer</DataExtName>
               <DataExtType>STR255TYPE</DataExtType>
               <DataExtValue>Balcrank</DataExtValue>
            </DataExtRet>
            <DataExtRet>
               <OwnerID>0</OwnerID>
               <DataExtName>CMC_Code</DataExtName>
               <DataExtType>STR255TYPE</DataExtType>
               <DataExtValue>Repair - 54110</DataExtValue>
            </DataExtRet>
            <DataExtRet>
               <OwnerID>0</OwnerID>
               <DataExtName>ACES_Stock</DataExtName>
               <DataExtType>STR255TYPE</DataExtType>
               <DataExtValue>Yes</DataExtValue>
            </DataExtRet>
         </ItemInventoryRet>

I'm also attaching a shot of the full extraction Loops for the request. 

From what I can tell, the interior Loop for the DataExtRet is never entered into.  Hopefully someone can see my error.  I did go ahead and add all the Custom Fields to the Extract to make sure that wasn't the issue.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Query Item Custom Fields
« Reply #7 on: May 30, 2017, 07:34:05 AM »
So it's still crashing?

Are there Custom Fields defined that might contain a value?

Have you contacted Productive Computing to see if this is a bug?

Using an Xpath filter in PCQB_ZParseXML on the result of PCQB_SGetXML( "Response" ) also lets you grab custom field data. It avoids the inner Loop and the need to open RelatedRecords, but Xpath has a bit of a learning curve.

HTH!

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

smcgovern

  • Member
  • *
  • Posts: 5
Re: Query Item Custom Fields
« Reply #8 on: May 31, 2017, 12:29:10 PM »
So I did reach out to Productive Computing and received this response:

From what I see in Intuit’s OSR, there aren’t any custom fields available when making requests for ItemInventoryQuery. Please check the OSR and verify which fields are available for ItemInventoryQuery under SDK version 13 under the qbXML format:
https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html

I must be confused as the XML does show the return value for the Custom Fields.


Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Query Item Custom Fields
« Reply #9 on: May 31, 2017, 01:18:02 PM »
The reply seems to suggest a misunderstanding--none of the ItemQueryRequests allow you to pass a Custom Field value, but that's not what you're trying to do. Try again, and explain that the problem is extracting the Custom Field values from the Query Response.

HTH!

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