[MNeedhamRyonet Contributions: 0Level 1☆]

I am trying to calcualte the total amount of an item sold per month. These items are sold as inventory items, part of assemblies and …. here is were it gets tricky, that assembly is also part of a kit/package. How can I, in a search capture all sales of the inventory item no matter where the item is sold. The assembly and inventory items I get easy but the portion that is sold in the kits i cannot seem to capture. Here is my current search Type
is any of Inventory Item, Kit/Package, Assembly/Bill of Materials

Transaction : Type
is any of Item Fulfillment, Invoice

Transaction : Status
is any of Item Fulfillment:Shipped, Invoice:Paid In Full, Invoice:Open

Transaction : Date
is within 9/28/2015 and 9/28/2015
This is a cached copy. Click here to see the original post.

[JCirocco Contributions: 0Level 1☆]

Actually if you use fulfillments only as an invoice of an Inventory Item also would have a fulfillment.

Are you looking to run the search for a single item at a time or get a list of items with all the sales?

If 1 item at a time could do invoices where item=”12345″ OR Member item=”12345″

If trying to run a list of items, use a case formula if Inventory item use item number and quantity, else use member item number and quantity * member quantity

[KCSF Bud Contributions: 0Level 1☆]

You might just boil it down to the accounting side – any time inventory moves it would be affecting an Other Current Asset account (or in our case it does). So use that account type as a criteria and then include any transactions that decrease inventory – for us that is Item Fulfillments and Assembly Builds. Then use summary criteria of Sum (use a formula to multiply by -1 if you want to show a positive quantity. Item fulfillments for kits will list the components of that kit in the search results. Key is to only show those that are posting to Inventory GL accounts.

We also use a custom field on item records that sources from the saved search, showing monthly inventory movement. Pretty helpful, also can do year to date, last year to date, etc.

[groupvertical Contributions: 0Level 1☆]

Originally posted by KCSF Bud

View Post

You might just boil it down to the accounting side – any time inventory moves it would be affecting an Other Current Asset account (or in our case it does). So use that account type as a criteria and then include any transactions that decrease inventory – for us that is Item Fulfillments and Assembly Builds. Then use summary criteria of Sum (use a formula to multiply by -1 if you want to show a positive quantity. Item fulfillments for kits will list the components of that kit in the search results. Key is to only show those that are posting to Inventory GL accounts.

We also use a custom field on item records that sources from the saved search, showing monthly inventory movement. Pretty helpful, also can do year to date, last year to date, etc.

KCSF – can you tell me how you set up this saved search and then how does the Inventory item know to only grab that matching SKU from the saved search? Id really appreciate it. Still trying to learn these dang saved searches

[KCSF Bud Contributions: 0Level 1☆]

Set up a saved search that uses a single Summary on the quantity field (using integer Formula field for {quantity} and probably Negate function as well if you want the fulfillments to show as positive numbers). If you were to view the results of that search it would just show a grand total of all of the quantities summed for all items, which is of course not particularly useful. Then set the Available Filter to Item, no need to show it in filter region. Now you can use those search results on a custom item field (ours is free-form text but decimal might work too). On that custom field, look at the Validation and Defaulting tab, it has a Search field. Select your search there. Then make sure the Store Value field is unchecked and probably want to set the display type to Inline Text. Netsuite will now automatically filter the search by the Available Filter on the search when you view the record, based on which item you are viewing.

Note that this only works when viewing or editing the item record, you can’t just go get the value for other saved searches or anything for wider reporting. To make sure you’ve done it right you can just spin off a test version of the search that is filtered to a single item. It would sum to the total that also shows on the item record and let you drill down to the source transactions.

We also have an ‘Inventory Movement’ tab on items that shows all ins and outs for that item, which ALWAYS sums to the current on-hand inventory. Very helpful for investigating issues. It sources from a similar saved search but a few more steps to set up.

Just PM or post back if you run into any issues, its been a while since this was set up so I might have forgotten something.

[Author]

Posts