Revise Report for New Tables

[scott_selph]

Need to revise this report to change where quantity is retrieved due to new DB structure with new tables and fields. Any help would be greatly appreciated. Thanks in advance!

Current report is identifying if an Operation is closed (ProductionComplete=True), and if true, gets the ujmoClockDateTime. Then gets the quantity based on the date/time.

Grouping at 3 levels:
Jobs.jmpCustomerOrganizationID
Jobs.jmpJobID
PartBins.imbPartRevisionID

REPORT PREVIEW

REPORT DESIGN

CURRENT DB STRUCTURE:

{@ClockDT}:

If {JobOperations.jmoProductionComplete} = TRUE then
{JobOperations.ujmoClockDateTime}

It then identifies the quantity based upon the last time value (max) of the ujmoClockDateTime.

{@LastClkDateDetal}:

WhilePrintingRecords;
If {JobOperations.UJMOCLOCKDATETIME} = Maximum({@ClockDT}, {Jobs.jmpJobID}) then
stringVar LastClkQty := totext({JobOperations.jmoQuantityComplete},0)

NEW DB STRUCTURE:
Our database structure has changed due to a software update. The quantity is now in new table with multiple times and multiple quantities for the same JobID and JobOperationID, where before there was only one (1) quantity. I need to return the TimeCardLines.lmlGoodQuantity when jmoProductionComplete} = TRUE, but my problem is getting the quantity based upon {TimecardLines.lmlJobID} and {TimecardLines.lmlJobOperationID}.

{@ClockDTNEW}:

If {JobOperations.jmoProductionComplete} = TRUE then
{TimecardLines.lmlActualEndTime}

{@LastClkDateDetailNEW}:

whileprintingrecords;
If {TimecardLines.lmlActualEndTime} = Maximum({@ClockDTNew},{Jobs.jmpJobID}) then
stringVar LastClkQtyNEW := totext({TimecardLines.lmlGoodQuantity},0)

[former_member11696 · Accepted solution]

Click on Database, Verify Database... to update the RPT file with the new data structure