How to retrieve part lots issued to jobs?

Hi,

We are in the process of implementing lot tracking.

We have a custom kitting label that is affixed to issued parts that hit the production floor.

I’ve been unable to find a decent way to display what lot(s) the parts came from on this label. Seems to me like JobTracker would have be a great place to find this, but no…

I thought I had it when I joined PartTran to JobMtl with partnum and jobnum, but I’ve found that if there are ‘extra’ transactions (unisssues/reissues) these results are included in the query. I can’t think of a way to filter these out as they are STK-MTL transactions, so using the PartTran table is probably a no go.

https://i.imgur.com/oOFiKtq.png

Does anyone have any ideas for displaying lot for issued parts on a specific job? I’m hoping I’m missing something obvious…

You are in the right place. Since you are in E9 I would do a updatable baq on getlist to summarize the lots used by job/material and put them all into a calculated character field.

Something like this. This has not been tested, but I just did a routine like it for lots to jobs for tariffs this month, so it should be close. If you want to eliminate lots that were entirely unissued it will take a bit more logic, but doable.

for each ttResults.

	For first JobMtl where JobMtl.Company = cur-comp and JobMtl.SysRowID = ttResults.JobMtl_SysRowID no-lock.
		
		message "in Job Mtl " + ttResults.JobMtl_PartNum + "/" + ttResults.PartTran_LotNum.

			For Each PartTran where PartTran.Company = cur-comp and JobMtl.JobNum = PartTran.JobNum and JobMtl.AssemblySeq = PartTran.AssemblySeq and 
							PartTran.JobSeq = JobMtl.MtlSeq and PartTran.JobSeqType = PartTran.JobSeqType and PartTran.TranDate = PartTran.TranDate no-lock.
					
						If Index(ttResults.Calculated_Lots,string(PartTran.LotNum)) = 0 Then	
							ttResults.Calculated_Lots = ttResults.Calculated_Lots + string(PartTran.LotNum).

						message " partlot mtl is " + PartTran.PartNum + " in "  + string(PartTran.LotNum).
					
			End.
						
	End.
End.	

Thank you, Greg.

I’m a little confused about the updatable suggestion, but I’ll see if I can make this work with my baq.

Trying to customize this software always makes me wonder whether or not I’m an idiot, never being able to understand the suggestions simply confirms it for me :slight_smile:

This is something I had never heard of either until I learned it from a consultant. If you are comfortable writing ABL then you can do a lot with this.

check your baq as updatable and on the update tab check advanced baq only.
image

click Advanced BPM processing. The bpm code will go on the post processing of GetList

The action is execute ABL code and you put your calculation code there. The message lines get written into the appserver log to help with debugging. I use a log viewer that can open the log while it is in use.

If you get stuck post a screenshot so someone can help you.

Either I’m worse off than I thought, or I didn’t explain myself well…or both.

So I just have a simple query:
https://i.imgur.com/91W0FWh.png

And an even simpler Crystal report:
https://i.imgur.com/dL1xCX5.png

ABL might as well be latin to me…so no luck there. I wouldn’t have guessed creating BPM’s and updatable queries is a solution to what I’m doing, but I’m obviously no expert.

I think this one might get added to the ‘next time we hire a consultant’ pile. I do greatly appreciate the help, sorry it will probably be in vain.

This would not work for Crystal anyway, but you could return all of the lots in the baq and then do a summary by job/mtlseq in the report.
There will be multiple lots a lot of the time when you are looking at lot tracked inventory being issued to jobs.

Would a summary table allow me to somehow filter out the transactions that I don’t want?

That’s been my main stumbling block even while trying to brainstorm a solution. No matter what parameters I would use, all the ‘garbage’ transactions will still satisfy as they are all ‘legit’ transactions and I can’t imagine any logic that will differentiate one STK-MFG from another.

This issue might only arise on a small percentage of our jobs, not often we need to issue, un-issue, re-issue…but I know it does happen.

My efforts may be best directed towards convincing those requesting that they really don’t need this info on the label.

Is there a reason why you would not print the label with the issue transaction? Don’t know if you would have to modify the label to suit your cause, but that is the place to do it.

Charlie

Thanks for the idea, Charlie. That might just work for me!