Scenario
If the item in a transaction (e.g. Sales Order) is expressed in another unit of measure, other than the base unit), upon creation of the Transaction Saved Search, the Quantity column would still be displayed in base units.
There are fields in the Results tab of the search called Quantity in Transaction Units along with the Units field. Adding these fields to the search will pull the quantity and units used in the transaction.
To be able to pull up the Base Unit from the item record, we need to use the Item joining fields. However, there is no joining field for the "Base Unit". This field is also not available in an Item Saved Search. Trying to use {item.baseunit} and {baseunit} in a Formula (Text) field will result in an error.
This is a known system limitation and is currently logged as Enhancement 170893 (Inventory Activity Detail report : Request to add Base Unit as an available column in the the report). Although the enhancement says "Inventory Activity Detail report", this is also applicable to Saved Searches.
Workaround
I ran some tests and was able to come up with a possible alternate solution for this. This will require some basic customization, which is not really my area of expertise, but I'll try to explain the steps as I did them.
Basically, the idea is to create a Custom Item Field which would have the same value of the Base Unit field in the Item Record.
So for this, we would need to create the following:
- Custom Item Field
- Workflow that would automatically populate the Custom Item Field
Step 1: Create a Custom Item Field
- Go to Customization > Lists, Records, & Fields > Item Fields > New
- Populate the following:Label = Base Unit (or whatever you want to call this field)
- Type = Free Form Text
- Store Value = T
- Applies To tab = select the item types you want this field to be applied to
Step 2: Create a Workflow
1. Go to Customization > Workflow > Workflows > New
2. Enter a Name for the Workflow
3. Record Type = Item
4. Sub Types = select Assembly/Bill of Materials and Inventory Part (or just Inventory Part if you do not use Assemblies)
5. Release Status = Released
6. Keep Instance and History = Always
7. Enable Logging = T
8. Initiation = Event Based
9. Event Definition = mark both On Create and On View or Update
10. Save the workflow
11. Click State 1, then add a New Action
12. Select Set Field Value
13. Under the Parameters section, set the following:
- Field: Base Unit (Custom field created in Step 1)
- Value: Formula = {baseunit}
14. Save the Set Field Value Action
Once this is done, try and update a field in an existing item record and it should automatically update the custom field you created with the value from the Base Unit field.
To be able to update all the existing items, you can either:
- Perform a mass update (this will trigger the workflow), OR
- Set the Workflow to a Scheduled, instead of Event Based (you would also need to create a saved search for ALL items you want to update here)
Once the Custom Item Field is populated, then you can now use field in your Transaction Saved Search to show the Base Unit of the item. You can find it in the Item Fields... joining fields.
-----
If you find this information useful, let us know by reacting or commenting on this post!