Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Automatically sum order products? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not sure if this is an easy customization or not, but it would be very helpful if it existed.

    When making a quote, or an order, or an invoice, if we make it entirely in CRM, it will automatically update the total cost in the header.  However, the problem is that we're importing a lot of data from the back-office which is in NAV, and I have to import it as two separate steps (salesorder and salesorderdetail).  Apparently doing it this way does NOT update the header, and they either have a price of $0, or more commonly, no amount at all.  I tried to create a workflow that monitored Order Products, but the problem is that the Total Amount field isn't edit-able, and therefore it won't let me select that for the workflow.

    Essentially, what I need is something that automatically updates the total amounts from each line item, whenever a new one is added, quantities adjusted, etc. (so basically, whenever an Ordrer Product is created, modified, or deleted.)  Same would go with quotes and invoices, though we are less likely to need it for invoices.

    Is there something similar to QuickPrice (which someone on this forum generously gave me) that can be used for this purpose?

    Thanks

    Wednesday, January 23, 2013 9:16 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Off the top of my head, there is not a supported way.

    My recommendation.  On the migration, use the MSMQ and have a step to close the order after you have imported all order details. 

    You would need to create a scribe package that will read the Order and order details and put it in a message in the MSMQ.  Your second package will then read the messages from the Queue and then write it to CRM.  By doing this, you will have multiple threads going into CRM. 

    For the package from NAV, I would create my package to pull half the orders to go the the Queue and publish it as an integration in Scribe Console.  Then I would alter that package in Workbench to pull the other half of orders.  You can have the published run at the same time as the other one in Workbench.  This will give you 2 threads from Nav.

    Depending on your Scribe License, you could have up to 63 threads going into CRM.  The number of threads will depend on your hardware. 

    • Proposed as answer by tmorrisy Friday, January 25, 2013 9:47 PM
    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    Friday, January 25, 2013 4:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A workaround, but maybe one that would fit your purpose quite well: integrate freight lines as Order Products at the required value, but set Quantity to 0, so CRM calculates the line value as zero and therefore does not affect product totals. Hopefully users will accept this is merely there to give them visibility of the multiple shipments and their cost, and that the zero quantity does not mean "FOC" (whereas the alternative would be a quantity of 1 and set discount = -1 x cost, so net = 0, but this seems to imply FOC to me)

    Use your integration to sum all freight lines to order level and copy this to the "freight amount" in CRM as well so grand total is correct

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    • Unmarked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    Monday, January 28, 2013 4:29 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not sure if this is an easy customization or not, but it would be very helpful if it existed.

    When making a quote, or an order, or an invoice, if we make it entirely in CRM, it will automatically update the total cost in the header.  However, the problem is that we're importing a lot of data from the back-office which is in NAV, and I have to import it as two separate steps (salesorder and salesorderdetail).  Apparently doing it this way does NOT update the header, and they either have a price of $0, or more commonly, no amount at all.  I tried to create a workflow that monitored Order Products, but the problem is that the Total Amount field isn't edit-able, and therefore it won't let me select that for the workflow.

    Essentially, what I need is something that automatically updates the total amounts from each line item, whenever a new one is added, quantities adjusted, etc. (so basically, whenever an Ordrer Product is created, modified, or deleted.)  Same would go with quotes and invoices, though we are less likely to need it for invoices.

    Is there something similar to QuickPrice (which someone on this forum generously gave me) that can be used for this purpose?

    Thanks

    Hi,

    I've imported lots of quotes, orders and invoices, and never encountered this problem. Upon importing products, the head (quote, order, invoice) total amount should be updated. But in this case, try to set the total amount of the head object when importing it.

    Thursday, January 24, 2013 7:21 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    The problem is that often times there's more than one product.  And they are completely separate entities.  In NAV, you have SalesHeader and SalesLine, in CRM you have SalesOrder and SalesOrderDetail.

    So I basically do SalesHeader -> SalesOrder and SalesLine -> SalesOrderDetail.  The second one uses the order number to make sure it's going to the right SalesOrder in CRM.

    Doing it that way, it's not really possible to set the total amount of the header up front.  If there is only one Order Product (SalesLine), it would be doable, I could just set the total to the line total.  But like I said, most of the time that's not the case.  And I experimented with it a bit, doing it that way resulted in the total only being of the first or last product, not a sum of them.

    Thursday, January 24, 2013 2:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    What are you using to integrate the orders from Nav to CRM?
    Thursday, January 24, 2013 2:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Scribe Insight.
    Thursday, January 24, 2013 3:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In your integration, is this a one time update for each order or can your integration update products on the order after the order is created (example: new order detail is added 2 days after the order is created)?

    Are you using the CRM adapter or an odbc connection to CRM?

    Thursday, January 24, 2013 3:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In regards to the first question, I'm doing both.

    All of the archived orders, I created a one-time integration that we'd run right before "going live", to get past sales data loaded.  Those won't be updated at all, since they're archived/completed/closed orders.

    But for open orders, yes, it will update any changes.  I'm having it monitor open orders so if you add or remove a line item, that change should be reflected in CRM.

    And I'm using the actual CRM 2011 adapter.

    Thursday, January 24, 2013 3:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The total amount field should update if you add your order details throught Scribe's adapter.

    You mentioned thought that "total cost in the header".  Is this a custom field or did you rename "total amount"? 

    If total cost is a custom field, then I would think that you might have some javascript updating that field with the value from total amount.  If this is the case, then I would ask why the extra field and not use the out of the box field total amount.

    Thursday, January 24, 2013 4:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The header has a pre-existing field called Total Amount.

    Interesting.  I tried making a manual addition (as in, I made a one-time DTS file that just manually added a line item, not based on anything else), and the total DID update as it should.

    So... I'm curious why it won't update when I run a batch of them.

    Thursday, January 24, 2013 4:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think you have narrowed it down to your scribe package.

    How are your steps built?

    Thursday, January 24, 2013 5:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Oh, you know what it is?

    When orders are imported using Scribe, they're locked (read-only) so that nobody can modify them.  Which is what we want.

    But apparently, CRM won't refresh the totals since the order's locked and therefore it would have no reason to update them.  Do you happen to know if there's any internal controls in CRM that would have it not update the order totals if it's a closed order?

    I suppose as a temporary workaround, I could have Scribe put in all our archived orders as "open" and fully modifiable, then put the products in, and then run through them in Scribe and set them all to closed.  But that's a waste of steps if there's an easier way, maybe some CRM config option I'm missing?

    More specifically, it works as long as the state code is 0 (new)

    If it's anything else, CRM won't update the totals.  I just tried it in Scribe - setting the state code to 0 and adding the line items... the totals now work perfectly as they should.

    So what I need is some way to force CRM into totaling the line items even with closed orders, is that possible?

    Thursday, January 24, 2013 5:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Off the top of my head, there is not a supported way.

    My recommendation.  On the migration, use the MSMQ and have a step to close the order after you have imported all order details. 

    You would need to create a scribe package that will read the Order and order details and put it in a message in the MSMQ.  Your second package will then read the messages from the Queue and then write it to CRM.  By doing this, you will have multiple threads going into CRM. 

    For the package from NAV, I would create my package to pull half the orders to go the the Queue and publish it as an integration in Scribe Console.  Then I would alter that package in Workbench to pull the other half of orders.  You can have the published run at the same time as the other one in Workbench.  This will give you 2 threads from Nav.

    Depending on your Scribe License, you could have up to 63 threads going into CRM.  The number of threads will depend on your hardware. 

    • Proposed as answer by tmorrisy Friday, January 25, 2013 9:47 PM
    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    Friday, January 25, 2013 4:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Alright, it works fine if I do it that way.

    I just added a step at the end that closes them once the order details are added - and it's initially set to a new order so CRM will sum them up properly.

    Just wondering, does CRM have anything for shipping/freight charges besides just the "Freight amount"?  The reason I ask is that some of our NAV orders have multiple FREIGHT line items, due to one large order having multiple shipments.  I could have my integration process total them all, but we'd like to be able to see the cost of each shipment, if any, which isn't conducive to doing it that way.  If we put Freight as a line item, it won't be a freight charge, but be totaled like a regular item.

    Monday, January 28, 2013 3:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A workaround, but maybe one that would fit your purpose quite well: integrate freight lines as Order Products at the required value, but set Quantity to 0, so CRM calculates the line value as zero and therefore does not affect product totals. Hopefully users will accept this is merely there to give them visibility of the multiple shipments and their cost, and that the zero quantity does not mean "FOC" (whereas the alternative would be a quantity of 1 and set discount = -1 x cost, so net = 0, but this seems to imply FOC to me)

    Use your integration to sum all freight lines to order level and copy this to the "freight amount" in CRM as well so grand total is correct

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    • Unmarked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    • Marked as answer by drfsupercenter Tuesday, January 29, 2013 3:48 PM
    Monday, January 28, 2013 4:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ah, I didn't realize you were allowed to set the quantity to zero.  That would work fine, I suppose.  Thanks.
    Monday, January 28, 2013 5:01 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Also note that "Quantity" on these sales entities can only be zero or positive in CRM, out of the box.

    It can be useful to customise the field to allow negative numbers if you want to be able to integrate credits as "negative orders", especially if the credit actually means returning an item to stock (or to write off).  Either way it is one (or a hundred) fewer that the customer actually bought and paid for, so if you do sales reports on volume as well as revenue you may want to bear that in mind. This way you would do the credit as -10 x price of (positive) $99 = (credit) amount of -$990. The alternative would be 10 x -$99 which ends up as a double count of the 10 sold, although the revenue balances out - but this means the "average price" is also badly skewed for this products and/or customer.

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Tuesday, January 29, 2013 3:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the note... I still think it's funny how you can put a quantity of zero out of the box, I can't picture any real-world situation when you would have an order of 0 parts and actually want it to print like that.  But just as a math workaround, it'll prove useful.
    Tuesday, January 29, 2013 4:38 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Zero quantity can be useful in businesses where they want to show an "opt-out", either to remind the customer of an option they could have had (and the amazing price they can buy it for!) or to ensure compliance or risk management - for example if the customer opts to not buy an extended warranty / support contract / insurance, or does not want to buy a charger / cable / carry case / second key or whatever.

    It can be a kind of a "waiver line" to say - "remember - you said you definitely did not want this, so don't come crying to us when it turns out you needed one".

    "No frills" airlines are good at adding these sorts of lines to order confirmations, I am sure there are other industries which have similar practices.

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Tuesday, January 29, 2013 4:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Something I noticed... when any of our orders have a price of zero (e.g. notes that are in NAV as line items), I get "An error occurred during pricing of a detail line item."  From what I've read, that's a common thing for zero-priced items... but is there any way to suppress that notification? It makes me worry that something is actually wrong with the order, when in reality it's not.

    --EDIT--

    The weird thing is that some orders do it and others don't.  All of the ones with a $0 price also have quantity of 0.

    Wednesday, February 6, 2013 4:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Where and when do you see the error? In NAV, CRM, or the connector?

    I've not noticed that problem with 0 value Order detail items, and I don't have a reference system with the connector to check at the moment. If it's purely CRM I may be able to reproduce.

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Wednesday, February 6, 2013 4:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yeah it's purely in CRM.

    In fact, that one's not even zero value, just that the final amount is since there's a quantity of 0.  Not sure what the error refers to, then.  That last column under Products is basically copied from NAV's line number, so that we can sort them in the proper order.

    Wednesday, February 6, 2013 4:47 PM