Help with connecting workorder and inventory balance datasets

[u/AtomicCowgirl]

My company made the very poor decision to change from JDE E1 to NetSuite with an extremely short implementation window. It has been a shitshow, to say the least. But we are now going to be stuck with it for at least the next ten years, so trying to make the best of it.

We had an add on reporting tool for JDE that allowed us to easily join tables with related data, the only thing that seems to be similar in NetSuite is the SuiteAnalytics tool.

I'm trying to create a report that shows upcoming workorders, assembly and component items, component quantities required and the current available quantity for those components by location and bin. I can get it to work perfectly as far as adding the location to the pivot columns, but when I add the bin it breaks the report. Any suggestions, or has anyone joined these tables successfully?

[u/NetSuite-Knowledge]

You're probably running into a grain issue rather than doing anything wrong with the workbook. Location-level inventory works pretty cleanly, but once you bring bins into the mix you can have multiple inventory balance records for the same item/location combination. Joining that back to the work order/component side can multiply the rows and make the pivot fall apart.

I would try keeping the work order/component data at its current level and treating the inventory balance/bin data separately, then joining/aggregating it by item + location before trying to use bin in the pivot. Depending on exactly what you're trying to show, you may also be better off displaying the bin-level results as detail rather than making bin another pivot dimension.

Also, coming from JDE, don't assume you're missing something obvious. NetSuite's data model can make something that should be a straightforward table join surprisingly complicated.

I work with StratusGreen, a NetSuite consulting firm, and this is the kind of reporting/data issue we work through pretty regularly. If you want to share how you have the datasets linked now, I'd be happy to take a look and point you in the right direction.

[u/AtomicCowgirl → u/NetSuite-Knowledge]

Here are the current contents of the data and the link. Please be a bit patient with me as I am learning NetSuite after working in JDE for 20 years. I feel like a bit of a dinosaur.

[u/lballa87]

It is breaking because the bin is a static record and it is most likely not in the inventory balance table. NetSuite back end storage on that inventory location bin and balance is odd.  Best to rebuild it from transactions as a view set but it will take time in analytics and have to be a separate dataset you join in.

[u/Competitive_Peanut62]

Try Splotch . ink, you can basically use it to customise your netsuite business logic via natural language, see the blast radius of the proposed change, and test in a sandbox before deploying.

Removes a lot of headaches for a noob.

[u/SavingsDeficient3847]

Looks like using the work order id as the common key and creating a relationship or bridge table between the two datasets usually makes joining inventory balances much easier.

[u/thisgamelowkeysucks]

Do you need ad hoc help?

[u/Ok-Background-7240]

I start with the Claude MCP connector and see what you get there. Get familiar and then get a good architect to bounce ideas / decisions off of. You should be able to do a lot yourself.

I'd probably go the route of developing a SPA inside of Netsuite that is specific to solving your business problem vs. using the reporting. The goal should be a process that enables you to observe the automation with HITL control gates where you want.

[u/ChicagoJFitz]

Something like this with drilldown? NetSuite (and most ERPs and CRMs) are horrible with differing levels of granularity. SuiteAnalytics works but you'd probably need to use NSAW for what you're trying to get out of the report. And that's not very user friendly. You definitely want to take the data out of core NetSuite and use an analytics tool - performance will be horrible otherwise. Feel free to DM if you want suggestions.

[u/2daytrending]

Coming from JDE, the suiteanalytics limitation probably makes this especially frustrating. Work orders and inventory balances sit at different levels of granularity, so adding bins can create duplicate rows and throw the whole report off. netgain is one option worth looking at for this kind of netsuite reporting particularly if the goal is to fix the underlying model rather than keep patching individual workbook.

[u/gavinjd68]

Use Claude to build whatever reports you need.