I have a BAQ listing job numbers, materials etc using jobMtl/JobAsmbl etc etc, I have placed a couple of buy to job Purchase orders for testing.
I added PORel as that has both JobNum and PONum and linked them to JobMtl by the job number but it just displays the PO number at the end of every line of material as it cannot link by the PartNum as its not available in PORel (for whatever reason)
I’m sure this is an easy fix (assumption!) for the smarter people in the room but I am wasting too much time trying to figure it out. Can someone assist?
Yes, I can get the Part number from other tables, the problem I am having is linking the PO number to the job number… When I link the two all I get is the PO and part number duplicated against all parts when I need it to only show to the buy direct PO as pictured.
And it looks like you’re inner joins are going to cause you problems. Being new, I’m assuming you haven’t learned about join types yet. Check out this post with the link to explain how the different join types work.
Thanks for that, had a good read through and think I understand it, However it still wont work. I tried changing the join type to all rows from POrel and POdetail and just get the same result. I removed everything that’s noise for now so I just want to see the job materials, if its direct material and show the PO number that is linked to that material…but it just repeats the PO number on all of the materials.
If you’re asking for help, you’re going to have to give us more than an outdated screen shot that doesn’t even show how you are joining the tables. Show us what you have now (including the join criteria) and walk us through why you have what you have.
Thanks, So you can see it shows the PO number on all rows where I only want it to show on the line it was actually purchased for (The only part ticked as purchase Direct on the job)
Ok, your join types are still messed up, but we’ll forget about that right now. If you’re ONLY looking for purcahases direct. Just make them all inner joins.
On the PO rel, you have a job number, and assembly sequence, and a another field called “JobSeq” which is going to be the mtlSeq. That’s what definitively links the PO to the Material line on the job.
also, from your screen shot, it looks like you just manually typed in “PartNum” on the POrel table part of the criteria? That’s not going to work, you should be getting a syntax error on that if that’s what you did.
Edit:-- well it doesn’t give you a syntax error, but it basically says partnum = partnum. Which means nothing.
Ahh yes I did, no error like you said but I have now removed it.
OK i have done as you said but now all I get is just that material line, where I still want to see all the materials, but a null return on any materials that have no direct PO raised. I hope that makes sense.
Thanks in advance for all of this by the way, I know it can be frustrating talking to a layman…I’m a buyer and forced into learning BAQs and dashboards to get the information I need…I’ve been waiting for a consultant to make this for me for far too long I decided to take it into my own hands.
This is where the join types come into play. This in SQL terms is a “left join” even though they renamed it in BAQ land. But set it to “All rows from JobMtl” and now you should see all of the material lines with blanks for the PO information.
Check out the tools user guide. There is a section on BAQs that is pretty good. There are case studies that walk you through step by step on making some BAQs. It’s not always going to tell you exactly why you do everything, 'cause that would take forever, but it at least shows you the basics of the tool and should at least get you to the point of asking intelligent questions. (knowing what you don’t know)
