AutoSplitKey problem

MortenSteengaard · 2018-11-19

Hi,

I'm still new to NAV...

I would like to change the sorting of page 1007 ("Job Planning Lines) to be "Job No.,Job Task No.,Planning Date, Line No." descending. So I add "Planning Date" in the primary key of table 1003 ("Job Planning Lines") and add the key on page 1007 property SourceTableView and setting it to "Descending".

It works fine, but when the user presses "New", NAV shows the error "The lines cannot be split".

How do I sort the lines the way I want, without causing problems for the "New" functionallity?

Slawek_Guzek · 2018-11-20

@MortenSteengaard the creation of a new record does rely on sorting order, and on the fact that the least field in PK is of an integer type.

In case of T1003 the Line No. is the last field in the PK, and it is an integer.

When you "ask" the page to create a new record it looks at the current Line No. and tries to insert new one with the next Line No. - if the record is the last on the list, or tries to find previous record and inserts a new line with Line No. half way between the current one, and the previous.

If you change the sorting order the page still looks for next/prev records, but the order is not now in line with the Line No. Order. This leads to PK duplicates - say you are on line 10000 (which is now displayed as a last), system sees that there is no other line after it, and automatically assigns new Line no. +10000 bigger than the current one, and tries to insert it. But if fails because a line with Line No. = 20000 already exists, but it is just displayed before the last line.

So there you go - you can have it one way or another, but not both fully working at the same time.

I would strongly suggest to tell the user off, and not to modify the page at all - as it will have another consequences soon when she start using Begin/End Total line types (and do not keep the Planning Date in sync on all the Begin/End Total line types)

Slawek_Guzek · 2018-11-19

Do not change the primary key on any table without knowing exactly what are you doing and what are the consequences of your action. That rule, by the way, applies not only to NAV, but to any application.

Why didn't you just click the Planning Date on the Job Planning Lines page when you needed them to be displayed in the Planning Date order?

MortenSteengaard · 2018-11-20

The customer is tired of always clicking on the column header "Planning Date" whenever she opens the window.

Slawek_Guzek · 2018-11-20

You do not need to modify any key on the Job Planning Lines table to achieve that (not even mentioning the PK change). Just set the desired sorting order in PageView property, or add a line of code with SETCURRENKEY to the OnOpen page trigger

Having said that - this is a prime example of when to say NO to customer's request to modify the system. What if you have another user in future, also using Planning Lines, and they want different default display order, because they are also "too tired to make one click"?

If you don't learn to decline such requests you will soon end up developing one big green button titled 'Guess what I want and do everything for me", which will have to be mind-activated (well one click is too tiresome for some users) and will have a system which is heavily modified, hard to understand, maintain, and keep up to date with patches released from Microsoft,

MortenSteengaard · 2018-11-20

I totally agree with you, but I need to make this work, which I can't.

In the OnOpenPage trigger, I write this:

SETCURRENTKEY("Job No.","Job Task No.","Planning Date","Line No.");
SETASCENDING("Planning Date", FALSE);

NAV totally ignores the "SETASCENDING"-line. I have tried with TRUE and FALSE and with other fields, but there is no change in my page.

(My changes to SETCURRENTKEY is visible on the page.)

I need the "Planning Date" to be descending.

MortenSteengaard · 2018-11-20

After "SETASCENDING..." I have this:

IF FINDFIRST THEN;

I can see that the FINDFIRST finds a different record when the SETASCENDING is FALSE compared to when SETASCENDING is TRUE. So I get some sorting.

But the problem is that in every case, the lines are shown in the same order (with the oldest on top). I need the newest "Planning Date" to be on top.

whynotwi · 2018-11-20

I agree with Slawek, that the users have a lot of wishes, but often it is not really senseful to fulfil this wishes. But ok, sometimes there is a higher power that says "Do this !" and you have to follow...

Changing a primary key is always a bad idea. At least until you haven't identified every GET-Action on the database to this record...

Is it possible to add a new key ? If so, add a key to the fields you want and use SETCURRENTKEY when opening the page. Then the order is in the field(s) you have choosen in the SETCURRENTKEY-function.

MortenSteengaard · 2018-11-20

Now I have tried to add a new key ("Job No.","Job Task No.","Planning Date","Line No.") on the table.

And I still have this code in the OnOpenPage trigger:

SETCURRENTKEY("Job No.","Job Task No.","Planning Date","Line No.");
SETASCENDING("Planning Date", FALSE);
IF FINDFIRST THEN;

In the page, all records have the same "Job No." and "Job Task No." - at least in my test case. And it still shows the smallest/oldest "Planning Date" as the first record on top.

Slawek_Guzek · 2018-11-20

You do not need to add any new key to the table.

Put "SORTING(Planning Date) ORDER(Descending)" in the SourceTableView property, and add IF FINDSET() THEN; to the OnOpenPage trigger.

MortenSteengaard · 2018-11-20

I have that now and now the page is fine. But when I press the "New" button, I get the error message: "The lines cannot be split". The error message turn up on every line except when I am on the topmost line.

whynotwi · 2018-11-20

@Slawek: ok, its offtopic, but doesn't the SourceTableView require a key ? In my mind the SourceTableView is the same as SETCURRENTKEY, just as a property (and mayvbe a little faster than that).

Slawek_Guzek · 2018-11-20

@whynotwi No. SourceTableView and SETCURRENTKEY did require a key in versions pre 2013, now they do not.

By the way the SourceTableView and SETCURRENTKEY should act exactly in the same way, but apparently there is some bug in NAV causing the SETCURRENTKEY to be ignored

Slawek_Guzek · 2018-11-20

@MortenSteengaard the creation of a new record does rely on sorting order, and on the fact that the least field in PK is of an integer type.

In case of T1003 the Line No. is the last field in the PK, and it is an integer.

When you "ask" the page to create a new record it looks at the current Line No. and tries to insert new one with the next Line No. - if the record is the last on the list, or tries to find previous record and inserts a new line with Line No. half way between the current one, and the previous.

If you change the sorting order the page still looks for next/prev records, but the order is not now in line with the Line No. Order. This leads to PK duplicates - say you are on line 10000 (which is now displayed as a last), system sees that there is no other line after it, and automatically assigns new Line no. +10000 bigger than the current one, and tries to insert it. But if fails because a line with Line No. = 20000 already exists, but it is just displayed before the last line.

So there you go - you can have it one way or another, but not both fully working at the same time.

I would strongly suggest to tell the user off, and not to modify the page at all - as it will have another consequences soon when she start using Begin/End Total line types (and do not keep the Planning Date in sync on all the Begin/End Total line types)

MortenSteengaard · 2018-11-22

Hi Slawek.

Thank you very much for your explanation! I learned a lot.

I won't change the page.

Best regards,

Morten

AutoSplitKey problem

Best Answer

Answers

Categories