Add sequential number to 3.10 register table - how to do it?

Dynamics 365 Business Central/NAV Developers Forum
#1

I have created a new field in the G/L Register table, “Period Trans. No.” and need to populate it with a sequential number based on the posting date range (another new field)…

However, the code below is not doing it for me, I get one number for each date:

01/01/08 = 1

01/01/01 (next record) = 0 (not assigned a number).

Here is my code. “Period Trans. No.” is assigned a value of 1 before processing;

//SORTING(Posting Date) ORDER(Ascending)

SETRANGE(“Posting Date”, PeriodStart,PeriodEnd);
“Period Trans. No.” := PeriodTransNo;
MODIFY;
PeriodTransNo := PeriodTransNo +1;

#2

Hi Colin,

Is this from a report?

It will look like the SETRANGE on the “Posting Date” is filtering to one record at a time.

Can you give us the design of the report?

#3

yes, a non-printing report.

Data Item = G/L Register

DataItemTableView:SORTING(Posting Date) ORDER(Ascending)

On Init Report : PeriodTransNo := 1;

vars PeriodStart and PeriodEnd

On after get record:

IF (PeriodStart = 0D) OR (PeriodEnd = 0D) THEN
ERROR(‘You must enter a Period Start and Period End Date’);

SETRANGE(“Posting Date”, PeriodStart,PeriodEnd);
“Period Trans. No.” := PeriodTransNo;
PeriodTransNo := PeriodTransNo + 1;
MODIFY;

That is it, would seem simple except it is skipping records.

#4

Hi Colin,

Again I think it’s the SETRANGE on the posting date. This should be in the OnPreDataItem trigger otherwise it will get the first record of the table and then set the posting date range.

#5

Makes no difference.[:(]

#6

Hi Colin,

What posting date range are you using?

The report is sorting the register by posting date (a new field) - has this been populated for all records?

#7

That was a typo, all dates are from 01/01/08 onwards.

All fields have a date.

Daterange 01/01/08…31/12/08

Actually, I am getting the Start Date and End Date not a date range…I tried the date range but when that failed, decided to use specific dates.

#8

Hi Colin,

Is PeriodTransNo declared as a local or global? - should be global

#9

Yes, a Global var.

[:^)]

#10

What does the “Posting Date Range” field in G/L Register contain and what is the type of it?

#11

Hi Colin,

Is it also declared as a local varible?

Here’s a report that work for me (Quick test)

OBJECT Report 50099 G/L Register Trans No.
{
OBJECT-PROPERTIES
{
Date=17/04/09;
Time=15:17:07;
Modified=Yes;
Version List=;
}
PROPERTIES
{
ProcessingOnly=Yes;
OnInitReport=BEGIN

PeriodTransNo := 1;
END;

}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table45;
DataItemTableView=SORTING(Posting Date)
ORDER(Ascending);
OnPreDataItem=BEGIN
SETRANGE( “Posting Date”, 010108D, 311208D );
END;

OnAfterGetRecord=BEGIN

“Period Trans. No.” := PeriodTransNo;
PeriodTransNo := PeriodTransNo + 1;
MODIFY;
END;

}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
PeriodTransNo@1040000 : Integer;

BEGIN
END.
}
}

#12

There is no such field.

The “Posting Date” is the field in the table.

When running the report, it is possible to enter a date range such as 01/01/08…31/12/08.

In this example, I have tried entering a date range (or daterange) but that did not make any difference to entering the Date variables, PeriodStart and PeriodEnd.

[;)]

#13

Matched line by line and still I do not get a sequential number!

3833.gl_registers.png

#14

Hi Colin,

Then it must be the key - I added a key for posting date only - Is yours Posting date and Period Trans. No.?

This would make sense as when you modify with a number then the position in the table is altered and the next record is the next date and Period Trans. No.= 0

#15

Sorry, No.

The primary key is the No. and I have a new key “Posting Date” and that is what I am using, see under;

Key
No.
Creation Date
Source Code,Journal Batch Name,Creation Date
Posting Date,Period Trans. No.
Posting Date

I need the Posting Date,Period Trans. No. key for a report that is the end result for Spanish business reporting.

#16

Correct me if I’m wrong:

You have extended your Register table with a “Posting Date” field. In this field you store the posting date of the General Journal.

How do you prevent the user from entering different posting dates in a General Journal?
How do you prevent the user from entering a posting date for the period between start and ending date AFTEr you have assigned your Period Trans. No.?

Dave’s report example should definitely work for you and do what you asked for.

#17

Hi Colin,

Are you using SQL?

It’s possible that SQL is being “Too clever” and using the “Posting Date” and “Trans no.” key. Try disabling this key for the update.

It this doesn’t work then I would use a record type variable and use it for the MODIFY and therefore not repositioning in the table

#18

Maybe call a

MODIFYALL(“Trans No.”, 0);

directly after the SETRANGE

This will prevent the system from confusing the lines.

#19

The Posting Date is the same as what was posted to the G/L Entry.

I don’t care what the user puts into the posting date in the journal, it is their journal.

The period transaction no. can be reset if needed, it is used for an end of year report so not likely to change except for the auditors adjustments.

I know Dave’s report should work but it does not!

That is the frustration.

#20

Not using SQL.

Native DB

I think you may have it with the record variable. it is on my list to try. I will let you know. Thanks, Colin