I recently attempted to answer a question about the various quantities held against inventory items with Field Service for Microsoft Dynamics CRM. In doing so I found that the circumstances when the quantity on order, on hand and allocated are effected may not be as obvious as I first thought. To better appreciate these columns, I decided to do some testing and document my results. This post gives a summary of my findings and therefore my understanding of when and how these values are increased / decreased.
I expected to see some unexpected behaviour! During this post I will highlight any points when inventory values did not behave as I imagined they would.
This might be a long post but one worth reviewing if you are having issues with inventory in Field Service and need to delve into the detail of how it operates. In this post I will cover;
- Inventory Adjustments
- Inventory Transfers
- Purchase Orders
- Purchase Order Receipts
- Using parts in work orders
First of all, a note about warehouses, in Field Service we have a concept of warehouses. These can be physical locations such as a store room or virtual warehouses. A common use for a virtual warehouse will be to define a warehouse as an engineer’s van. All stock consumed by the engineer can be associated with a warehouse, it will be common for that default warehouse to be assign to the van.
For the purpose of my tests I created exactly this situation. I have a main warehouse called “West Midlands Warehouse”. I also had a virtual warehouse that would be my pickup truck. I named this using the registration of the truck.
On my bookable resource record, I set the warehouse to my truck. You do this in the administration section of Field Service. Below you can see my warehouse field in the Field Service tab contains the name of my truck.
Products and Unit Groups
Before I look at stock transactions that can impact inventory items, a note about products might be appropriate. For my test I needed a product but I also wanted to look at how unit groups effect stock values. So I created a test product that I called “An Acme Widget”.
I gave this product a units group of ACME UNITS and selected the default unit as ONE. I’d previously created this simple unit group. I have shown my unit groups below. Here I created a concept of ONE being a single item and a box. In my example a box would contain 10 widgets. Obviously you could have more complicated unit groups. As it will be common for some products to be supplied (and used) in multiple carton sizes.
Spoiler alert! – I hadn’t used unit groups on inventory items before. They didn’t work as I expected!
Next, for any product to have inventory values you need to ensure it is set as an inventory product. Below you can see that in the Field Service tab on my product the Field Service product type has been set to “Inventory”. (Other options include non-inventory and service but I won’t discuss them today!)
Don’t forget that after you have created a product you will need to publish it.
Having published my product, I can then use the product inventory option from navigation to view the stock held in my warehouses.
It is worth knowing that when a product is first created no inventory records will exist. And therefore no records are shown against any warehouse. These only start to show as stock transactions occur.
Let’s first consider stock adjustments and their impact on inventory levels.
Using the Inventory adjustments option, I can increment or decrement the stock quantities for any inventory item. This will typically be done to correct stock levels (maybe following a stock take) or to set initial stock levels when implementing Field Service.
I decided I would book 10 boxes into my main warehouse. Using the inventory adjustments option, I created an adjustment for my main warehouse.
Then from the products option in the navigation I added a stock adjustment. This is when I found my first piece of unexpected behaviour! I created an adjustment for 10 boxes of my newly created product. As the items default unit group was “ONE” and I booked 10 boxes of 10 into stock, I expected my inventory value to become 100. But it didn’t. My quantity on hand was simply incremented by 10. This seemed strange at first until I started to realise how the stock by units is actually stored.
Some more testing allowed me to see that the unit seems to have no restriction in the stock adjustment option. At least not out of the box. In fact, you can pick any units not just the ones within the unit group associated to the product. See below that I booked in 10 boxes, 1 single and 12 “Bluetooth Headphones”! The “Bluetooth Headphones” were selected at random simply to show that unit groups have no bearing on stock adjustments.
After I had completed these adjustments I checked my product inventory to see the impact on the inventory quantities. I was surprised to see three lines! I had incorrectly expected to see one line giving the total value.
Opening one of the lines showed me that the system actually keeps one inventory record per unit group. Meaning I have 10 boxes, 1 single and randomly 12 headphones in stock.
Therefore, stocking of product is per warehouse / unit combination. That is logical enough but you can select any units not just the ones from the unit group associated with the product. I guess this might be a product feature. (or should I say bug!) You could however simply add some validation to the select of unit groups to prevent mistakes.
Also out of the box the view of inventory items does not show the units. You might want to add this to the associated view of product inventory. As I have now done. Once I’d altered my views the logic did make more sense!
Therefore, the inventory adjustment option simply increments the quantity on hand figure to the unit group and warehouse selected. (I say increment but don’t forget that you can reduce stock by entering a negative value.)
I next looked at inventory transfers. These are actually very similar to stock adjustments expect they decrease stock in one warehouse and increase it in another. In my test I decided to use this to transfer some stock to my truck.
So first I created a stock transfer header. Notice that my source warehouse is my main warehouse and my destination is my truck.
This time I selected the same product and I used “Box” as my units.
Having saved my transfer looking back at the stock inventory I can see that my box stock has increased in my van by 2 and reduced in my main warehouse by 2. The transfer has just effected the quantity on hand. (Note : The quantity available will also have been effected.)
A nice thing about the stock transfer is you MUST have the stock on hand to be able to transfer it. An error message will be given if you try to transfer stock between warehouses that you don’t have. The message is shown below. Maybe not the most user friendly dialog in the world but it works just fine.
At this point it might be worth mentioning the inventory journals entity. See below that I have done an advanced find on the inventory journal entry for all entries created today.
The results show that the inventory journal entry shows the adjustments made on any of the quantity fields. For example, my stock transfer effected the on hand quantity by taking 2 from my main warehouse and adding 2 to my truck.
The inventory journal entity includes some additional columns you might find useful when hunting down stocking issues. Including links to the work order, adjustment, receipt or purchase order product that resulted in the journal.
Next I looked at how purchase orders can effect the inventory quantities. To test purchase orders, I simply used the purchase order option to create an order and added a product. To understand how unit groups impacted orders I decided to order 1 box and 1 single of my test product. Notice that the view in purchase orders again doesn’t show the units. You might want to add the unit column!
One thing I was interested in was when the inventory values get updated. The answer is that the quantity on order is incremented when the purchase order is submitted to the supplier. Meaning that no values change when the order is in draft or approved statues.
My first purchase order was not linked to a work order. I then repeated the test with a second order but this time I decided to link to a work order. Once that order was created and submitted the quantity on order increased, as I expected. But other than that associating the purchase order to a work order had no other effect. I also added the product to the work order and that also had no effect.
Tip: You can link the purchase order to a work order at an order level or at a product level. I opted to link it at a product level.
Purchase Order Receipts
Next I received the stock I had added to my two purchase orders. Prior to entering the stock receipts my stock position was this;
Notice that 2 items are on order. One on a purchase order linked to a work order and the original one I ordered.
My first order contained my acme widget product twice. As the order was for one box and one single. Notice that the units aren’t shown on the purchase order receipt screen. This could be a problem! Units aren’t shown on any views but they can easily be added to standard CRM views should you wish to use them. But here the purchase order receipt screen cannot be changed. Meaning I wouldn’t know which line is for a box and which is for a single.
After my stock reciept had finished you can see that the quantity on order has been reduced and the quantity available and quantity on hand has increased.
Next I repeated this process and received stock against my purchase order linked to the work order.
Notice that this time when I receive the purchase order it says “Associate to work order” and gives my work order number. (And optionally a booking.)
This time the quantity on hand increased. But it also showed that this item was allocated to my work order. So the quantity available did not increase.
Completing a Work Order
Finally, I wanted to understand when the allocated amount would reduce. So I scheduled the work order.
In the mobile application I selected the scheduled work order and opened the work order product screen. I recorded that I had used one of my products.
I also changed the allocated field to Yes and selected the warehouse that contains the allocation. Now this could be confusing, in my opinion. As the allocated field defaults to “No”, so the engineer could forget to update it. Plus the warehouse for the allocation can differ from the one the engineer uses for physical stock.
To explain, my purchase order was received into my main warehouse. At this point it was allocated to the work order. So the allocation shows in the main warehouse.
But my engineer always uses inventory items that come from his van. So it will default to reducing the quantity on hand figures from the virtual warehouse associated with the engineer. (aka his van.)
If you decide to allocate stock to work orders like this, you might need to consider these complications and decide how to handle them.
Below you can see that my quantity allocated reduced when the product was marked as used on the work order. (FYI: The change happens as soon as the product is saved in the mobile app.) Also notice that I ended up with negative stock in my van! This is because in my example I didn’t transfer the stock to the van. Whilst this seemed confusing maybe in the real work it wouldn’t be so bad. As the engineer must have had to transfer the stock to his van to be able to use it!
Afterwards I did re-test stock transfers. If the quantity available is less than the quantity on hand are still allowed to transfer the quantity on hand amount into the van. Meaning the concept of stock being allocated in one warehouse but consumed from another will work.
I realise this is a long post but I hope it will help anyone trying to understand the complexities of unit groups and stock allocations in Field Service.
5 thoughts on “Field Service – Inventory Quantities”
Pingback: Field Service – User Guide | Microsoft Dynamics CRM and Unified Service Desk
If you order stock against a work order and the stock moves to the on-hand and allocated columns. What would you do if you ordered 2 items but only used 1 of them? As far as I can see, there’s no way to remove the unused item from the allocated column which means that you would slowly tie-up more and more stock that you couldn’t use.
Unfortunately you are right. Another situation I’ve seen is if the field engineer forgot to tick in the mobile app the product used comes from an allocation. Meaning the qty on hand is reduced but the allocation remains.
In the real world I suspect we’ll find several events that could happen like this to make the on order and allocated fields inaccurate.
On the product inventory form the fields are read-only but that could be changed. Maybe just for an admin user. So we could manually override the values.
In addition I imagine there will be a need to have a report (or something) that would help calculate what the values should be. And flag differences. When I have worked on ERP solutions it has been common to have a reset / re-calculate routine for these types of fields. Commonly I have seen this sort of adjust applied routinely at the time of a stock take.
You are right without some enhancements they could gradually drift. Out of the box I don’t know of an approach to reset them!
At the end of the day CRM is not an ERP solution. And that means features like purchasing, inventory and invoicing have some limitations. Unfortunately.
Thanks for the prompt response.
I too have seen the problem where the engineer forgets to flag the product as used, also where they forget to set the ‘allocated’ flag on the work order product line.
I think there are 2 possible solutions here; either create an adjustment process as you suggested or only order product without a work order association, receive them into warehouse stock, and manually add them to the work order. This way you could remove any allocated items by editing the work order products.
I am working on one opportunity when some parts will be consumed from stock and others might be ordered for specific installation work orders. Mixing these concepts and keeping accurate records will no doubt take some thought and customizations.
Another challenge to me is that the allocation is against the purchase order is received into. But the engineer will consume stock from another warehouse. Typically their van. This works but I feel I might end up coming up with a bespoke solution that moves the allocation as the stock moves. So that the available stock figures in each warehouse report sensible information.
Some fun problems. 🙂