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 .

image

image


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

image

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:

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 )

Net result is:
image