BAQ Part to Buyer

Kinetic ERP
1

Hello,

I am creating a BAQ that needs to connect the Part to the person who purchased it. I have Part but trouble is starting when I am trying to find the Buyer.

Thank you.

2

in the BAQ it would be the following tables.

Name of the PurAgent will be the buyers name

Part
PartPlant
PurAgent

Granted if you are looking for the PO information you will need use

PO
PurAgent

1 Like
3

I did run into an issue. I have duplicate Parts. I am not sure how to get rid of them.

select 
	[PartOpr].[PartNum] as [PartOpr_PartNum],
	[PartOpr].[SubContract] as [PartOpr_SubContract],
	[PurAgent].[Name] as [PurAgent_Name],
	[PartPlant].[Plant] as [PartPlant_Plant]
from Erp.PartOpr as PartOpr
inner join Erp.Part as Part on 
	PartOpr.Company = Part.Company
	and PartOpr.PartNum = Part.PartNum
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum
inner join Erp.PurAgent as PurAgent on 
	PurAgent.Company = PartPlant.Company
	and PurAgent.BuyerID = PartPlant.BuyerID
where (PartOpr.SubContract = True)

image
image1478×932 31.8 KB

4

PartOpr table is partnum, rev, OprSeq

take a look at those fields as well.

What are you needing from the PartOpr table?

5

Whether or not the parts have Subcontracts.

6

ahh a subquery will help that then. have the subquery return a list of parts that have a subcontract

7

I am not sure I did it correctly. Still returning duplicate Part lines.:

image
image1478×818 31.4 KB

8

You need to add another subquery to the BAQ. Where the arrow is in the below photo. End query will look similar.

image
image1492×864 179 KB

Add the PartOpr table and the criteria

image
image1460×808 148 KB

In the display fields select partnum (note the group by check box is selected)
and create a calculated field like below

image
image1785×837 143 KB

Change Subquery Options to be inner SubQuery

image
image848×497 55.2 KB

add the subquery to your toplevel query

Make sure you main query type is set to TopLevel on SubQuery Options tab

image
image909×533 51.6 KB

9

If any of that is not automatic, please add to this idea:

10

Hello,

Thank you for taking the time to make a guide. I am stuck at the part where you say to add the subquery to the toplevel query. How do I do that?

11

The new icon in the first picture. Give it a click.

12

I have two SubQueries here.:

Name:SubQuery1
Type: InnerSubQuery

Name: SubQuery2
Type: TopLevel

SubQuery2 is completely empty of tables.

13

Here is what I have.

SubConParts.baq (24.7 KB)

14

Ok I managed to get a bit further. My results come out something like this though:

image

Updated: SubConParts.baq (42.4 KB)

15

Add fields from Part

That just means your Part doesn’t have an operation.

16

If the report is only items in subcontract, then you can make your join be an inner join; matching rows from SubQuery1 to Part as the join type.

17

I tried all the joins and got Severity: Error, Text: Bad SQL statement.

18 
select 
	[SubQuery1].[Calculated_sub] as [Calculated_sub],
	[SubQuery1].[PartOpr_PartNum] as [PartOpr_PartNum],
	[PartPlant].[Plant] as [PartPlant_Plant],
	[PurAgent].[Name] as [PurAgent_Name],
	[Part].[Company] as [Part_Company]
from  (select 
	(CASE WHEN (COUNT(*)>0) THEN 1 ELSE 0 END) as [Calculated_sub],
	[PartOpr].[PartNum] as [PartOpr_PartNum]
from Erp.PartOpr as PartOpr
where (PartOpr.SubContract = True)
group by [PartOpr].[PartNum])  as SubQuery1
inner join Erp.Part as Part on 
	SubQuery1.PartOpr_PartNum = Part.PartNum
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum
inner join Erp.PurAgent as PurAgent on 
	PartPlant.Company = PurAgent.Company
	and PartPlant.BuyerID = PurAgent.BuyerID
19

I also selected a column from Part but nothing changed.

20

Looking for help still. Thank you!