Author Topic: [SOLVED] Invalid Argument. The specified record does not exist in the list  (Read 13125 times)

johnlewisdesign

  • Member
  • *
  • Posts: 81
Hi guys

I'm experiencing a weird problem. I am syncing line items to QuickBooks, the SKU below is an assembly.

CODE: 3140
SEVERITY: Error
MESSAGE: There is an invalid reference to QuickBooks item "BWB18FH-RW-CBW" in the invoice line. QuickBooks Error mesage: Invalid Argument. The specified record does not exist in the list.


This does exist, the stock is synced and up to date. This assembly's contents are also all synced individually and up to date. All syncs are successful.

Further down the invoice, there is another stock item with SKU:

VGP9648*3/4-CBW

I have a suspicion this could this be causing our issue? Are asterisks escaped in FM Books' transaction? Are asterisks an illegal characters in QuickBooks SKUs?

Any clarification greatly appreciated.

Thanks,

John
« Last Edit: February 19, 2013, 09:19:42 AM by johnlewisdesign »

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #1 on: February 13, 2013, 07:30:51 AM »
When you look at BWB18FH-RW-CBW in the QuickBooks Item list, what's the value in the Type column? I suspect it's a Group Item and not an Inventory Assembly Item. If so, it needs to be added to the Invoice using the InvoiceLineGroupAdd tag because adding it with the InvoiceLineAdd would produce the referenced error.

With regard to escaping: it's always a good idea to wrap text values in the PCQB_SFormatString function.

HTH!

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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #2 on: February 13, 2013, 08:55:23 AM »
Hi and thanks for the response! It says its an Inventory part. In other news, it says the Quantity on hand is -1 but this is built not bought.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #3 on: February 13, 2013, 03:40:20 PM »
An Inventory Assembly would not normally be bought, it would be built from other Items. Having none available (or less than zero) might be the source of the error.

In looking at my code base I see that I send QB a BuildAssemblyQuery Request for every Inventory Assembly Item on an Invoice and make sure that QuantityOnHand + QuantityCanBuild >= InvoiceLineAdd::Qty. If the answer is yes, and there aren't conflicts among the component Items that make up the Bill of Materials, I loop back through and send the necessary BuildAssemblyAdd Requests.

This is a pretty complex script with a lot of nested Loops because there may be multiple InventoryAssembly Items with component Items that intersect another Assembly Item's BOM, or an Item in a BOM may also be its own InvoiceLine on the Invoice. If it's not possible to Build all the InventoryAssembly items, I don't make the InvoiceAdd Request.

tl;dr... Try building enough of the InventoryAssembly Items via the QB UI and see if the InvoiceAdd Request succeeds.

HTH!

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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #4 on: February 14, 2013, 05:27:24 AM »
Hi Geoffrey and thanks for this.

That had crossed my mind, but unfortunately this makes no difference. I adjusted a Qty on hand value of -1 in Quickbooks to +1, also I tried one with 197 on hand. It returns an error the first line item every time. On every invoice. This happens AFTER its processed all lines and set the transaction up ready to send. Its like it returns to the first row, and can't find it any more in QB.

I've tried trapping the loop that rinses through the invoice line items too. I made a new invoice with one line item; when the script had finished it was showing $PRNO as 2 (portal row number variable) even though there was just one line item. Fearing it was trying to send an empty row, I trapped it, creating a count for the primary key for each line item ($PortalRowCount) and set it to exit if $PortalRowCount > $PRNO, but the problem still persists.

EDIT: I forgot to mention the assemblies are handled by our solution in their entirety. All items are single inventory items in QuickBooks. This worked perfectly in FileMaker 11 using Fm Books Connector v4, but not in 12 using Fm Books Connector v5.

Argh. I'm losing the will...and running out of ideas! I'm off to check the old version for script changes.. :o
« Last Edit: February 14, 2013, 10:27:45 AM by johnlewisdesign »

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #5 on: February 14, 2013, 11:51:46 AM »
Once the transaction is setup and ready to send but before you evaluate the Execute function, set a field or variable to PCQB_SGetXML( "Request" ; "" ) and post the result here. The problem might be easier to identify when looking at the entire InvoiceAdd Request in its native language.


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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #6 on: February 15, 2013, 05:29:50 AM »
Hi Geoffrey and thanks, yes that really does help. I'm learning new tricks throughout this process and will be sure to keep my hand in around these parts, I owe it now!

This looks well-formed to me, but its the first raw XML I've ever seen from Fm Books - in fact I'm picking up where a previous employee left off, so I'm still working it out. In at the deep end!

OK here's pretty much everything:

XML
1 line item version:
http://pastebin.com/AAwBnf4X

37 line items version:
http://pastebin.com/yw35V0Ki

Script
Send invoice to QB script
http://www.anony.ws/ie7

Send QB request script
http://www.anony.ws/ieG

Build invoice request info
http://www.anony.ws/ieH

If anything jumps out I will be relieved to say the least. Thanks for your help it is much much appreciated. There's only so much head scratching I can do before I hit the squishy part, then its game over!  ;D

John

PS MODS: Your SMF upload path isn't specified, or your temp folder isn't writable - so your attachments don't work! Nor do your images. If you want some help, let me know!

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #7 on: February 15, 2013, 06:02:39 AM »
The 1 line version shows an unescaped quote character in the Desc element. As mentioned earlier, the PCQB_SFormatString function is your friend. In your script, try wrapping both Invoice_Line_Items::Stk_Code and Invoice_Line_Itmes::Description arguments in a PCQB_SFormatString function using the qbType "STRTYPE".

HTH!

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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #8 on: February 15, 2013, 07:09:36 AM »
I see what you mean now, couldn't see the wood for the trees. This is quite common with this inventory too. I'll look into that and report back. That is a probable cause I'd not even explored yet, even though I was barking up the right tree with the asterisks! Sorry I missed the action in that first post, my brain was melting!

Really appreciate the time, Geoffrey - here we go!

John

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #9 on: February 15, 2013, 09:31:28 AM »
...Aaaaaaaand it failed again! Damn I thought that was it! Thank you so much though for that, I have now learned how to use QBXML properly :)

in other news, I notice the cursor ends up in a blank row, which may or may not have something to do with it. I have made sure the request is built and exited before this though so it shouldn't [SHOULDN'T] be a problem.

See:
http://www.anony.ws/ilg

BUT maybe more importantly, if I open it in a code editor (Sublime Text 2), the XML has a hard return and a new line at the end...

See:
http://www.anony.ws/ilt

Could this dodgy trailing character be the cause of my woes maybe?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #10 on: February 15, 2013, 09:42:26 AM »
Probably not--that's something the plug-in created and over which you have no control. What version of QB are you using? Is it a non-US version?


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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #11 on: February 15, 2013, 09:51:55 AM »
Fair enough.

I'm using QuickBooks Premier Manufacturing and Wholesale Edition 2012 (US), the very latest Fm Books Connector v5, and FileMaker Pro 12 Advanced v3.


I really cannot work out why this is causing me pain in 12 but not 11! Our scripts are identical, as is the QuickBooks. Its gotta be FM12 or FMBooks connector v5 that has changed. Think I'll rule out FileMaker right now by testing their previous version converted to 12.

I think its going to drive me to drink - being Friday and all :)

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #12 on: February 15, 2013, 10:10:27 AM »
Probably not--that's something the plug-in created and over which you have no control.

I just thought - this would have been created using the FM11 sample file I bet. Whats the difference between the FM11 and FM12 sample files, apart from format, anything drastic in the scripting?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Asterisk in SKU
« Reply #13 on: February 15, 2013, 11:21:00 AM »
I worked with a developer yesterday who was integrating FMP12 and QB Premier 2013. No issues passing multi-line Invoices.

I just looked at your second link to see if I'd misunderstood "dodgy trailing character" (I hadn't) but did notice that you still have an unescaped quote mark in the InvoiceLine's Desc element.

HTH!

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

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Asterisk in SKU
« Reply #14 on: February 18, 2013, 05:25:34 AM »
Sorry Geoffrey that was a bum steer, that was the very first output with a part highlighted. I have actually got all suspect chars escaped now.

I have a feeling this lies within the scripting or relationship now. I can successfully complete a transaction in a converted to FM12 version of their old system. The thing is, where to start!

Arghhh!!