ItemInventoryQuery Usage

[Joe Germann]

I am so stumped.  I am trying to test ItemInventoryQuery to see if a Part Number is already in QB; If not then I will add it.  I've tried to do a simple query on ItemInventoryQuery using a multitude of options (all // out in the attached clip) and it always returns !!ERROR!!.

What do I need to do to find out if an Item is already in Inventory?

Many thanks,

Joe

Set Variable [ $QB_PART_NUMBER; Value:AWI_Line_Items to Invoice::Line Item Part Number & AWI_Line_Items to Invoice::Line Item
Part Option ]
# Check (Query) if this item already exists or is going to be an ADD
PCQB_RqNew [ Select: On; Results: $$PCQB_RESULTS; Request Type: "ItemInventoryQuery"; Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "ItemInventoryRet::FullName"; Field Value:
$QB_PART_NUMBER; Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "Name"; Field Value: $QB_PART_NUMBER;
Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "FullName"; Field Value:
$QB_PART_NUMBER; Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "ItemInventoryRet::Name"; Field Value:
$QB_PART_NUMBER; Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "EntityFilter::FullName"; Field Value:
$QB_PART_NUMBER; Optional Attributes: ]
// PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "IncludeLineItems"; Field Value: "true";
Optional Attributes: ]
PCQB_RqExecute [ Select: On; Results: $$PCQB_RESULTS; Hide FM Window: Off; Optional Filter: ; Optional File Path: ]

      If [ $$PCQB_RESULTS ≠ 0 ]
      #The item does not exists; ADD to QB
      PCQB_RqNew [ Select: On; Results: $$PCQB_RESULTS; Request Type: "ItemInventoryAdd"; Optional Attributes: ]
      PCQB_RqAddFieldWithValue [ Select: On; Results: $$PCQB_RESULTS; QB Field Name: "Name"; Field Value:
      $QB_PART_NUMBER; Optional Attributes: ]
      PCQB_RqAddFieldWithValue [ Select: On; Results: $$

[PCIPal]

Hello Joe, 

You cannot search by part number directly. But there is a workaround. 

You will have to pull all ItemInventory records down from QB ("ItemInventoryQuery" maybe specifying and ActiveStatus as TRUE), then iterate over the set and see if the part number they have on hand matches the "ManufacturerPartNumber" field of each record returned. 

This process can be made faster by specifying "IncludeRetElement" with "ManufacturerPartNumber" so the query only returns the part number. 

Hope this helps

-PCIPal

[Joe Germann]

Thanks.  I'll give that a try in the morning.

Joe

 

[Joe Germann]

18 hours ago, PCIPal said:

Hello Joe, 

You cannot search by part number directly. But there is a workaround. 

You will have to pull all ItemInventory records down from QB ("ItemInventoryQuery" maybe specifying and ActiveStatus as TRUE), then iterate over the set and see if the part number they have on hand matches the "ManufacturerPartNumber" field of each record returned. 

This process can be made faster by specifying "IncludeRetElement" with "ManufacturerPartNumber" so the query only returns the part number. 

Hope this helps

-PCIPal

Something is still screwy.   I wrote a Script to download ALL ItemInentoryQuery items BUT only 15 ItemInventory records appear to be opened that I can sequence through and they are not in the order I would expect.  The script is very, very simple. 

What am I missing or doing wrong?

# Start with as clean FM buffer
Go to Layout [ “R3D_PCQB_Inventory” (R3D_PCQB_Inventory) ]
Select Window [ Current Window ]
Show All Records
If [ Get (TotalRecordCount) > 0 ]
Delete All Records

# The QB Item Inventory data to Pull
PCQB_RqNew [ Select: On; Results: $$PCQB_RESULTS; Request Type: "ItemInventoryQuery"; Optional Attributes: ]
PCQB_RqExecute [ Select: On; Results: $$PCQB_RESULTS; Hide FM Window: Off; Optional Filter: ; Optional File Path: ]
PCQB_RsOpenFirstRecord [ Select: On; Results: $$PCQB_RESULTS ]

Loop
Exit Loop If [ $$PCQB_RESULTS = "!!ERROR!!" or $$PCQB_RESULTS = "END" or $$PCQB_RESULTS = "?" or
$Loop_Count > 100000 ]
New Record/Request
Set Variable [ $Loop_Count; Value:$Loop_Count + 1 ]
Set Field [ R3D_PCQB_Inventory::Portal_ID; $Loop_Count ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_ListID; QB Field Name: "ListID" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_TimeCreated; QB Field Name: "TimeCreated" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_TimeModified; QB Field Name: "TimeModified" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_EditSequence; QB Field Name: "EditSequence" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_Name; QB Field Name: "Name" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_FullName; QB Field Name: "FullName" ]
PCQB_RsGetFirstFieldValue [ Select: On; Results: R3D_PCQB_Inventory::QB_IsActive; QB Field Name: "IsActive" ]
PCQB_RsOpenNextRecord [ Select: On; Results: $$PCQB_RESULTS ]
End Loop

[Joe Germann]

YEA!!!!   I got it figured out.  My items are BOTH Inventory and non-Inventory items. I was just pulling the Inventory Items; I just need to pull the non-inventory items also.

Thanks so much for your guidance.

Joe

 

[Geoffrey Gerhard]

Coming to this thread late, but a couple notes for readers who might have similar issues:

1. Use an ItemQuery to find all Items, regardless of their QB type, that match the request's criteria.
2. PCI Pal correctly notes that the "ManufacturerPartNumber" is not available as a filter in a Query Request. But if  "Part Number" is meant to describe the QB Item Name/Number value, you can use the NameFilter in your Query Request.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC 28105
704.814.6852

[Joe Germann]

Thanks so much for he help.  I did  script that used BOTH Inventory and NonInventory.  I'll rewrite it to do just the single itemQuery and pull all of the different items into a FM buffer.  This is a real nice way for me to check if an Item needs to be added or not to QB.

 

Joe

.