Method Community

Hi there,

Sometimes our customers need to be able to see their PO numbers for each line item on an invoice.

We prepare one Sales Order for each of their POs, using the PONumber field to record their PO number (not our internal PO number for the items we need to order). That's easy enough, but there can be multiple Sales Orders on each Invoice.

I was hoping to drive this in a report by adding a Linked Field to the InvoiceLine table. I noticed there was a LinkToSalesOrder link option which can bring in data from the SalesOrderLine table. The LinkToSalesOrder link option doesn't have the PONumber field since that's part of the SalesOrder table and not the SalesOrderLine table, so I first added the linked PONumber field to the SalesOrderLine table... but the LinkToSalesOrder link option from the InvoiceLine table does not have the linked field.

Not only that, but it appears as though there's no transparancy around what is included in the LinkToSalesOrder "table", which does not appear to be a real table or view that is available under Customize - Tables / Fields.

Is there any way for me to change the fields available in LinkToSalesOrder?

Is there a better way to go about doing this?

-S

Hello sbradley,

      I found an older post discussing the issue here.  In summary, it may be a QuickBooks SDK limitation and you can do some configuration to get what you need in another way.

     I have looked into this to see if I could do it.   First, the LinktoSalesOrder links to the SalesOrderLine table.  When you are in a Tables/Fields - Edit Table… you can hover the mouse over the DropDown fields (they will be faintly underlined) and a popup will come up telling you what table it is linked to.  So it does appear there is a link, but it doesn't seem to be usable.

     I checked it out and I did hit the odd thing where if you try to create a Linked Field with LinkedToSalesOrder, you don't get all the fields for an option.  This must be by design for some reason or limited by the QuickBooks SDK.  I currently can not find a way to rewire it.  There is a field called LinktoSalesOrderLine on the InvoiceLine tabe, but I have no data in there for invoices.  

- Greg

Hi Greg,

Ok, that makes sense. Although since I can add stuff to pretty much any other table on the Method side for Method's exclusive use maybe the ability to do the same with the LinkToSalesOrder "entity" will be available in the future?

I guess in the meantime I can either:

1) Add a custom field to InvoiceLine which has the Sales Order RecordID if someone creates an Invoice from a Sales Order, or;

2) Create a custom table that has the InvoiceLine RecordID and the SalesOrderLine RecordID to create the relationship, and have a new row added whenever an Invoice is created by a Sales Order.

Ideally I'd want to go with Option 2 but it doesn't appear as though I can actually specify a link with an existing table in a new table. Is that correct? Is that functionality going to be rolled out?

-S

Hello sbradley,
   As the other forum post states, it's not a matter of Method developers creating the link between an Invoice and Sales Order.  It is a question if QuickBooks SDK allows Method to use the link.  It looks like the SDK does not allow third-party software developers to use the link.  Currently, I do not believe this is something in the works with our developers as it looks like a limitations in the tools.
    
      If you are looking to link an Invoice to a Sales Order, you can try Adam's suggestion in the other forum post.  Create a field for the QuickBooks_SalesOrder_CreateInvoice screen that carries the Sales Order number and have a custom action on Create Invoice to transfer that number to the Invoice.  You would then have a link field with the Sales Order number to link back to the Sales Order.

      If you are looking for Sales Orders for individual lines on an Invoice, you can have a drop down to the Sales Order table.  To reduce clerical errors, you can filter that list for Sales Orders for only the Customer on the Invoice and maybe by date as well.

      Your option 2 could work, but what happens with the second sales order you converted to an invoice?  How do you link that Sales Order with an existing Invoice?  You can make a relationship table as you describe, but how do you create the records? It seems like it would get into complex relationships.

In the Loop Through Grid, after the InvoiceLine has been created, I'd create the relationship table record based on the SalesOrderLine RecordID and the InvoiceLine RecordID.

But like I mentioned, that would only sort of get me there because when creating a fully custom table I can't define relationships in there with other tables, no referential integrity.

I think what I'm going to have to do, since I can't put real lookup fields in a grid which is what I'd need to do otherwise, is abandon the idea of referential integrity. Create two new fields in InvoiceLine: one for the SalesOrderLine RecordID, just so I can see a real flow if I want to, and one for the SalesOrder PONumber. Get the PO number before the CreateInvoice screen pops up, get the SalesOrderLine RecordID when I'm traversing the grid in the CreateInvoice screen and insert both of those data into the InvoiceLine table when the InvoiceLine records are being created.

Doing that will also mean I'll need to setup some kind of additional constraint around actions related to the PONumber field in the Sales Order screen; if someone changes the contents of that field after the InvoiceLine records are created, when the Sales Order is saved should it cascade that change to the InvoiceLine records? Or should that change be something the user is notified of when they enter an already saved Invoice where the Sales Order corresponding to the InvoiceLine records in that invoice no longer has the same "InvoiceLinePONumber" as the Sales Order PONumber?

Some things I'll have to consider.

Hello sbradley,
   I had a couple of comments on the relationship table.  First, the field LinktoSalesOrder is a relationship between InvoiceLine and SalesOrderLine.  The options have been limited.   Creating another relationship between SalesOrderLine and InvoiceLine may cause trouble, but you can try it.  The other thing is you could create a relationship table, which is really a many-to-many relationship.  One column is the InvoiceLine_RecordID and the other field is SalesOrderLine_RecordID.  The problem I see is how to insert, update and delete from the table.  It requires some high-end customization.  As you said, what happens if the invoice item is changed?
     I couldn't answer your questions on how the saves would cascade or how warn users of changes.  It would be all in the way you set up the screens and tables.  It's beyond my scope for forums.