Baq quick search - po number

(Shelbie Taylor) #1

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?

Please and thank you.

(Pierre Hogue) #2

How did you construct your query ?

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.

(Calvin Krusen) #3

You need an outer join between the Part and PO tables. That will allow Part records with no matching PO to be returned.

For Yes, No, Both … I make a calculated field that returns a char string like

if sampleTable.boolField = true Then “YB” Else “NB”

Then in the Report, Dashboard or search, use the “contains” comparison (or is it “matches” ?) to the calculated field. Entering :

  • “Y” will only return records where sampleTable.boolField was true
  • “N” will only return records where sampleTable.boolField was false
  • “B” will only return records where sampleTable.boolField was true or false

edit: (you might be able to even leave that calc field blank to return both)

(Shelbie Taylor) #4

I used the join and it worked! Thank you!

(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 .



(Calvin Krusen) #5

I think you are missing link criteria for the links between

Part <- POHead
PartWhse <- PartMtl
PartMtl <- POHead

P.S. - Other than the Company field, I don’t know how you can link Part and POHeader tables

(Calvin Krusen) #6

This is from E10, but does what you want (I think)


Ignore the criteria I have in PartWhse table as that is just limit my results to one warehouse.

The criteria in PODetail is OpenLine = true

Link definitions:

And the resulting Query Phrase:

	[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 )

Net result is:

(Shelbie Taylor) #7

I do not have the “inner join, left join” etc?

Hmm this is what I have:

(Tim Shoemaker) #8

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.

(Calvin Krusen) #9

Tim - What do the symbols in the graphical display mean in E9?

In E10, the image is for Inner Joins
and image 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?

(Tim Shoemaker) #10

I wish i could remember E9 symbols… all I remember is that you only had 2 choices… inner and left joins.

(Shelbie Taylor) #11

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:


(Calvin Krusen) #12

As far as E9 is concerned an Outer join, Left Outer Join, and Left join, are all the same thing

Edit: added a diagram

(Jarrad Layne) #13

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.

(Ernie Lowell) #14

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.

(Shelbie Taylor) #15

Sorry I am not good at BAQ’s.

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.

(Calvin Krusen) #16

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

That what you’re looking for?

(Shelbie Taylor) #17

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)

I hope this makes sense?

(Calvin Krusen) #18

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:

Add criteria to the PODetail table to only return PO’s that are open (yet to be received)

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:

Here’s my Output

  1. P/N AN-0003 appears twice because there are two open PO’s for it
  2. P/N AN-0005 appears twice even though it is only on one BOM, because it appears twice in that BOM (that’s why I added the MtlSeq field)