This is probably an easy question for most and I most likely have made this harder than it is.
What I would like to do is a list of Part Numbers on Order. I would like the SO number, the Part Number (could be multiple part numbers per order) and any jobs that are open for that part number. FYI: We DO NOT link all our jobs to our Sales Orders. Also included in this report would be Qty On-Hand if available.
I’ve tried making the Sales Info a SubQuery and Job Info as a separate SubQuery with Part Info being the TopLevel.
I am not an extremely Technical person - so please, no Tech Talk please
This is the logic I’m trying to accomplish:
Part Number
If job list JN
If SO list SO
List Qty On-Han
Hope this makes sense to someone - Thank you for any information you can provide!
Inevitably you’ll run into a part having multiple open orders or open jobs. Now we enter the classic debate of “why can’t you just put it all on one row per part?”
So, there are a couple options:
Concatenate the multiples separated by commas - this gets technical; there are posts here on the forum. Good for dumping to Excel, for example. Not my preference, but has its place.
A publish/subscribe dashboard
a. One grid has part numbers and maybe columns for how many open jobs/orders for each part but no details (no order numbers, no job numbers). Put OH qty here, too.
b. A second grid (from a separate BAQ) shows a list of open orders and dates and customers and other goodness for just that one part number - it dynamically changes if you click a different part in the first grid.
c. Third grid like the second one - open jobs for just that part.
Must have read my mind - this is how this all started. I was making a Dashboard to do this. But I couldn’t figure out how to enter a part number and have all 3 grids pull information for that part. That is why I thought I was going to have to only use one BAQ.
The examples are odd with the gauge thing and all that.
Basic idea is this:
Create 3 BAQs [I’ll explain more at the end]
a. Part numbers, part description, Qty OH and not much else
b. Open sales order lines (part number, customer name, qty on order, qty shipped already, etc. to your heart’s content)
c. Open jobs (part number, due date, qty, etc.)
Create dashboard; start with just 1a
a. Publish the part number field from THIS BAQ
In the same dashboard, New > Query > pick BAQ from 1b
a. Subscribe this BAQ’s part number TO the published part number from 2a
b. It will come in as a tab, but that’s silly for something small. Drag the tab to be a split screen with the first BAQ (one on the left half and one on the right half, for example). Also, you may need to refresh just this pane - and only because you just now created it.
Test this by clicking different part numbers in the first grid. The second grid should instantly change and show order lines for the newly-clicked part number
Repeat step 3, using the BAQ from 1c.
====================
More about the BAQs:
For the orders and jobs, filter out the ones you know are useless to you (closed ones, possibly unfirm jobs, ones from other sites or companies)
But the part number filtering is done in the dashboard. You don’t need to exclude any part numbers here
There is a way to use parameters, but let’s not for your first pub/sub dashboard
Some data is not in a single table (qty shipped already, I think is not); you may have to sum over a subquery for that. Don’t get bogged down with that just yet. Just get the basics working first.
Thank you so much…looks like I was missing pcs here and there!
I’ll give this a go and let you know how it works out. I already have the 3 BAQ’s it was just the way to get all 3 to filter on a specific Part Number. I’m not sure what the “subscribe” is but I will look it up as you suggested. I believe that is the part I’m missing!
