[lewjen2006 Contributions: 0Level 1☆]

I am trying to build a report that shows the quantity of items purchased from a vendor. Along with that, I want to show qty sold in the same period. I can’t seem to find a report template that allows both qty purchased and sold. Can anyone point me in the right direction? Thank you.
This is a cached copy. Click here to see the original post.

[mmarcelo Contributions: 0Level 1☆]

You may create a Transaction Saved Search to display both the quantity purchased and sold within a specific period. Here are the steps:

1. Go to Lists > Search > Saved Searches > New.

2. Choose Transaction.

3. On the Criteria tab > Standard subtab > Add Filters:

– Type = Sales Order and Purchase Order

– Main Line = False

– Tax Line = False

– Shipping Line = False

– COGS = False

4. On the Results tab > Columns subtab > Add Fields:

– Item | Summary Type = Group

– Formula (Numeric) | Summary Type = Sum | Custom Label = PO Total Qty

— Formula = case when {type}= ‘Purchase Order’ and {mainname} = ” then {quantity} else 0 end

*Please note that the Vendor Name in the formula must be the exact name of the Vendor in the transaction. It is case-sensitive.

– Formula (Numeric) | Summary Type = Sum | Custom Label = SO Total Qty

— Formula = case when {type}= ‘Sales Order’ then {quantity} else 0 end

– Formula (Text) | Custom Label = PO Vendor

— Formula = case when {type}= ‘Purchase Order’ then {mainname} else null end

– Formula (Text) | Custom Label = SO Customer

— Formula = case when {type}= ‘Sales Order’ then {mainname} else null end

*Fields may be added/removed as necessary.

5. On the Available Filters tab > Add Filter:

– Date | Show in Filter Region = Yes

6. Name your Saved Search.

7. Click Save & Run.

[lewjen2006 Contributions: 0Level 1☆]

That is awesome! Thanks!

[Author]

Posts