I wrote a BAQ for our purchasing specialist that provides her with:
Part Number
Part Description
Part Inactive (y/n)
Oh Hand Quantity
Total Demand
PO Number (PO for ordering the part)
I am having trouble with the PO Number, it’s a “mandatory” field, and will only bring up results if there is a PO for that part number. I need all the part numbers to come up regardless if a PO is attached or not (quick search critieria being part num = inactive/active though)
the criteria works for inactive y/n , but I am having a hard time figuring out the PO Number bringing all results if null or active.
We sometimes have multiple PO numbers associated to one Part number, I want these all to be included in the search too as separate lines
Can someone help me finish out this BAQ? (I’m not the greatest at BAQ’s, self taught but I know a little but this BAQ is proven difficult. I tried making it update able and marking it as “read-only” but its grayed out and can’t be selected?
if you joined say Part with the PODetail it will show only parts that have a PO. You need to use LeftJoin between the two. But then you will have multiple rows with part if that part was on multiple PO’s…
It all depends of what you want this query to show.
For instance, showing the lastest PO number this part was purchased ? Then I would use a subquery with PODetail with grouping on Company and partnum, and a calculated aggregate field showing the most recent.
Join Part with this new subquesry via a Left join…should give you one record per part, with the latest PO it appeared on. and for the parts never purchased, empty PO number.
(sorry for the late reply, I wear a lot of hats around here and it gets quite busy for me)
I have added new columns that the purchaser would like to have added, and the report runs and will not yield any results (it wont stop spooling, it stops responding and I have to close Epicor) What am I doing wrong? (I’m not the greatest at creating connections/child/parent/joining etc.
for each Part no-lock , each PartWhse no-lock where (Part.Company = PartWhse.Company and Part.PartNum = PartWhse.PartNum ) , each PartMtl no-lock , each POHeader no-lock by Part.PartNum by Part.PartDescription by Part.InActive .
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[InActive] as [Part_InActive],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PODetail].[PONUM] as [PODetail_PONUM]
from Erp.Part as Part
inner join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company and (Part.PartNum = PartWhse.PartNum) and ( PartWhse.WarehouseCode = 'mfg')
left outer join Erp.PODetail as PODetail on
Part.Company = PODetail.Company and (Part.PartNum = PODetail.PartNum) and ( PODetail.OpenLine = true )
Look again at your screenshot… you DO have the outer join… outer join is basically the same as a “left join” but you have to make sure the arrow is pointed the right direction.
Tim - What do the symbols in the graphical display mean in E9?
In E10, the is for Inner Joins
and is for Left Joins (where all the records are returned from the lower numbered table, and only matching records from the highernumbered one)
I see both of those symbols AND arrows in Shelbie’s image.
And what does a dotted link (like her POHeader -> Part link) mean?
I’m aware that I have the outer join, as far as “inner” and “left” join there is not a button for these, the connections (dotted/solid) are when I drag the tables onto the diagram it either automatically gives a parent/child connection or I have to manually give the connection (which seems to be the dotted), I have the OUTER join ticked between Part and PO Header. As far as determining a left or inner join, there is not a button I am visually aware of like the below screenshot:
I’m a little late to this conversation but I think Calvin brings up some good points. What is your link between PO Header and Part? This could be a problematic link. I would think PODtl would be better because you could link via part number. Also I notice you have two parent tables for POHeader (Part and ParWhse) this could also be problematic. You should have only one directly linked parent table. These two things are most likely the cause of the unending spooling of the BAQ.
Part linked to PartWhse To PODetail To POHeader would probably be the route to get what you want.
Shelbie, when you create a link between two tables in the BAQ designer you’re creating an Inner join, meaning that the data has to appear in BOTH tables for it to show.
To make that link an Outer join (also called Left Outer Join or Left Join), select the join by highlighting it and check the box labeled Outer join.
This is the information I need
Part Inactive (Y/N)
Part Number
Part Description
On Hand Quantity
Demand (sales) qty
An open PO for that “part” number if avail (purchasing PO, not sales)
-Open PO’s
-Order Date
(I only want active PO’s to be returned and ones that DO not have a PO (active)
This is hard to explain I DO not want the “inactive” or PAST PO’s. Only current. but I want all parts returned whether they have a PO or not.
PartMtl.MtlPartNum - I need the part numbers that are used to make that finished goods part number (its in “where used”)
PartMtl.QtyPer - this is the purchasing qty? this is HOW MANY of the “where used” part numbers takes to reach a finished good (lets say 20 butt splices as a “bulk” goes into making ONE finished good. "20pk butt splices is 1/each) 20/1 ratio.
By “active” do you a PO which you expect to have future receipts? If so, then use the PODetail.OpenLine - this is true when the PO is first created, and is switched to false upon receiving complete, or manually closing the PO Line or the whole PO.
As for the PartMtl … you want this to only return parts that are on a BOM? Or all parts, regardless of whether they are on a BOM or not (like returning all parts regardless of being on a PO or not)?
If your Part table contained (the QOH and QOD aren’t really in the part table)
(Assuming open jobs for (5) of AS-1000 and (3) AS-1002)
PartNum
Desc
Type
Inactive
QOH
QOD
RM-0001
Widget Case
P
FALSE
1
8
RM-0002
Widget Harness
P
FALSE
0
5
RM-0003
Widget Harness, Hi Power
P
TRUE
0
0
RM-0004
Widget Harness, Hi Power, ROHS
P
FALSE
0
12
RM-0005
Flubber
P
FALSE
0
7
AS-1000
Widget Sub Assy
M
FALSE
0
5
AS-1001
Hi Power Widget Sub Assy
M
FALSE
0
0
AS-1002
ROHS, Hi Power Widget Sub Assy
M
FALSE
0
3
Your PartMtl had
PartNum
Seq
CompPN
QtyPer
AS-1000
10
RM-0001
1
AS-1000
20
RM-0002
2
AS-1001
10
RM-0001
4
AS-1001
20
RM-0003
4
AS-1002
10
RM-0001
4
AS-1002
20
RM-0004
4
and the only “active” PO (num 12345) was for P/N RM-0001, the result of the BAQ would be:
Inactive
PartNumber
Description
QOH
QOD
PO Num
Order Date
WU PN
FALSE
RM-0001
Widget Case
1
8
12345
5/29/2018
AS-1000
FALSE
RM-0001
Widget Case
1
8
12345
5/29/2018
AS-1001
FALSE
RM-0001
Widget Case
1
8
12345
5/29/2018
AS-1002
FALSE
RM-0002
Widget Harness
0
5
AS-1000
TRUE
RM-0003
Widget Harness, Hi Power
0
0
AS-1001
FALSE
RM-0004
Widget Harness, Hi Power, ROHS
0
12
AS-1002
FALSE
RM-0005
Flubber
0
7
FALSE
AS-1000
Widget Sub Assy
0
5
FALSE
AS-1001
Hi Power Widget Sub Assy
0
0
FALSE
AS-1002
ROHS, Hi Power Widget Sub Assy
0
3
RM-0001 appears 3 times because it is used on 3 assy’s
The RM-0001 lines are the only ones that have a PO num, as its the only part with an open PO
*P/N’s: FLUBBER, AS-1000, -1001, and -1002 show no were used, as they are not part of any BOM
I took PartMtl table off and substituted for PartBOM? I also replaced POHeader with PODetail
Here’s my chart.
Could you help me with the connections between these?
These are my display columns now:
I also have a quick search with part status and part type criteria.
Part.Inactive and Part.TypeCode
This way I ONLY want purchased “P” parts that are ACTIVE to show results (no other part numbers) our “purchased” are our bulk numbers, our bulk numbers should have BOM part numbers(if they do not, then returning blank) and I would like to have results for any active PO’s that have not been “Received” or “closed” (or return blank)
First off, you don’t want PartBOM. The help for that says:
PartBOM : * Indented Part Bill of Material. NOTE: This is a definition of a Temp-Table used to view the Part Structure. Data does not actually exist in this table.
Link Part to PartWhse
This should be an inner join (your “outer” check box should be unchecked.)
The link criteria should be:
Link Part to PODetail
This should be an outer join (your “outer” check box should be checked.) It is important that the arrow of the link be pointing towards the Part table. Not sure exactly how to do that in E9
The link criteria should be:
Link Part to PartMtl
This should be an inner join (your “outer” check box should be unchecked.) Make sure PartMtl.MtlPartNum is used (this is the component part of the BOM), PartMtl.PartNum is the parent part of the BOM (what you want to display)
The link criteria should be:
is for Inner Joins
is for Left Joins (where all the records are returned from the lower numbered table, and only matching records from the highernumbered one)
