Method Community

What I've noticed is that as line items from a sales order are invoiced, they are somehow marked so that the next time you convert an SO to invoice, those line items wont show up in the SalesOrder_CreateInvoice screen (the popup screen you get from clicking the "Create invoice" button)

However, after spending some hours examining the action lists and tables in these three screens, I can't find the action that marks a line item as invoiced. I assume the "Invoiced" field in the SalesOrderLine table is changed somewhere in this process.

I'm interested in finding and changing this feature so that all line items are listed and available to be invoiced, regaurdless of whether they were invoiced previously.

smohyee-

Take a look at the actions on the OnScreenLoad event for that screen (This is accessed by clicking the Advanced button).You will see that there is a condition in there that skips certain invoice line items from being added to the grid filter. If you remove that condition it should display all items.

-Michael

Smohyee-

This is one of my favorite technical topics for QB Method integration.  No joke, I'm really that interested.

There is a related process where the entire Order gets marked as closed.  I believe this actually happens in QB as it compares the Order QTY of all line items to the Invoiced QTY.  If the Invoice QTY is >= the Order Quantity, QB will mark the Order as "Invoiced" (I believe that's the boolean flag which will then control which orders are open versus closed.

I do not think there is an automatic flag for each individual line item, so for example in my "Open Line Items" grid, I do not have an easy way to hide line items that are fully invoiced.  Somewhat the reverse of your problem.

This becomes very important as we try to build screens to show open line items, partially shipped line items, or closed complete line items (or Orders) and how to we maintain logical and intuitive agreement with what QB might do automatically versus user interaction we might want to create, or Method automation.

I stopped short of creating a new boolean flag for each line item to maintain an open or closed status as I did not want to add additional logic if I could lean on QB or Method standard logic, but I think ultimately this will need to be done for me and I will be curious to know what you come up with.  (and I have a related problem).

Cheers,

James

First off, thanks Mike, that's what I was looking for. I've got to remember that "advanced" button for the screen, lots of nifty features to be found there =).

James:

I'm not sure how QB handles it, but Method seems to work similarly to what you're describing. If you look at the SalesOrder table synched with QB, there is a boolean field, "IsFullyInvoiced". It seems reasonable to assume they mark this based on comparing QTYs invoiced to QTYs on the Sales Order.

What's interesting is that if you do a partial invoice of a Sales Order in QB, then go back to create a second invoice from that Sales Order, QB will show you in the popup the QTY of each line item that you had previously invoiced. That display is not included in Method, but I think Method is doing the same thing in the background as QB:

Looking through the action list for the SalesOrder->Invoice screen, I see that the SalesOrderLineItem table has a "Invoiced" field, and that as QTYs of line items are invoiced, the QTY is stored in that field. A second invoice from the same SO doesn't overwrite that Invoiced value, it adds to it, in order to keep track of the total QTY invoiced so far for each line item. A relevant question I'm working on answering: where in Method is the action that changes the value to the "Invoiced" field? Answering that would lend some clarity to the process.

From what I understand of what you're trying to do, you can probably filter out fully invoiced line items from your "Open Line Items" grid by doing a comparison of the "Quantity" field and the "Invoiced" field within the SalesOrderLineItems table. If the number stored in "Invoiced" is >= the number in "Quantity", then that line item should be filtered out.

-Sam

Sam-

smohyee:

A relevant question I'm working on answering: where in Method is the action that changes the value to the "Invoiced" field? Answering that would lend some clarity to the process.

QuickBooks actually handles this. When you create an Invoice from a Sales Order the Invoice gets pushed to QB which updates the Sales Order QTY's in QB which then gets passed back to the Sales Order in Method.

If you do not have the sync engine running and create an Invoice from a Sales Order and then look at the remaining QTY's you will see that they have not been updated.

-Michael

Ditto to Michael.  

QB handles all the updates of the boolean "IsFullyInvoiced" AND the QTY's.  If this was done in Method it would be easy to fix or modify the behaviour, but since it happens automagically by QB, we have to be careful not to trip up the QB logic, get double counts etc.

Back to my original premise that additional functionality in this area might require a completely new set of Boolean status indicators and count accumulation fields as I do not think it is a good idea and probably impossible to mess with fields QB is using in the way that it uses these fields. 

Keep pluggin Sam, I think you're just a few steps behind me and hopefully will leapfrog ahead and blaze the trail.

There's gold in those relational database key fields and maybe you can tell me why my Invoice QTY's don't update to line items, sometimes.  I believe the Invoice line items carry a key field association to the Order Line items (Many to One) and of course the Order Line Item is related (many to one) to the Order Header.  I also suspect QB has a trigger (or maybe that's an Oracle term) on its data base to run a query to tally all the child Invoice line items to calculate the Invoiced QTY.  My updates don't work because either my relations are getting broken or the trigger's not running.  (Just guessing).

James

James / Method Team,

I've just completed an action set for the "Sales Order to Invoice" Conversion Screen that counts up the qty's invoiced on previously made related invoices, and enters that value in the "Invoiced" field for the sales order line item. I didn't mess with the "IsFullyInvoiced" field.

It seems to be working! 

Essentially, I set up Method to do what you're saying QB handles. Because I've been working on Method without regularly synching with QB, I haven't gotten a chance to see how synching might throw off counts like you're saying will happen. I'm concerned, but also hopeful that QB won't affect my Qty Invoiced system, because I'm using my own method for relating invoices and sales orders (via a "RelatedSalesOrder" field I created in the Invoice table), so QB shouldn't be able to tell which Invoices to count against "Qty Invoiced". Hopefully it doesn't start counting other, unrelated invoices!

Anyway, I just wanted to update the forum with my work in this area. Once I synch with QB again and test to see how QB's actions will affect my system, I'll post another update. If everything seems to work smoothly, I'll post more details on my setup.

Edit: Just so I remember this the next time I post here: the field used by Quickbooks to related Invoices and Sales Orders is the LinkToSalesOrder field. At least, that appears in the InvoiceLine Table, and is used by the action set in the SalesOrder to Invoice popup screen. 

Smoyee,

I'm really curious to know how this works out for you.  Did Method and QB allow you to update the Invoiced qty field?  I'm surprised that this isn't a system field that only QB can update.  What happens if QB also updates the field?

I haven't fully digested this, so I'm thinking out loud.

Michael also advised that there is a fix in the February edition that might solve some of the synching issues.

Regardless of the QB maintained Invoiced qty field, I'm really interested in how a "custom" maintained field might work since I think there is some custom functionality that might require the logic you are developing.

For example, we often ship line items 1 or 2 pieces short of the order quantity due to scrap losses on making castings to order.  In those cases we need to go back to the Sales Order Line and reduce the QTY to allow QB to change the Boolean Flag for the Sales Order to Invoiced.  For now, the only way a Sales Order Line is removed from our Open Orders Schedule is by having QB close the entire Sales Order which requires all line items to have Invoiced QTY >= Ordered QTY.  Eventually, I'd like an easier way to close out invidividual line items as they are shipped without going back to the Sales Order and reducing the order QTY or making sure all lines are closed out.

In general I try real hard to lean on the automatic background processing of QB or keep Method standard, but I think in this case the default functionality may always be inadequate and require customization.

I assume that there's an easy query to total the Invoiced line items to the related Sales Order line?

Thanks for the update.

James

On second thought, now I'm not sure why I'm using the Closed flag from the Sales Order header as I can see a line by line flag in QB.  I know I tried to have individual line status from the QB flags, not sure why I got stuck at the header level????

I know this thread is a bit aged, but I thought I would post here to add (hopefully something valuable) to the discussion.

I'm just now using the QB concept of Invoicing (family business, no tech until now, moving along...) and I'm in the middle of customizing our usage of it in Method. To handle our Sales Order process, I made our own custom Sales Order status that is manually managed by our office manager that handles our order process. Now with Invoices in play, I need a way to automatically "close" a Sales Order but - of course - it's not the way QB manages to close a Sales Order using quantities. Instead, it's by total invoiced amount. We do a couple things re: invoicing that shows why counting quantities invoiced doesn't actually tell us when something is completely invoiced. 1) We combine common products from diff SO line items onto a single Invoice line item and 2) we have a single sales order for construction projects with a single line item with qty of 1 and we progress bill off that amount during the project. For those reasons the QB concept of closing a sales order is basically erroneous for our business, hence the need to create our own concept. So QB's LinkToSalesOrder and automagic sales order closing is just something we ignore altogether.

Re: total amount invoiced...every time we click Save on an Invoice, I add up all the other Invoice total amounts tied to the Sales Order and automatically set our custom Sales Order status to 'Closed/Invoiced' if the amount is >= the Sales Order total amount. This actually eliminates what was previously a manual step for our office manager. Just one more thing I've been able to do by customizing Method.

Hope this is informative and helpful.