Saved Search to Display Sales Order and Linked Drop Shipped Purchase Order #, Date, Amount and linked Invoice #, Invoice date and Invoice Amount

1. Navigate to Reports > Saved Searches > All Saved Searches > New
2. Select Transaction link
3. Set the following under Criteria tab > Standard Subtab:

• Type = Sales Order
• Shipping Line  = False
• Tax Line = False
• Applying Link Type is any of Drop Shipment, Order Bill/Invoice

4. Under Results Tab > Columns subtab, add following fields:

• Date
• Type
• Number || Summary Type = Group || Custom Label = Sales Order
• Name  || Custom Label = Customer Name
• Amount || Summary Type = Sum ||Custom Label = Sales Order Amount

•         Formula (Text) || Formula :CASE WHEN {applyinglinktype} = 'Drop Shipment' THEN {applyingtransaction} ELSE ' ' END || Custom Label = Drop Ship Purchase Order Number

 

•          Formula (Text) || Formula : CASE WHEN {applyinglinktype} = 'Drop Shipment' THEN TO_CHAR({applyingtransaction.trandate}, 'YYYY-MM-DD') ELSE ' ' END || Custom Label = Drop Ship Purchase Order Date

 

•         Formula (Text) || Formula : CASE WHEN {applyinglinktype} = 'Drop Shipment' THEN to_char({applyingtransaction.amount},'$9,999.99') ELSE ' ' END || Custom Label = Drop Ship Purchase Order Amount

 

 

•         Formula (Text) || Formula :  CASE WHEN {applyinglinktype} = 'Order Bill/Invoice' THEN {applyingtransaction} ELSE ' ' END|| Custom Label  = Invoice Number

 

•         Formula (Text) || Formula : CASE WHEN {applyinglinktype} = 'Order Bill/Invoice' THEN TO_CHAR({applyingtransaction.trandate}, 'YYYY-MM-DD') ELSE ' ' END || Custom Label =  Invoice Date

 

 

•         Formula (Text) || Formula : CASE WHEN {applyinglinktype} = 'Order Bill/Invoice' THEN to_char({applyingtransaction.amount},'$9,999.99') ELSE ' ' END || Custom Label = Invoice Amount

    5. Enter a Search Title

    6. Click Save and Run