BAQ help - get first

Epicor ERP 9
1

I am creating a query to get the first shipment date of a serial number - I am getting STK-CUS and MFG-CUS. I am using the SNTrans table. I displaying the part number, serialno, transdate, job and it linked to Customer table, and I am getting the CustID and Name.

I have excluded RMA’s -

When I run the query, it only gets one record.

Any suggestions

2

FWIW - In V8, using “First” and “Last” in BAQ’s wasn’t reliable.

What do you consider “the first record”? the one with the lowest TranNum? Earliest ship date?

3

This code should be close (off the cuff):

DEF VAR firstTranDate as DATE no-undo.
FOR EACH SNTrans fields (PartNum SerialNum TranDate JobNum) no-lock WHERE SNTrans.Company = cur-comp and SNTrans.PartNum = "XYZ" and SNTrans.SerialNum = "1234" and (SNTrans.TranType = "STK-CUS" or SNTrans.TranType = "MFG-CUS"),
	EACH Customer fields(Name CustID) no-lock WHERE Customer.Company = SNTrans.Company and Customer.CustNum = SNTrans.CustNum ORDER BY SNTrans.TranDate DESC.
	
assign firstTranDate = SNTrans.TranDate.
	
END.
4

I was hoping that it would pick the first record based on Serial number and date. We have a progress database and I am working with a BAQ.

5

Ah then without code, I’m not sure this can be done.
I use a uBAQ to update a calculated field with this code in GetList. I would additionally populate the Customer/ID with this code because the summary info is not robust in E9 BAQs.