Author Topic: CODE: 3140 There is an invalid reference to QuickBooks Item  (Read 3675 times)

Brans

  • Member
  • *
  • Posts: 9
Hi Folks,

Just trying out the Demo QB Plugin.  Managed to work out adding customers to QB.  Spent most of the day trying to create invoices in QB.  I have written the attached script and can see from the error that QB will not add/create the invoice if the item is not listed in QB.  Understood so whats the correct way to say something like:

If item doesn't exist = add it to QB

If the item exists in QB then it works fine, but I need to add items if they don't exist.  I have almost 200,000 items in Filemaker that change in price and qty everyday.

I have been looking everywhere without much luck.  Your assistance would be greatly appreciated.

Thank you, Brans

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #1 on: June 14, 2015, 05:48:25 PM »
You can perform an ItemQuery in your Loop to confirm whether or not the Item exists in QB, and insert the necessary steps or call a sub-script to add the Item for you. This can be significantly more challenging when FMP and QB have been used separately prior to your integration, as the ProdID value in FMP must exactly match the FullName value in QB. And adding Items from FMP can be tricky by itself because there are several Item types and the QtyOnHand and Cost per Item are not always included in FMP.

Check the archives here, and feel free to post additional questions as the arise.

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

Brans

  • Member
  • *
  • Posts: 9
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #2 on: June 15, 2015, 04:09:10 AM »
Hi Geoffrey,

Thank you for you kind reply.

I'm not too worried about the products/items in QB.  It is more important that items are uploaded from FM.  Then of course existing items that have already been uploaded from FM can be updated in QB.  No need to pull anything from QB initially.

Therefore I need to know the commands require to:

a. add items and related info to QB if it doesn't exist (ItemQuery example script step)
b. if it does exist, command to update. (PCQB_RqAddRelatedRecord tried this and PCQB_RqAddFieldWithValue but doesn't add if the item doesn't exist in QB)

I have been looking for a similar script step to work from on this forum but struggling to find anything relevant.  Looking at the QB OSR is some help but knowing which commands to use is, as it seems, not so obvious to me.

I did look at the sample Demo Database hoping to extract what I need but its totally different to the videos and sample scripts in the developers guide.  Using Set variables instead of set fields as shown in the samples.

Many Thanks
Brandan

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #3 on: June 15, 2015, 11:25:39 AM »
When looking at the OSR, you'll want to choose the correct type of ItemAdd from the Select Message drop-down menu. ItemInventoryAdd, ItemNonInventoryAdd, ItemServiceAdd, etc.

ItemInventoryAdd is the usually the trickiest to get right, since QtyOnHand, InventoryDate and TotalValue affect what happens to Inventory values as of the TxnDate of an Invoice.

HTH!

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

Brans

  • Member
  • *
  • Posts: 9
Adding and modifying items in QB
« Reply #4 on: June 25, 2015, 03:47:43 PM »
Hi Geoffrey,

Successfully adding items to QB but still failing to modify items that already exist.

Is this the right method to modify an existing ItemNonInventory (attached).

Getting this error:

CODE: -1000
SEVERITY: failed validation
MESSAGE: Error at Name
Reason:   Element 'Name' is unexpected according to content model of parent element 'ItemNonInventoryMod'.
Expecting: ListID.

Field Value:   20


If I try by ListID using an existing QB ListID it then complains about SaleOrPurchaseMod fields.

CODE: -1000
SEVERITY: failed validation
MESSAGE: Error at SalesOrPurchaseMod
Reason:   Element 'SalesOrPurchaseMod' is unexpected according to content model of parent element 'ItemNonInventoryMod'.
Expecting: EditSequence.

Field Value:   


I simply need to update an items desc and price.

Thanks again.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #5 on: June 26, 2015, 11:27:50 AM »
Using "FullName" instead of "Name" should do the trick, but I prefer to use ListID. The second error is showing up because your ModRequest must contain the EditSequence value. I always query for that value as part of the ModRequest, since it's value can change based on user interaction in QB since your last Add/Mod Request for the target List record.

HTH!

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

Brans

  • Member
  • *
  • Posts: 9
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #6 on: June 27, 2015, 03:44:05 AM »
Thanks Geoffery,

I didn't understand at first but do now.  Bit of a drag you have to download ListID and EditSequence every time you want to update a product.  Also you have to do this to add an item so you know if it exists or not.

So downloading the items is the method used when dealing with items ?  Is there no other method to simply ask QB direct if an item exists using ItemNonInventoryQuery::Name because 'Name' would be the ProductID used in FM.  This would save downloading the items list.  Our Product (Item List) could easy approach 10,000's of items and services.

If I decide this system works I would be looking to migrate to QB Online (I understand I need the other plugin).  Are there known performance issues if I have to download thousands of QB items before I upload one or more invoices ?

We don't intend to use QB to add items and update them.  Just need to add or update items in QB before producing invoices.

We only need QB for accounting and reports (keeps the accountant happy).

Thank you again
Brans

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: CODE: 3140 There is an invalid reference to QuickBooks Item
« Reply #7 on: June 29, 2015, 05:48:22 AM »
I'm not certain what you want to accomplish, but I'll take my best shot at it....

When updating an Item in QB, you need to include the current EditSequence value. An ItemQuery Request is more certain than storing that value in FMP and hoping it's the same value at your next ItemMod Request. Ideally, you're storing the Item's ListID in FMP, and if not, constructing the FullName value from one or more FMP fields.

You can ask QB whether there is an Item with a FullName value that matches your FM ProductID by using an ItemQuery Request. This becomes trickier if you have an Item hierarchy in QB where an Item can be the Parent or Child of another Item, because the FullName value becomes a concatenation of it's Parent Item's FullName values, separated by ":" characters. LIke this: "GrandparentItemName:ParentItemName:ItemName".

From your description, it seems to me that you'll never need to download 10,000 Items. There are mechanisms that allow you to maximize the efficiency of doing so, should the need arise. That said, "migrating" to QBOnline means you'll need to rewrite the scripts that interact with QB because the two plug-ins are different, all Query Requests need to be written as SQL Queries, and QBOnline doesn't support the same feature set.

HTH!

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