Linking Resource Groups and Part Transactions

Kinetic ERP
, , ,
1

Does anyone know a way to join tables so that I can do a sum of PartTran.TranQty and group this by both PartNum and ResourceGroupID? I was thinking if I joined JopOpDtl to PartTran on JobNum that I could do this, but I am getting results that are different than if I just group by the PartNum. Grouping by only PartNum seems to give me the correct TranQty sum, when I add ResourceGroupID to the group by the numbers are sometimes double what they should be. I just can’t figure out why that would be the case.

2

What is the goal here? You might need to link to PartOpDtl instead.

3

Nate, I was asked to sum up all MFG-STK transaction quantities for each part and to include the ResourceGroup on that as well. I can’t find a good way to do that, and it may not be possible. Will take a look at the PartOpDtl table as well. Appreciate the response.

4

Unless you only use one operation with one resource on all of your jobs, adding in RG will multiply the results by the number of RG on the job.

1 Like
5

Start by filtering your Part Tran table for this type. That will help reduce the results. I think what you want is possible. Make sure you know exactly what you are looking for. If you start with the Part tables then you are only looking at the part master data, if you start in the job tables you will have duplicated results for every job that part was created for. Are you trying to get a list of all the resource groups used for a part?

6

Something like this should
RGPartTrans.baq (40.1 KB)
do:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[SearchWord] as [Part_SearchWord],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[RGs].[Calculated_RGs] as [Calculated_RGs],
	(sum(PartTran.TranQty)) as [Calculated_TranQtySum]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
	and Part.PartNum = PartRev.PartNum
inner join Erp.PartTran as PartTran on 
	PartRev.Company = PartTran.Company
	and PartRev.PartNum = PartTran.PartNum
	and PartRev.RevisionNum = PartTran.RevisionNum
	and ( PartTran.TranType = 'MFG-STK'  and PartTran.TranQty > 0  )

group by [Part].[PartNum],
	[Part].[SearchWord],
	[PartRev].[RevisionNum],
	[RGs].[Calculated_RGs]

Edit one thing to watch out for are the auto joins made between PartRev and PartOpr, PartOpr and PartOpDtl. Two of the auto joins are on AltMethod and ProcessMfgID. Make sure to remove these fields from the joins. That is, unless you have the modules those fields require. I hate that we still have to manually fix these joins, but this should get you closer.

7

Hey Nate, sorry I’ve been busy the past week. What you did is what I was looking for but it ended up having the same problem all of the queries I’ve tried have had. Do you know if there is a way to see more specifically how many part transactions were performed by a specific Resource Group? What I’m really looking for is how many of each child part has been issued by each resource group. So columns would be something like: RG, Parent PartNum, Child PartNum, IssuedQty, and UOM.

I have tried to use both IssuedQty and Part Transactions with a TranType of STK-MTL (I originally asked about MFG-STK transactions but that was a mix up on my part). Is it impossible to associate an Issued part to a specific resource group?

Thanks for the time you’ve put into your responses.

8

I am not sure I understand. Part transactions can happen all over the place, and they are not directly tied to a resource group. Tell us more about the real-world problem you are trying to solve again.

To help with this, open the Inventory Transfer form, and go to Actions > Transaction Log… Run this for any part you want to see the history of. Look back and see what data is available in your system. Scroll across the column list. You should see userID and job/asm/operation. You may be able to use one of these fields to link a user to a resource group, or an operation to a resource group. Is that what you want? If so, does your data support the linking (does job/asm/op exist in the data?)

Strictly speaking, resource groups do not make transactions. People do. :slight_smile:

2 Likes
9

This is what I was wanting to figure out. Management just wants to be able to see parts that are being issued over a given time frame. I can’t say I completely understand why they want to see resource groups, but it was asked for. Thank you again, will have to see if they want to look into using UserIDs to try and tie transactions to resource groups.

10

I believe you could follow the table chain like this:

PartTran → JobMtl by JobNum=JobNum and JobSeq=MtlSeq

Then you link to the operation through the RelatedOperation from JobMtl to get the resource group.

What do you think?

11

Josh,

I’ve tried various combinations of using job tables and part tables. I do not believe I’ve tried this specifically so will give it a shot when I have a moment and report back. Appreciate the thought!

12

Josh, I put a query together and it has had a lot of trouble running. I’m not sure why, but I’d probably write this off as not worth the time.