Table filtering for unique secondary keys on a Report.

Business Central/NAV Developers Forum
1

I want each Phase Code that was posted against each Job No. in the Job Ledger Entry table. I was wondering if there is a more efficient way to skip though and find the unique records without reading and comparing each record as I am doing now in this Report. I believe there is a way with FIND and NEXT, but I’m not sure how to code it or if that method would apply here.

Job Ledger Entry - OnAfterGetRecord()

IF “Phase Code” <> LastPhase THEN BEGINLastPhase := “Phase Code”;*Other Processing…*END ELSE

CurrReport.SKIP;

2

Add a new key to Job Ledger Entry table;

Job No.,Phase Code,Entry No.

This will sort the entries by Job No., then by Phase Code and finally by Entry No.

See below;

Entry No. Job No. Phase Code
1 GUILDFORD, 10 CR 1-SPEC
2 GUILDFORD, 10 CR 2-FURN
3 GUILDFORD, 10 CR 3-APPR
4 DEERFIELD, 8 WP 1-SPEC
5 DEERFIELD, 8 WP 2-FURN
6 DEERFIELD, 8 WP 3-APPR
7 GUILDFORD, 10 CR 4-ASSEMB
8 GUILDFORD, 10 CR 4-ASSEMB
9 GUILDFORD, 10 CR 4-ASSEMB
10 GUILDFORD, 10 CR 4-ASSEMB
11 GUILDFORD, 10 CR 4-ASSEMB
12 GUILDFORD, 10 CR 4-ASSEMB
13 GUILDFORD, 10 CR 4-ASSEMB
14 GUILDFORD, 10 CR 1-SPEC
15 GUILDFORD, 10 CR 2-FURN
16 GUILDFORD, 10 CR 3-APPR
17 GUILDFORD, 10 CR 4-ASSEMB
18 GUILDFORD, 10 CR 4-ASSEMB
19 GUILDFORD, 10 CR 4-ASSEMB
20 GUILDFORD, 10 CR 4-ASSEMB
21 GUILDFORD, 10 CR 4-ASSEMB
22 GUILDFORD, 10 CR 4-ASSEMB
23 GUILDFORD, 10 CR 4-ASSEMB
24 GUILDFORD, 10 CR 4-ASSEMB
25 GUILDFORD, 10 CR 4-ASSEMB

With new key;

Entry No. Job No. Phase Code
4 DEERFIELD, 8 WP 1-SPEC
5 DEERFIELD, 8 WP 2-FURN
6 DEERFIELD, 8 WP 3-APPR
1 GUILDFORD, 10 CR 1-SPEC
14 GUILDFORD, 10 CR 1-SPEC
2 GUILDFORD, 10 CR 2-FURN
15 GUILDFORD, 10 CR 2-FURN
3 GUILDFORD, 10 CR 3-APPR
16 GUILDFORD, 10 CR 3-APPR
7 GUILDFORD, 10 CR 4-ASSEMB
8 GUILDFORD, 10 CR 4-ASSEMB
9 GUILDFORD, 10 CR 4-ASSEMB
10 GUILDFORD, 10 CR 4-ASSEMB
11 GUILDFORD, 10 CR 4-ASSEMB
12 GUILDFORD, 10 CR 4-ASSEMB
13 GUILDFORD, 10 CR 4-ASSEMB
17 GUILDFORD, 10 CR 4-ASSEMB
18 GUILDFORD, 10 CR 4-ASSEMB
19 GUILDFORD, 10 CR 4-ASSEMB
20 GUILDFORD, 10 CR 4-ASSEMB
21 GUILDFORD, 10 CR 4-ASSEMB
22 GUILDFORD, 10 CR 4-ASSEMB
23 GUILDFORD, 10 CR 4-ASSEMB
24 GUILDFORD, 10 CR 4-ASSEMB
25 GUILDFORD, 10 CR 4-ASSEMB

Hope this helps

3

I do have the table sorted by Job No. and Phase. My approach seems to be working. However, it is not very efficient. There could be thousands of transaction for the same Job Phase. I am simply looking for a way to FIND the next unique combination without reading though all of the records and then skipping them, if they are the same as the previous.

4

Why not add Phase table to your processing.

new variables

Phase As Phase table

JobLedgerEntry2 As Job Ledger Entry table

set key on Job Ledger Entry - Job No., Phase Code, Entry No.

then;

Job Ledger Entry - OnAfterGetRecord()

IF (“Job No.” = LastJobNo) AND (“Phase Code” = LastPhaseCode) THEN BEGIN

CurrReport.SKIP;

END ELSE BEGIN

LastJobNo := “Job Ledger Entry”.“Job No.”;

LastPhaseCode := “Job Ledger Entry”.“Phase Code”;

Phase.RESET;

JobLedgerEntry2.RESET;

JobLedgerEntry2.SETRANGE(“Job No.”, “Job Ledger Entry”.“Job No.”);

JobLedgerEntry2.SETRANGE(“Phase Code”, Phase.Code);

IF “Job Ledger Entry”.FIND(’-’) THEN BEGIN

{Do processing}

END;

END;

I haven’t tried this but it stops processing of job ledger entries where the job no and phase code have been dealt with, by using 2 variables - lastjobno and lastphasecode.

Hope this helps?

5

It appears logically that this approach would work also. However, wouldn’t you still be reading through all of the records before doing a comparision?

Job Ledger Entry - OnAfterGetRecord()

IF (“Job No.” = LastJobNo) AND (“Phase Code” = LastPhaseCode) THEN BEGIN

CurrReport.SKIP;

… and then you have additional reads to the table with:

JobLedgerEntry2.SETRANGE(“Job No.”, “Job Ledger Entry”.“Job No.”);

JobLedgerEntry2.SETRANGE(“Phase Code”, Phase.Code);

IF “Job Ledger Entry”.FIND(’-’) THEN BEGIN

I think you may be on the right track. Maybe something along the lines of FIND(which) and/or NEXT(step) ?? I’m just uncertain of how to code this.

6

OK how about attacking this from a different angle. Have a report with a Job DataItem and then process job ledger entries from that instead (combined with the Phase table).

ph As Phase rec
jle As Job Ledger Entry rec

Job - OnAfterGetRecord()

ph.RESET;
NoOfPh := ph.COUNT;
ph.FIND(’-’);
repeat
jle.RESET;
jle.SETCURRENTKEY(jle.“Job No.”, jle.“Phase Code”, jle.“Entry No.”);
jle.SETRANGE(“Job No.”, Job.“No.”);
jle.SETRANGE(jle.“Phase Code”, ph.Code);
IF jle.FIND(’-’) THEN
//repeat
MESSAGE(’%1 ; %2’, jle.“Job No.”, jle.“Phase Code”);
//until jle.NEXT = 0;
until ph.NEXT = 0;

Is this what you wanted?

7

Again, I think we are getting closer by reducing the number of reads to the Job Ledger. However, now we will be reading every Phase table record for every Job No.

I think I have a solution that may work. What to you think?

Job Ledger Entry - OnAfterGetRecord()

Other Processing - Process the first record occurance of the Phase

SETRANGE(“Phase Code”,“Phase Code”); // Set the range for the Phase that was just processed
IF “Job Ledger Entry”.FIND(’+’) THEN SETRANGE(“Phase Code”); // Find the last record for the phase and reset the Range to allow the next record to be read

8

Phase records should be a lot less than Job Ledger Entry records. Surely there wont be too many Phase records to read and filter on? Also my example only reads the first Job Ledger Entry if it exists.

Not sure about your example. Looks incomplete to me.