Saturday, February 2, 2019

Set a Constant Value to Either X or Y Variable in a Report Formula

Since it is currently not possible to a value in the report formula converting Units of Measure or set a constant value to either x or y variable (see Enhancement Requests 126702 and 155072) we can use a custom field with a constant value instead.

In the steps below the report to customize is a Physical Inventory Worksheet report.

1. Create a custom item field which will contain the constant value

   • Go to Setup > Customization > Item Fields > New
   • Enter Name, like Conversion Factor
   • Set Type to Decimal Number
   • Mark Store Value = T
   • Under Applies To tab, mark Inventory Item box (mark other boxes as required by the report you customize)
   • Under Validation & Defaulting tab, set Default Value to any number
   • Save

2. Mass Update all item records to populate the custom field with the constant value

   • Go to List > Mass Update > Mass Updates
   • Under General Updates > Items, select Inventory Item
   • Under Mass Update Fields tab, mark the box next to the custom field and make sure that there is a value
   • Preview
   • Perform Update

3. After the mass update, you can now customize the report and add the custom field which will represent the constant value in the report formula

   • Go to Reports > Inventory/Items > Inventory/ Items> Physical Inventory Worksheet > Customize
   • Expand Inventory Item folder and select Conversion factor
   • Under Edit Columns, click Add Formula Field button
   • Set Formula Type to Multiply x*y
   • Select On Hand in X variable
   • Select the name of the custom field in Y variable
   • Preview

Note: You can apply the steps above for other record types by using the applicable custom field type, like a custom entity field for customer driven reports, etc. But if the field type is a custom transaction column field, Mass Update will not work. Customer may opt to import via CSV instead.


No comments:

Post a Comment