Scenario
User needs to create a Transaction Saved Search for Work Orders and needs to show the Assembly Item (Main Line) and Component Items (Item Line) in the same row per Work Order. Using the Item field (field ID: {item}) pulls both the Assembly and Component lines in separate rows.
Solution
1.) Navigate to Lists > Search > Saved Searches > New
2.) Select Transaction
3.) Add the Criteria:
- Type = Work Order
4.) In the Results tab, add the following:
- Document Number | Summary Type: Group
- Formula (Text) | Summary Type: Maximum | Label: Assembly ItemFormula: CASE WHEN {mainline} = ‘*’ THEN {item} || ‘ (‘ || {quantity} || ‘)’ END
- Formula (HTML) | Summary Type: MaximumFormula: REPLACE(NS_CONCAT(CASE WHEN {mainline} != ‘‘ AND {iswip} = ‘F’ THEN {item} || ‘ (‘ || {quantity} || ‘)’ WHEN {mainline} != ‘‘ AND {linesequencenumber} > 1 AND {iswip} = ‘T’ THEN {item} || ‘ (‘ || {quantity} || ‘)’ END),’,’,'<br>’)
5.) Enter a Search Title, then click Save & Run
The Results page should show something like this:
Notes:
- If you export this as a CSV or XLS file, the <br> code will be exported.
- You can also modify the formula to show another Quantity field as necessary. Example, use {bomquantity} instead of {quantity} to show the value for the BOM Quantity column instead of the Quantity column.