Method Community

I am creating some logic on the Sales Order screen to automatically calculate pricing based on the line item Quantity.  I am using Text Change events on both the Item field and the Quantity field.  The idea is to update all of the pricing data when the Item changes and then calculate the pricing whenever the Quantity changes.  I want the Action Sequences for both fields in the grid to share the same set of Item specific data, so I have put all the values into Shared Results.

When I attempted to use the Shared Results in a Conditional Statement, it appears that the Conditional Statement cannot directly address Shared Results.

One method might be to import all the Shared Results into Action Results before starting the conditional logic.  I think that would work but I have a bunch of statements to put Action Results into Shared Results in one cell of the grid, to then pull them all back into Action Results in another cell, it just doesn't seem very efficient.

Or, I could update the Item Specific data only in the Quantity cell, but I didn't want to make a bunch of redundant Item lookups when only the Quantity is changing.  I suppose pulling the Shared Results into the Action Result space of the Action Sequence would be more efficient than looking up the values each time the quantity changes.

Is there a better way to pass data between Action Sequences occuring in the same Grid, Row than export values to Shared Results then importing them into Action Results for each value shared amongst action sequences in the same row of a Grid?

Thanks,

James

Hi James,

From what I am reading, you are trying to calculate and update the price whenever the item or Qty columns on the grid change. Where are you putting the calculated price?

To me it could be a simple as adding actions to the text change even of the item dropdown list to do “basic math” and then “Enter Value Into Field On Screen” for the grid column’s row if you want it to display. Once you have this set up on text change for the item column, I would use the copy actions feature to copy them to the Qty column but use the lose focus event here instead.

To answer some of your other questions, you have the right idea that action results are only available within the action set they are assigned. To share them you would need to assign them to a shared result and then use the ‘get value from shared result’ action to pull them in to a new action set. Using action results and shared results may not necessarily be more efficient than looking up the table values each time. It really depends on how your action sets are set up and what they are doing.

If I’ve miss understood what you’re looking to do please let me know.

Valbon,

Yes, that's a good idea, but the calculations are based on a bunch of values stored on the Item record that are not displayed on the screen, so I'm a bit stuck retrieving the values.  I basically have a fixed dollar amount pricing at several different levels, per item, based on quantity.  So, I'm stuck using boolean logic to choose the right price level based on Quantity (which I will then place into the Price value on the screen which is the standard QB Price field which should carry all through the Method/Quickbooks integration). 

I don't use a percentage or other repeatable calculation model to discount (or in our case, escalate the price), and I allow the user to create completely custom pricing for quantity breaks like 1 To 3, 4 To 9, 10 To 24, 25 to 99, etc. to 500.

Since the pricing information is unique per Item, I am updating the values based on the Text Change event of Item, and then trying to perform the lookup logic on the Text Change of QTY.  I've got the Item Text Change event working but my QTY text change event actions are not running.  To avoid potential data integrity errrors, I have the Item actions resetting the quantity back to 0 so the user cannot change the item without recalculating the QTY based lookup, which I'm afraid may be kicking off the text change event of QTY prematurely, or creating a race condition.  But, there is a good chance I have created another problem in my QTY text change actions.

I think your suggestion to use the Lose Focus event might be the key to solving my problems and has some really clever implications for avoiding unintentional cascading of actions.

These are some really good ideas, now I think I just need to clean up my logic, it must have an obvious flaw somewhere....

Cheers,

James

 James,

Glad to see my advice helped you get on your way. I really do think you’re better off using lose focus when it comes to columns that are number values.
 
Where are you storing these price levels? I’m not sure how your customization is set up but you might be able to avoid the all the conditional statements. The Retrieve value from table action is powerful stuff.

Tell me if this makes sense. If you had a new table with 4 fields called Item, RangeMin, RangeMax, CustomPrice, each time you changed the Qty on your grid, you retrieve value (CustomPrice) from the table using the item you just selected where the Qty from screen is >=RangeMin AND <=RangeMax. Instead of storing it in an action result...just enter it directly on the screen using the same action. This way you can have a nice little screen to view and edit these custom prices at any time without changing your actions.

If I am way off...just say so and I’ll get back to what I was doing before this post.

Valbon,

I think your design would work and would be more clever but I might tend more to the "flat file" structure.

Our pricing is a Base Price stored as the Sales Price, plus a Base Quantity and then tiers of pricing that have adders or penalties for being a smaller quantity than the base price.  For example the base price is $10.00 for a base quantity of 100.  We then have a standard "penalty" schedule, although I want this custom, or process dependent in the future.  So, I am storing a price value for 1 To 3 pieces, 4 to 9 pieces, 10 to 24 pieces, and so on as custom fields on the Item record.  For an order QTY of 3, the 1 To 3 price would be valid an in this case the price would be $25.00 each.  It is almost like a setup charge of $50.00 divided by the tier quantity to result in a per piece price.  It is a bit strange but has been in use for so many years.  This one "business rule" has caused more problems attempting to use a "shrink wrap" solution than anything else and is one of only two use cases that really prevent us from using QB out of the box.  But, as with many things we'll be able to model it exactly how we want with Method.

So, IF the Item is a NonInventory Item, and IF the Order QTY is less than the Base Quantity, the lookup goes and finds the right price as stored in the Item table.

One of the benefits of storing the price levels "flat" on the Item record instead of relational (as would be more correct) is that it is easy for me to export the Items and adjust pricing en masse, then import them.  Once the price levels become relational structures, I might have problems, (well now to think about it, it would be easy, wouldn't it, since the Item FullName is the key value?).

I am also considering just using Show Message or Show Screen in Popup to alert the user (just myself and one other person) that the Order Quantity is below the Base Quantity and display the pricing tiers for manual entry.  By involving the user, we can either reinforce that all Item records need some kind of Base Quantity or make sure we are deliberately applying the higher price to our customer and agree with the penalty.

Thanks so much for considering how to model this, using the Where Clause is much more elegant, but I might keep it a bit simple, if not requiring more Actions to accomplish for now.  I've also got the issue of how to print a Quotation with all of these pricing tiers and get it to fit on a form.  I think I have a solution for that by using a "Price Adder" page to the cover page of the Quotation.

Oh, and I tested the Lose Focus event and it does work much better on the number value field.  Or, I should say I could not get Text Change to work on the number value field and Lose Focus works just fine.

Thanks again for thinking about my use cases, I'm relieved to know I'm not heading down the wrong track, or at least not too far in the wrong direction.  You've saved me a bunch of time already.

Cheers,

James