1

Sorry a novice at Stack Overflow, so hope I'm doing this correctly.

I'm trying to make a saved search to find out which assemblies can be built by determining if all the components are in stock. I found a similar question by Garrett Penfield at NetSuite saved search formula to find the difference of two custom columns and was hoping that Garrett or others may be able to assist.

Thanks

Stephen

Stephen Davie
  • 23
  • 4

1 Answers1

2

To show the Buildable Quantity of all Assembly/Bill Of Materials items:

Create a Item Saved Search as follows:

  • Criteria (Standard) Tab
    • Type ANY OF Assembly/Bill Of Materials
    • Inventory Location ANY OF - None - and <Your Location>
    • Member Item Fields > Inventory Location ANY OF - None - and <Your Location>
    • Member Item Fields > Type ANY OF Assembly/Bill Of Materials and Inventory Item

Criteria tab selections

  • Criteria (Summary) Tab
    • Type: Minimum
    • Field: Formula (Numeric)
    • Formula: GREATEST(FLOOR(NVL({memberitem.locationquantityonhand},0)/{memberquantity}),0)
    • Greater than 0

Criteria tab (summary selections)

  • Results Tab
    • Internal ID (Summary type Group)
    • Name (Summary type Group)
    • Description (Summary type Group)
    • Formula (Numeric) (Summary type Minimum): GREATEST(FLOOR(NVL({memberitem.locationquantityonhand},0)/{memberquantity}),0)

Results tab selections

Example of results: Example of results

Notes

  • You may want to change locationquantityonhand to locationquantityavailable in the formulas used in both the Criteria (Summary) and Results tabs.
    • Doing so will change the result to only show how many assemblies can be built without using items that are already committed to other customer orders.
Kane Shaw
  • 314
  • 1
  • 8
  • Hi Kane, what a legend thanks a ton for all your help on this. – Stephen Davie Jun 15 '20 at 03:20
  • No problem! If your Assembly contains non-inventory items you will want to make a small change to ensure that they don't "zero out" the buildable quantity (because they aren't in stock.) Let me know if you need help to fix that. (Hint, just add into the Filter Criteria: Member Item > Type ANY OF Assembly/Bill of Materials, Inventory Item) – Kane Shaw Jun 15 '20 at 04:55
  • Thanks Kane, are you using NetSuite yourself for business, or are you a NetSuite developer. Our issue revolved around identified and building assembly items that were on back order, and producing a list of these that could be build if all components were in stock. I have commissioned a scrip to be written to facilitate this as it seemed to complicated for a saved search. What do you think? Cheers – Stephen Davie Jun 16 '20 at 05:25
  • Stephen, I use NetSuite at my workplace, but I am also our in house IT manager/Developer. I have built many NetSuite customizations for within my workplace, some of the larger ones being a native Australia Post integration (no middleware) and our own native Magento connector (no middleware.) What you've described may be possible with a saved search alone. If you haven't already had a script written, or if you aren't satisfied with it, perhaps we could get in contact and work out an approach that will benefit your business. – Kane Shaw Jun 16 '20 at 12:53
  • Stephen, please accept my answer on this question, then ask a NEW question specifically asking for a way to create a saved search to find the buildable quantity of assembly items that are back-ordered. I have figured it out and will post the answer on the new question! – Kane Shaw Jun 17 '20 at 04:13
  • Hi Kane, wow sounds like you have has tons of experience with some interesting and complex NetSuite customisations. I very much appreciate your help on answering this question. I will as suggested ask another question. I am a first time novice on stack so not sure what if anything I need to do to finish this question off. I thought I had last time, but couldn't give you my 10 out of 10, as I didn't have any cred.. We recently implemented NetSuite, BigCommerce and HubSpot to our Business BrandNet based in Canberra Australia, doing some exciting things :<) – Stephen Davie Jun 17 '20 at 19:30
  • @StephenDavie Definitely up there with the experience :) You should be able to mark this question as answered by clicking the Tick symbol on the left of my answer. Also, I posted an answer on your other question! – Kane Shaw Jun 17 '20 at 23:28
  • thanks Kane, I have just seen your latest will go and try this one now. Appreciate the help. – Stephen Davie Jun 19 '20 at 00:48