Method:Classic General Q&A

Re: Using linked fields to relate Purchase Orders/Invoices/Credit Memos

Method_Adam — Tue, 16 Jul 2013 13:32:39 GMT

Hi Charles,

I've replied to the thread above. Please post there if you have any questions. Thanks.

- Adam

Re: Using linked fields to relate Purchase Orders/Invoices/Credit Memos

furrywombat — Sat, 13 Jul 2013 16:45:56 GMT

Hi Valbon,

This is obviously an old post, but we're having a very similar issue trying to grab the originating sales order # from the Invoice or InvoiceLine table. I'm not seeing this field anywhere, even though it exists in the local QB database table.

There is an ongoing thread for this issue, which you will find here:
http://www.methodintegration.com/cs/forums/p/4752/18257.aspx

No Method staff has posted to the thread, and I'm hoping that the reason isn't because there simply IS no solution.

Please help! My brain is about to explode over here.

Best,

Charles

Re: Using linked fields to relate Purchase Orders/Invoices/Credit Memos

Method_Valbon — Mon, 23 Jan 2012 15:23:13 GMT

Hi Sam,

Every table must have a record ID…but it won't appear as a listed field under Customize>>>Tables / Fields because it's just assumed it is there.

You should be able to see this field when you customize the screen from the tool box displaying all the fields or somewhere on the screen if the field is already in use. Be aware that once a field is used on the screen or grid it's caption/label can be something different than the actual field name. A field's name cannot be changed once it is defined…but the caption or label on the screen can.

E.g. RecordID could be displayed as 'Order Num' on the screen and at the same time 'Order #' on a grid.

The Sales Order Number is a field called 'RefNumber' on the table. but is displayed as 'S.O. #' on the screen.

Hope this helps!

Re: Using linked fields to relate Purchase Orders/Invoices/Credit Memos

smohyee — Fri, 20 Jan 2012 23:53:57 GMT

Ahhh, two very important points you made there that helped greatly, Valbon:

1) the RecordID and the QB Sales Order # are not the same field.

2) the RecordID, once created, cannot be changed.

The concern that led to the question was the possibility that users could change the Sales Order Number, and thus destroy the connection I create in Method between the PO and SO. But if changing the Sales Order Number from QB doesn't affect the RecordID, then it won't matter in the slightest.

You also raise the followup concern as to what is supposed to happen to the PO if the sales order changes. Good point, and I'll have to decide if it makes sense to design Method to ensure that the forms generated from a Sales Order are forced to continously match a Sales Orders info every time the sales order is modified.

Thinking about it now, actually, I think it would make sense to leave the PO/Invoice/Credit Memo unaffected if their origin Sales Order is later modified, because if a user had manually modified one of those forms without similarly modifying the origin SO, they would risk losing those modifications when the Sales Order was changed later. But, I could put a button on the sales order screen, offering the option of updating all the related forms that were already created, if thats what the user wants.

-Sam

EDIT: Valbon, I've just gone through the fields in the SalesOrder Table, and I couldn't find anything called "RecordID" or "SalesOrderNumber" or anything similar. I could understand the RecordID not being listed because it can't be edited, but what about the field storing the QB Sales Order #, which is changeable in QB?

Re: Using linked fields to relate Purchase Orders/Invoices/Credit Memos

Method_Valbon — Fri, 20 Jan 2012 22:16:09 GMT

Hi there,

Thanks for the post and all the details. Using a linked field would not work in your scenario because you don’t have an existing relationship between your Sales Orders or Purchase Orders.

For this to work you would have to have a field on the PO table to relate it the Sales Order, kind of like having a Customer drop down on every Invoice allows you to reach into the customer table for reference

Let’s walk through your example and see what we can do to help.

Step 1 – sales order is created and has a unique record ID (from Method) and SO # (from QB). It does not matter how this is created or who did it.

E.g. an order comes in for 5 iPhones and it is given record ID #50 (from Method) which is auto generated and cannot be changed...ever.

Step 2 – the sales order is converted to a PO which also has a unique record ID (from Method) and PO # (from QB). In addition, you as a designer using Method actions have carried forward the SO Record ID #50 to the new PO custom field called ‘ReatledSalesOrder’.

E.g. the order for 5 iPhones is converted to a PO for my vendor called Apple and ‘ReatledSalesOrder’ indicates it came from sales order #50

Step 3 – the sales order is changed from 5 iPhones to just 3 iPhones.

This is where we need some more details on what you would be doing next?

Modify the existing sales order (still #50) but just change the qty from 5 to 3. Then you can just update the qty on the PO as well.
Delete the old sales order and create a new one.
Mark the old sales order as closed or inactive and create a new one.
None of the above…I do something else.

You'll need to determine the next part of the work flow and accommodate. Please let us know and we'll provide some guidance.

Using linked fields to relate Purchase Orders/Invoices/Credit Memos

smohyee — Fri, 20 Jan 2012 18:20:34 GMT

In our business, every order starts with filling out a Sales Order. I'm setting up method so that all the different forms (except quotes) are generated based off the Sales Order - Invoices, Purchase Orders, Credit Memos, etc. (If you're wondering why you would related POs to a Sales Order, its because we're a drop shipping company, meaning each order we recieve results in us placing that exact order with another vendor).

Anywho, I'm trying to figure out the proper way to link these forms together, and I've settled upon adding a "RelatedSalesOrder" field to each relevant forms table. Here's my question: would making my new "RelatedSalesOrder" field a linked field help my design?

I could just create it as a normal field, but I can see that leading to problems. For example: Say I generate a PO from a Sales Order, but later something comes up and I have to change the Sales Order #. When I first generated the PO, it grabbed the original Sales Order # and stored it in it's "RelatedSalesOrder" field. But since the RelatedSalesOrder field of the PurchaseOrder table isn't linked to the RecordID field of the SalesOrder Table, when I later change the Sales Order Record ID, the PO field will not update its information accordingly, and the relationship between the SO and PO would be lost.

If I linked these two fields somehow, can I make it so the "RelatedSalesOrder" field updates its value automatically to match the value of the RecordID field of the Sales Order?