Author Topic: Invoice Add Where is S.O. NO?  (Read 5821 times)

trebor

  • Member
  • *
  • Posts: 57
Invoice Add Where is S.O. NO?
« on: December 16, 2014, 01:15:36 PM »
I want to put some data into the S.O. No field, but I do not see it in the  OSR reference in Invoice Add.

I see PO No, FOB etc but I cannot find SO NO.  Where in the sequence of fields could I stick it?

Or is the SO No created from a Sales order when converted to an Invoice?

I want to add a name on the order, and really do not want to have to deal with Sales people. Adding them if they are not there, testing to make sure the name is spelled right etc.  Plus I do not want initials, I want the full name.

I just want to dump the text in (oh yea I already am using Memo, CustomerMessage, Other for a bunch of text).


Any other fields available I could possibly use (other than custom fields:: I want to print on the invoice)?

Thanks
Rob


Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #1 on: December 16, 2014, 01:53:21 PM »
There's no such field, although the UI may show that label on an Invoice Template. If you see it on an Invoice template, go into the Layout Designer and see what field it labels.

I've seen users customize a template by re-labeling the PONumber, FOB, Memo, and Other fields for use in place of adding a CustomField. The trouble with that method is the potential conflict with values that may have been entered in those fields in the past, or that may need to be entered in the future.

Custom fields are really useful, and they aren't all that hard to integrate into your workflow. They show up in the list of Filters (fields) you can select from QB's Advanced tab in the Find window, which is much more intuitive than remembering to select FOB (for example) when you want to find Invoices tied to a particular Sales Order Number.

HTH!

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

trebor

  • Member
  • *
  • Posts: 57
Re: Invoice Add Where is S.O. NO?
« Reply #2 on: December 16, 2014, 02:25:31 PM »
I shuffled some things around and am using the FOB field right now, it seems to be doing its thing.  Good part about my solution is there is basically never a time they actually create invoices in QB, it is all done through a web form.  Thanks for getting back.

Ok an odd thing is happening to me.

After I create the invoices,  it passes back specific info for that invoice :: Invoice Number (refNumber), Balance Due, And Invoice Date.  When I put that all back up to the web into a MYSQL table, it adds 1 penny to the Balance.

I suspect a rounding error. Or could it be the MYSQL field I am putting into corrupting it (QB_InvoiceAmt    decimal(10,2) )? Should I round to 3? Then in the PHP display, truncate it?  Ok this has nothing to do with FM Connector.

Anyway Wondering if you have seen that before.

Thanks
Rob

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #3 on: December 17, 2014, 11:14:46 AM »
I'm not sure there's any benefit applying a rounding function, since the value that comes back from QB is already rounded and padded with two decimal places. Could \ store the value in a separate text field and then pass the text field version to MYSQL?

HTH!

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

trebor

  • Member
  • *
  • Posts: 57
Re: Invoice Add Where is S.O. NO?
« Reply #4 on: December 18, 2014, 02:17:17 PM »
I figured it out.  Rounding error. Happens.

Thanks
Rob


mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #5 on: January 07, 2015, 12:59:41 PM »
It sounds like trebor found a way to work around this issue, but I've come across it now, so I'd like to re-ask the question.

Geoffrey, you say that there is no such field, and that it's probably just a label for some other field, but when I click on Customize Data Layout to see how the fields were labeled on the particular Invoice Template that I am using, it shows that "S.O. No." is the name of a field, not the name of a label.

I also checked to see if this is a custom field, and it's not.

I'm working with QuickBooks Enterprise Solutions 15.0 (Country: US, Version: 25), and I noticed that the max SDK version at https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html is 13. Could it be that the OSR is out of date?

Any other ideas?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #6 on: January 07, 2015, 02:32:36 PM »
I was mistaken: there is such a field and has been for several years. There is no way to directly access its value via the API, but I believe I've found the mechanism needed to add or retrieve it.

At the Invoice Level: pass the TxnID for the target SalesOrder record as the value of LinkToTxnID. You can pass more than one value, but I'm not sure what is displayed in the field when it's located in the QB Sales Invoice template's Header.

At the InvoiceLine Level: pass the TxnID of the target SalesOrder record as the value of LinkToTxn::TxnID and the SalesOrderLine's TxnLineID as the value of LinkToTxn::TxnLineID for each InvoiceLine. Same question about what value appears in the S.O. No field when it's placed in the Invoice's Header on the Sales Invoice Template. It is apparently possible to add a Column to the Invoice Template by clicking the Prog Cols tab, and in this case it will display the SalesOrder Number for every InvoiceLine linked to a SalesOrderLine.

I presume the S.O. No field in the header will show only the value of the first related SalesOrder.

It's possible to use both methods of identifying related SalesOrders, but you may not pass the same TxnID value in a LinkToTxnID and a LinkToTxn::TxnID element.

HTH!

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

mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #7 on: January 13, 2015, 08:04:59 AM »
Geoffrey, thank you very much for responding.

It turns out my client was using the S.O. No field to store some text value and not a sales order number. We switched their invoice template to use a custom field instead, so now I'm using the DataExt request to make the modification.

mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #8 on: February 24, 2015, 05:06:17 PM »
I'm working on another FileMaker/QuickBooks integration project, and this time the client really does need to be able to link an invoice to a sales order.

I tried Geoffrey's method of specifying the sales order at the invoice level:

PCQB_RqAddFieldWithValue ( "LinkToTxnID" ; $txnID )

This populates the S.O. No. field correctly. However, it also automatically creates an invoice line item for each sales order line item from that sales order.

This may actually work out great - I still need to check with the client to see if this is what's desired - but my guess is that I'll end up having to remove these automatically created lines and create my own invoice line items.

So I decided to try the other approach that Geoffrey suggested, which is to link each invoice line item to a sales order and a sales order line item:

PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnID" ;  $txnID )
PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnLineID" ; $txnLineID )

However, when I try this, I get an error. The error code is 3153, and the message is: "The specified parameters conflict with each other."

I tried just specifying the sales order line item, but then I get a "failed validation" error with code -1000 and message "Element 'TxnLineID' is unexpected according to content model of parent element 'LinkToTxn'. Expecting: TxnID."

Any ideas?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #9 on: February 25, 2015, 09:54:07 AM »
Are you still passing...

PCQB_RqAddFieldWithValue ( "LinkToTxnID" ; $txnID )

...in your new approach? If so, is the same $txnID being passed to any of the...

PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnID" ;  $txnID )

...calls? That may be why you're getting the "...conflict with each other" message.

Note that if you pass "LinkToTxn::TxnID" you must also pass "LinkToTxn::TxnLineID" and vice-versa.

HTH!

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

mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #10 on: February 27, 2015, 03:52:38 PM »
Hi Geoffrey,

Thank you for responding - I appreciate it very much.

>> Are you still passing... PCQB_RqAddFieldWithValue ( "LinkToTxnID" ; $txnID ) ...in your new approach?
Ive tried both.

When I dont pass it, I get the "The specified parameters conflict with each other" error message.

When I do, I get this error: The given object ID "5954A-1424826385" in the field "LinkToTxn TxnID" is invalid.  ("5954A-1424826385" is the value for $txnID.)

If I remove the PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnID" ; $txnID ) and PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnLineID" ; $txnLineID ) at the sales order line item level, then the invoice is created successfully and the S.O. No. field is populated correctly. The sales order that Im testing with has two line items, so when I do this, two invoice line items also get created automatically and are correctly linked to the sales order line items. (This is the first approach I was describing in my previous post.) But then I also end up creating two additional line items (because thats what I'm specifying in the "InvoiceAdd" request), and these are essentially duplicates of the first two line items that are created automatically (but there's no guarantee that all of the values will be the same). So if I cant get this to work, I think what Ill need to do is create the invoice and in the same step have it link to the sales order. This will result in the invoice line items being created that correspond to the sales order line items. Then, as a separate step, Ill need to update the invoice line items with data from FileMaker. It's going to be quite a bit more work, so I was hoping to be able to make it work in a single pass.

>> If so, is the same $txnID being passed to any of the... PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnID" ; $txnID ) ...calls?
Yes, its the same $txnID value.

>> That may be why you're getting the "...conflict with each other" message.
But what other transaction ID would I pass to it?

>> Note that if you pass "LinkToTxn::TxnID" you must also pass "LinkToTxn::TxnLineID" and vice-versa.
Yeah, I noticed that - thanks. Although I don't understand why that would be necessary. If invoice line item A is linked to sales order line item B, which itself belongs to sales order C, then we indirectly know that invoice line item A has a relationship with sales order C. So why does that need to be specified?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #11 on: February 28, 2015, 08:26:40 AM »
I wonder if there's something else causing the error. Are either of the SO LineItems already received in full in QB?

Are you trying to pass the ItemRef element? You did not post your script, but you'll get an error if you pass an ItemRef element AND the "LinkToTxn::TxnID" and "LinkToTxn::TxnLineID" elements in the InvoiceLineAdd node.

Given the error messages you report, I'm betting it's the latter issue. Please update the thread if either of these, or something else entirely, solves the problem.

HTH!

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

mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #12 on: March 03, 2015, 08:31:29 AM »
>> Are you trying to pass the ItemRef element?
That was it. I removed PCQB_RqAddFieldWithValue ( "ItemRef::FullName" ; $itemName ) from the InvoiceLineAdd node, and it worked.

Thank you Geoffrey!

Here's the full sequence of what I'm doing now, in case it's helpful to anyone:

PCQB_RqNew ( "InvoiceAdd" ; ""  )

PCQB_RqAddFieldWithValue ( "CustomerRef::FullName" ; $customerName )
PCQB_RqAddFieldWithValue ( "ClassRef::FullName" ; $className )
PCQB_RqAddFieldWithValue ( "TemplateRef::FullName" ; $template )
PCQB_RqAddFieldWithValue ( "TxnDate" ; Get ( CurrentDate ) )
PCQB_RqAddFieldWithValue ( "ShipAddress::Addr1" ; $addr1 )
PCQB_RqAddFieldWithValue ( "ShipAddress::Addr2" ; $addr2 )
PCQB_RqAddFieldWithValue ( "ShipAddress::City" ; $city )
PCQB_RqAddFieldWithValue ( "ShipAddress::State" ; $state )
PCQB_RqAddFieldWithValue ( "ShipAddress::PostalCode" ; $postalCode )
PCQB_RqAddFieldWithValue ( "PONumber" ; $poNumber )
PCQB_RqAddFieldWithValue ( "TermsRef::FullName" ; $terms )
PCQB_RqAddFieldWithValue ( "DueDate" ; $dueDate )
PCQB_RqAddFieldWithValue ( "SalesRepRef::FullName" ; $salesRepName )
PCQB_RqAddFieldWithValue ( "FOB" ; $fob )
PCQB_RqAddFieldWithValue ( "ShipMethodRef::FullName" ; $shipMethod )
PCQB_RqAddFieldWithValue ( "ItemSalesTaxRef::FullName" ; $itemSalesTax )
PCQB_RqAddFieldWithValue ( "Memo" ; $memo )
PCQB_RqAddFieldWithValue ( "CustomerMsgRef::FullName" ; $customerMessage )
PCQB_RqAddFieldWithValue ( "CustomerSalesTaxCodeRef::FullName" ; $customerSalesTaxCode )
PCQB_RqAddFieldWithValue ( "Other" ; $other )
// Do not include the LinkToTxnID element.
// (I'm showing it here commented out just to make the point that it's not included in the request.)
// PCQB_RqAddFieldWithValue ( "LinkToTxnID" ; $transactionID_salesOrder ) //

PCQB_RqAddRelatedRecord ( "InvoiceLineAdd" ; "" )
// If the Sales Order transaction IDs are included below, then do not include the ItemRef::FullName element.
// PCQB_RqAddFieldWithValue ( "ItemRef::FullName" ; $itemName ) //
PCQB_RqAddFieldWithValue ( "Desc" ; $desc )
PCQB_RqAddFieldWithValue ( "Quantity" ; $quantity )
PCQB_RqAddFieldWithValue ( "Rate" ; $rate )
PCQB_RqAddFieldWithValue ( "ClassRef::FullName" ; $className )
PCQB_RqAddFieldWithValue ( "SalesTaxCodeRef::FullName" ; $salesTaxCode )
PCQB_RqAddFieldWithValue ( "Other1" ; $other1 )
PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnID" ;  $transactionID_salesOrder ) //
PCQB_RqAddFieldWithValue ( "LinkToTxn::TxnLineID" ; $transactionID_salesOrderLineItem ) //

PCQB_RqCloseRelatedRecord
« Last Edit: March 03, 2015, 08:33:32 AM by mislav »

mislav

  • Member
  • *
  • Posts: 10
Re: Invoice Add Where is S.O. NO?
« Reply #13 on: May 14, 2015, 09:56:46 AM »
An update:

When you add the invoice line items to the request, make sure you do it in the same order as the sales order line items that exist in QuickBooks.

For example, suppose we have a sales order in QuickBooks with line items A, B, C, and we have an invoice in FileMaker with line items A, B, C that correspond to the sales order line items A, B, C.

If, when creating the request, you add the line items out of order (for example A, C, B), then you will get an error with code 3210 and message like this:

The "LinkToTxn" field has an invalid value "TxnID=ZZZ, TXNLineID=YYY".   

In the example error message above, ZZZ is the sales order transaction ID, and YYY is the sales order line transaction ID corresponding to line item B.

Note that C is not considered to be out of order, because you can skip line items; you just can't go backwards.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Add Where is S.O. NO?
« Reply #14 on: May 15, 2015, 03:51:03 AM »
This is good to know, and not something I'd have expected. Thanks for the follow-up.