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