Wednesday, February 20, 2019

Correct Revenue Recognition Saved Search that shows incorrect Amount when Summary Type = Sum is used

A Transaction Saved Search (Type = Revenue Recognition) will create a Cartesian product or duplicate lines when both Multi-Vendor feature is turned on and the Item field is used as a criteria / column. This is because by design, the system will join the two tables and Item to Vendor has a one-to-many relationship.

The degree of how many times the line will be duplicated is based on the number of Vendors on the Item Record. To illustrate:

Assume that you have Item: Test_Item with two Vendors: Vendor_1 and Vendor_2. Then, you create a Sales Order where a Revenue Recognition is used and Amount is $100. Then you create a Saved Search:

1.    Go to Transactions>Management>Saved Searches>New.

2.    Select Transaction.

3.    On the Criteria tab add Type is Revenue Commitment, Tax Line is False, Shipping Line is False and Item Fields: Name is Test_Item.

4.    On the Results tab add Item (Summary Type = Group) and Amount (Summary Type = Sum).

5.    Hit Preview or Save.

The results will show Amount = $200 instead of $100. This is because, as previously discussed, there are two Vendors on this Item Record. If there are 4 Vendors for this Item then the Amount will be $400.

The solution is to avoid using the Item Fields in the Criteria tab. Instead, use:

Formula (Numeric) = CASE WHEN {item} LIKE 'value' THEN 1 ELSE 0 END is equal to 1

In this case, you will need to replace 'value' with 'Test_Item'. This uses the same logic but prevents the use of the Item Fields and therefore works.

No comments:

Post a Comment