Method Community

Hopefully someone can help me, and quickly!  I'm at my wits end...

So in very simple terms, my client charges minimums if the QTY x Unit Price do not equal the set minimum.  Additionally different clients have different rates and different minimums for each item. I have created a table in Method that lists all the “billing exceptions” as they call them.  I’m trying to get this to calculate in Method in the invoice screen.

 What I’ve done is set up a loop to go through the “Billing Exceptions” table to match the customer & item number and then grab the custom rate and minimum (if applicable).  This is all done on the text change to the item field in the line item grid.  So it looks to be working correctly until I click the “Update” button, and then the Amount field changes to 0, regardless of what is in the Qty or Rate fields.

 I have set up “fake” Qty and rate fields (suggested by Danny), and hidden the real ones, so that the client sees the numbers the way they want to, but in the background it should be calculating correctly.  I thought this would fix the issue I am having.  I’ve done a calculation on the Amount field, when it loses focus, where it takes the value from the Amount field and divides it by the “fake” rate field to come up with the “real” qty, and saved the “fake” unit price to the “real” unit price, but the Amount field is still returning a value of ZERO.  I set up extra fields on the invoice to save the numbers that I need for the calculation, and am using the values in these fields, and they are showing to be the correct numbers, but no matter what I seem to do, the Amount field STILL returns a value of ZERO!

 Is there any way someone can tell what I’m doing wrong from what I’ve explained? (crossing fingers!)  

I’m hoping it’s just something simple I’m missing – I’m supposed to deliver this to a client tomorrow morning, so I am crossing my fingers (again) that someone can answer this first thing in the am.

Thanks so much!

Karen Dellaripa

Karen,

I do something similiar, although different.  That makes a lot of sense, Eh?

All of my pricing is driven by the Item data and I have a quantity based pricing structure applied to each item. This is independent of the Customer, but it must be calculated for each line item added to an Order or Invoice.

When the Item is changed, (Text Change Event), I go out to the Items table and get all the information required to calculate the quantity driven price and store it in local variables.

But, I don't perform any pricing calculations until the user enters a Quantity.  The key point here is that numeric fields must be triggered by (Lose Focus) events, not (Text Change).  Danny helped me with this tip.

I'm not sure, but I think this might help you.  By triggering recalculations on the user input to QTY, you can always keep your calculations up to date with potential user changes.  I don't keep a hidden Quantity or Amount.  You'll have to calculate the Amount in the background to implement your rules to set the price.

The other complication is that you cannot change the QB Amount field.  It is ALWAYS calculated by QB as QTY X Price.  So, for example if your customer wants 5 of something, and that must revert to a line item minimum total, then you have to convert the order to a QTY of 1 times the Minimum Price to get the minimum line item Amount Total you want.

I do something else with my Orders and Invoices that might help you.  We apply a Surcharge to each line item based on the Total Weight of the Line Item.  After a line item is entered, and then the Grid is updated a new link is available in the grid called "SUR".  When the user clicks SUR a New Line is added for the Item Surcharge.

You may want to keep the ordered quantity of the Item accurate even though you are applying some type of minimum line or order pricing and it might be better to create a new billable line for the amount override.  Upon user input of the Quantity you could calculate whether the Minimum Amount override should be performed and provide a message to the user or set a Checkbox Flag in the Grid.  The user clicks the grid link and the price for the line item is zeroed out, but a new line is added for QTY 1 X Order Minimum = Minimum Amount.

This brings introduces another concept that might be helpful.  In my system all Items of Type Non-Inventory and Inventory are Shippable, meaning they would print out on the Invoice AND Packing List.  For Non Shippable line items, for example things like Surcharges, etc., I set those Items to type Other Charge.

Although a bit advanced it is fairly easy to control which Item Types print on reports such that you can show all Items on an Invoice but only show Shippable items on a packing list.

There isn't a logical connection between lines in an Invoice, but QB and Method will always print the lines in the order they were created or shown in the Grid since the default order is by RecordID or the hidden physical record number.

Not sure any of that will really help, but maybe generates some ideas.

Cheers,

James

Hi Karen,

Is there a reason you are looping through the grid?  I would suggest using "Retrieve value from table", unless there's a specific reason for using the loop.  Also, can you tell us what values are in the actual (hidden) QTY and Rate fields?  Calculations are done in the background to determine the Amount from these two fields (QTY x Rate).  If either of them is blank or zero, the Amount field will calculate to zero.  

If you're really stuck, I would suggest booking some consulting time.  Trying to do this over the forums might take some time, and considering your time crunch, it may be a better option.  Let us know, and we can put you in touch with a consultant.

- Adam

Oh, and maybe a shorter suggestion might be to avoid doing any event based logic or calculations on the QB Amount field.  QB will ALWAYS revert this field to QTY X PRICE.  I avoid this field completely in all logic as QB must calculate it and then it get's updated upon the refresh to Method.  You won't see the refresh of some fields in Method upon an Update or Save, sometimes you have to reselect the Invoice record completely.  This shouldn't be true for the Amount field, but I am pretty sure the Amount must come from QB doing the calculation, and can't be updated in Method alone.  This may be the only field that operates this way as even the Invoice refNumber can now be updated in Method alone without a round trip to QB, which was an awesome change by the Method team some time ago.

Adam,

I called support at 9:15 and left a message but noone has called me back yet!!!!!

I am not looping through the grid... I am looping through a table to retrieve values.

James, thanks for your suggestions, I will take a look at them now.

Karen,

Did you mean support or consulting?  Consulting is done by appointment only on a first come first serve basis.  

- Adam

Adam,

I left a message for paid support.  Not sure where that falls, but I called at 9:15 this morning and have not had a call back yet!

Karen,

As paid support (consulting) is by appointment only, there will be a delay in getting back to requests for consulting, as its likely our consultants are already booked into consulting sessions.  A consultant will get back to you as soon as they can.  You can also choose to reach out to another MSP for assistance.

- Adam

Karen,

If you need a quick fix, I would have your Actions update the QTY to 1, and adjust the Price to the Order Minimum upon finding the exception record in your Item/Customer table.

You can have your Action Sequence automatically append text to the Item Description to include the actual Item Quantity and a note about the price override.

If you are attempting to calculate the Amount instead of letting QB do it, I'm 95% sure that will not work and you must provide QB a QTY and Price that will Net to the price you need.  Alternatively you could leave the QTY alone and calculate the per Item Price on the fly such that the result would be the minimum Amount you want.

I'm 99% sure you can't mess with the Amount value directly and must let QB do it.

Best Regards,

James