AutoIncrement value + OnInsert trigger

StLi

Hi!

I'm kinda in a stalemate here. And I hope someone can help me here without me needing to make essential changes on the table design.

My customer has a form modified so it copies the current line when you insert a new one. it just alters the line No. to prevent a primary key conflict.

This works quite well so far. Here is the problem: We recently added some tables and this table is related to said forms sourcetable by an Autoincrementing integer field.

My problem is that i have to make a routine which copies the related lines from the source record to the new record. But the identyfiing field which i have to save in the related tablerecords is still 0 until the OnInit trigger on the table has finished running. Is there any way to predict the value of the autoincrement-field?

I'm really glad if someone here knows a solution.

Regards
stli

p.s. i thought it might help if i explain the situation a second time a little different:

status quo:
I have a Table (A) and its from has been modified to automatically copy the current lines values if the user initiates a new one.
Recently had to add a related Table (B) which uses an Integer field of Table (A) to save said relation. Said field is autoincrement and - even though not part of the PK - used as an ID for relations quite often within the solution (don't ask me - wasn't my idea).

Now i have to copy the related records from table B as soon as the initiated Record in table A is inserted. The problem is: The AutoIncrement-Field i need to save in Table B is still zero when the triggers from table A and the dedicated form are run.

DenSter

StLi wrote:

Is there any way to predict the value of the autoincrement-field?

No not really, not reliably, because SQL Server assigns the number. There's always a chance that the next number is taken by another user. You can do INSERT and right after that the auto-increment field will have its value, but obviously you don't want to do that inside the OnInsert trigger.

I'd probably reconsider using auto-increment.

StLi

That would be quite an essential change to the table and we'd have to go back to square one with the testing process :-/

I just thought about a possible way to circumvent the trigger problem but i'd like to hear if you have any concearns why this could cause problems:
I could save the PK-values in the global vars of the form when the new line is inserted. Then i'd build a function which only runs when all the needed data is in the variables. That function would run the copy job for the related lines and then clear the variables.

I'm aware that this would affect performance a little and it's most certainly unconventional and needs good documentation. But: would it work and more importantly: is there any way how this could corrupt the integrity of the data? i don't see any way how you can use the form without triggering the onaftergetrecord trigger after a new record has been inserted. The only flaw i could imagine is that there will be two seperate transactions so - if the client crashes at the exact "right" time, the related records wouldn't be created.

DenSter

Wow that sounds a LOT more complex than redesigning the auto-increment property.

No I would not program anything that simulates the auto-increment values, simply because you cannot rely on that. NAV is a multi-user system, and multiple users can insert records into that table, so any solution that involves storing values in any unique session will not work properly.

There's one other thing you can try and that is to program whatever you need to do in a codeunit/function, and handle everything there.

So you'd have something like this:

RecAutoIncr.INIT;
RecAutoIncr.AutoIncrField := 0; // this will trigger the auto-increment to get the next number
RecAutoIncr.OtherFields := <value>
RecAutoIncr.INSERT(TRUE); // this will execute any other OnInsert code
// at this point, AutoIncrField will be populated, and you can access its value

RelatedRec.INIT;
RelatedRec.FKField := RecAutoIncr.AutoIncrField;

As long as you keep all that away from any OnInsert triggers, you should be able to see those values.

StLi

well.. that would be the usual way to do it, but i can't stay away from the triggers cause i have to do the job without the user having to click any extra buttons (yeah - not my design - some salesman agreed to copy the exact behaviour of the program navision is suposed to replace here)

However... the PK-values i could take from the insert trigger aren't autoincremented they are set by the Line No. and some "document no."-like code value so, the probability that these pk-value trigger a multiuser conflict are about the same as having the same problem with sales lines. And even if there where two useres at the same time creating the same line, the values would be exactly the same and the later of them would get a PK-conflict error. or have a second line with the same data (cause the other line has been inserted by the first user after the form was opened but before the new line of the second user was initiated.

I'm more concerned about any occasions that could prevent the onaftergetrecord from beeing run after the line has been inserted.

p.s. i'm very aware of the standard way to create new records and new related lines. But i wouldn't have to ask here if i could use default methods here.

DenSter

StLi wrote:

some salesman agreed to copy the exact behaviour of the program navision is suposed to replace here

Sounds to me like you need to have an open discussion with your customer about why they are replacing their system in the first place, if they want NAV to behave the same way as their old system. You also need to take a couple of guys, invite this sales guy into an alley. One of you holds the guy and the others knock some sense into them. Just kidding (or am I?)

If you know that something is a bad solution, you need to tell your customer about it, and propose a solution that will work in NAV.

Bottom line is that you CANNOT reliably predict auto-increment values. You are of course free to implement anything that you think will work. Just expect to be fixing issues with that solution for a very long time.

StLi

i dropped the autoincrement after my initial post.

well... thanks anyway I checked the solution with a more experienced coworker... and it actually should work.

now i'm just having trouble that i can't write to the DB when i'm in OnAfterGetCurrentRecord -.-

DenSter

OnAfterGetCurrentRecord is a form trigger, and should only be used to manage stuff that happens on the form itself. The only thing you should ever do in OnAfterGetCurrentRecord is display type stuff, like colors or bold or visible. This is not the right place to put any business logic.