Modifying fields in OnInsert trigger using RecordRef

rocatis

Re. subject: how do I do this?

I want to modify the value of a field on a record when the OnInsert trigger for that record is run. But I need to do it using RecordRef/FieldRef and I can't seem to get my head around it.

For instance, say I want to set "Prices Including VAT" on the Customer table to TRUE when running the OnInsert trigger. Usually you would simply insert a line saying

"Prices Including VAT" := TRUE;

But if I try to obtain the same functionality using RecordRef and FieldRef I run into the classic problems:

1) I cannot do a RecordRef.MODIFY because the record has not yet been created.
2) If I do a RecordRef.INSERT I get an error when NAV tries to insert the record .

How do I get the changes made in RecordRef copied back into Rec?

Xypher

OnInsertRecord
The C/AL code in this trigger is executed before the system inserts a new record into the table.
...

You can't modify what has not been created yet.

The OnInsertRecord function actually has to return (exit) TRUE before the system even considers inserting the record.

I would look into OnNewRecord for anything remotely close to what you're trying to do. (Even though you most likely still wont be able to accomplish what you are trying to do there)

DenSter

Why do you have to do that with a record reference? You're right there in the record, you already HAVE a reference to the record. Just use the field directly. That way it doesn't matter if it's in the database or not.

rocatis

Xypher wrote:

OnInsertRecord
The C/AL code in this trigger is executed before the system inserts a new record into the table.
...

You can't modify what has not been created yet.

I know...

The OnInsertRecord function actually has to return (exit) TRUE before the system even considers inserting the record.

This is on the form. I'm working on the table which kind of limits my options to the OnInsert trigger.

I would look into OnNewRecord

Still on the form.

Even though you most likely still wont be able to accomplish what you are trying to do there)

I'm afraid you might be right.

rocatis

DenSter wrote:

Why do you have to do that with a record reference? You're right there in the record, you already HAVE a reference to the record. Just use the field directly. That way it doesn't matter if it's in the database or not.

The field is dynamically referenced (i.e. the user selects which of a set of fields should be populated using a setup table). By doing it this way I don't have to change any code if new fields are added to the table.

Sandeep_Prajapati

Hi Rocatis,

Here is a partial solution to do the job. :-k

Suppose we are dealing with Item Table.
In OnInsert() at the last

Name--- DataType--- Subtype
ItRef--- RecordRef
FldRef--- FieldRef
It--- Record ---Item
PrimaryKeyFieldRef--- FieldRef

    It.RESET;
    IF It.GET('') THEN    // If found any blank record in table, delete it
    It.DELETE;    
            
                            
    ItRef.OPEN(DATABASE::Item,FALSE);
    ItRef.GETTABLE(Rec);  
    PrimaryKeyFieldRef := ItRef.FIELD(1);  // "No."
    PrimaryKeyFieldRef.VALUE("No.");
    FldRef := ItRef.FIELD(3);              // Description
    FldRef.VALUE('By Rec_REF');            // Just for an example
    ItRef.INSERT;
    ItRef.CLOSE;
    CLEAR(Rec);                            // make the record blank    
              

1)So, after executing this code it has inserted one line in the table with the required field(s) value changed. O:)
2) Another record (blank) will be inserted in the table as (CLEAR(Rec)) :-#

Again, when the user inserts a new record , then
a) the blank line will be deleted
b) A record with desired field(s) values will be created
c) one blank record would be created.

Problem : 1) Blank Record
possible solution : delete blank record from the onTimer() in regular interval OR deleting from the onValidate() of a field of the Item Card for which user is most likely to update value after the default record Insert. Or a batch Job.

Problem : 1) After insert the form shows the blank line inserted corresponding to "rec" instead of corresponding to "ItRef".
possible solution : some kind of tag and/or filter work


I hope it helps O:)

DenSter

rocatis wrote:

DenSter wrote:

Why do you have to do that with a record reference? You're right there in the record, you already HAVE a reference to the record. Just use the field directly. That way it doesn't matter if it's in the database or not.

The field is dynamically referenced (i.e. the user selects which of a set of fields should be populated using a setup table). By doing it this way I don't have to change any code if new fields are added to the table.

Ah right, you have say generic default values for yet undefined fields, and you want to populate those in the OnInsert trigger. I don't think that will work because I think recref works with existing records. You could build a brand new one with recref, say right after it sets the primary key, ignoring the current record, and then do some sort of TRANSFERFIELDS. I'm not an expert there though, I don't even know if there is a method like that in recref.

rocatis

Ah right, you have say generic default values for yet undefined fields, and you want to populate those in the OnInsert trigger. I don't think that will work because I think recref works with existing records.

RecordRef works works fine even if the record has not been inserted yet.

If you add the line "RecRef.GETTABLE(Rec)" in the OnInsert trigger, RecRef will contain any fields populated at that time - even though the record hasn't been inserted yet.

The problem is that there doesn't seem to be any way of transferring the modifications done to RecRef back into Rec. The normal way to do this would be to use RecRef.MODIFY but of course this won't work in this case because Rec has yet to be inserted.

You could build a brand new one with recref, say right after it sets the primary key, ignoring the current record, and then do some sort of TRANSFERFIELDS. I'm not an expert there though, I don't even know if there is a method like that in recref.

It's basically that exact function that I'm missing and therefore trying to find a workaround for :-k

DenSter

Have you considered using the virtual Field table? Filter that on the table number and you should have a complete list of fields for that table.

DenSter

rocatis wrote:

If you add the line "RecRef.GETTABLE(Rec)" in the OnInsert trigger, RecRef will contain any fields populated at that time - even though the record hasn't been inserted yet.

I did not know that, that is pretty cool . That's where I thought you'd have the biggest issue.

rocatis

DenSter wrote:

Have you considered using the virtual Field table? Filter that on the table number and you should have a complete list of fields for that table.

Well, you're right of course, but why would I want to do that? The problem is not knowing which fields are available/defined, the problem is transferring a value from a RecRef to a Rec which hasn't been inserted yet.

DenSter

...because there is no generic way to access fields in Rec, gotcha

rocatis

Sandeep Prajapati wrote:

Here is a partial solution to do the job. :-k

I was actually in the middle of writing that I could see no point in what you suggested but then.... :idea:

I'm not sure if this was actually your idea, but this is how I've dealt with the problem:

OnInsert trigger:

(...)
// Part 1
RecRef.GETTABLE(Rec);
FunctionThatDoesStuff(RecRef);

// Part 2
DummyRec.GET;
TRANSFERFIELDS(DummyRec,FALSE);
DummyRec.DELETE;

In FunctionThatDoesStuff I basically change the value of my dynamically defined field to the desired value (this also worked before) but the twist now is that I clear all the primary key fields before doing a RecRef.INSERT.

Part 2 then retrieves the new (temporarily created) record and transfers all the field values except the primary key fields. It then deletes the temporarily inserted record...

I'm not saying it's the most beautiful piece of code and it has some built-in caveats (like, it won't work if blank primary keys are allowed in the table) but it works, it's simple and the code is fairly generic.

Thanks, everybody! \:D/

Sandeep_Prajapati

Hi rocatis,

could you please share, at what point your "part 2" gets executed. I mean can you elaborate the solution. ("part 2" is also in the OnInsert() ...???) O:)

DenSter

I think it's a pretty ugly solution but it is effective. Instead of using one single value though I'd probably try to use some sort of random number as the primary key, so that multiple users don't get interfered with (locking or worse GETting a record that someone else just inserted), maybe use a GUID. I do think that the record should be deleted right at the end of the function.

Another thought, does recref work with temp records?

ajhvdb

Maybe i'm missing the point but doesn't SETTABLE and GETTABLE do the work?

Xypher

ajhvdb wrote:

Maybe i'm missing the point but doesn't SETTABLE and GETTABLE do the work?

SETTABLE doesn't work

rocatis

ajhvdb wrote:

Maybe i'm missing the point but doesn't SETTABLE and GETTABLE do the work?

Let me quote the online help:

GETTABLE (RecordRef)
Use this function to make a recordref variable use the same table instance as a record variable.

Compare that to SETTABLE:

SETTABLE (RecordRef)
Use this function to make a recordref variable use the same table instance as a record variable.

Any filters that are applied to the record are also applied to the recordref. If you change the filter that is applied to the record, you must call SETTABLE again to apply the new filter to the recordref.

It's basically the same function although one applies filters and the other does not. If you ask me it should've been a single function with an "ApplyFilters" parameter.

Also, they should've implemented a PUTTABLE function :P

Xypher

rocatis,

If I remember correctly from past posts and personal experience, I'm pretty sure this function doesn't work. (it's bugged)

rocatis

Sandeep Prajapati wrote:

could you please share, at what point your "part 2" gets executed. I mean can you elaborate the solution. ("part 2" is also in the OnInsert() ...???) O:)

The "Part X" comments are only there for me to use as reference in the text... All of the code lines have been added at the bottom of the OnInsert trigger.

rocatis

Xypher wrote:

If I remember correctly from past posts and personal experience, I'm pretty sure this function doesn't work. (it's bugged)

In any case, none of them will transfer information from RecRef to Rec, only the other way round.

Xypher

It may be risky but since you do get all the current fields & their values when you declare the RecRef, you could do the following...

OnInsert()

  RecRef.GETTABLE(Rec);
  COMMIT;  //Or possibly Rec.DELETE;
  //DoYourStuff
  RecRef.MODIFY;

rocatis

DenSter wrote:

I think it's a pretty ugly solution but it is effective.

Well, Microsoft haven't given us the option to make it pretty ](*,)

Instead of using one single value though I'd probably try to use some sort of random number as the primary key

I agree with you in principle, but you would need to pass the generated key between the OnInsert trigger and the FunctionThatDoesStuff in order to get that to work. Which in turn means that you would need to code a specific FunctionThatDoesStuff for each unique type of primary key... #-o (i.e. Code20, Code20+Integer, Code20+Code20+Code10+Integer etc.). That's even uglier :sick:

so that multiple users don't get interfered with (locking or worse GETting a record that someone else just inserted)

Simply put a LOCKTABLE before Part 1 of my code and that's sorted.

I do think that the record should be deleted right at the end of the function.

Well, it is... (it would make absolutely no sense at all to keep it - it's basically just a backwards way to transfer parameters).

Another thought, does recref work with temp records?

It does.

rocatis

Xypher wrote:

It may be risky but since you do get all the current fields & their values when you declare the RecRef, you could do the following...

OnInsert()

  RecRef.GETTABLE(Rec);
  COMMIT;  //Or possibly Rec.DELETE;
  //DoYourStuff
  RecRef.MODIFY;

You haven't actually tested this, have you? [-X

COMMIT will NOT "flush"/complete the INSERT in progress which means that RecRef.MODIFY will fail with "Rec does not exist". The same fate awaits DELETE.

Xypher

It is the point of using either command to halt the current operation of inserting the record. So that you may issue another insert but with your modification.

But I realize now you can't do this only because you're placing this code in the table itself rather than doing it from a form. (Which you could do this from a form)

rocatis

Xypher wrote:

It is the point of using either command to halt the current operation of inserting the record. So that you may issue another insert but with your modification.

But I realize now you can't do this only because you're placing this code in the table itself rather than doing it from a form. (Which you could do this from a form)

Yep. Placing the code on the form will result in more modified objects and eventually somebody will forget to add the code to a form where the users insert records and then the sh*t's gonna hit the fan.

DenSter

rocatis wrote:

Well, Microsoft haven't given us the option to make it pretty ](*,)

You are trying to make NAV do something it's not designed to do. It is what it is, I would never work with this approach. I guess I just don't mind adding indivudual fields to custom functionality. I don't have this urge to make everything generic.

rocatis wrote:

I agree with you in principle, but you would need to pass the generated key between the OnInsert trigger and the FunctionThatDoesStuff in order to get that to work. Which in turn means that you would need to code a specific FunctionThatDoesStuff for each unique type of primary key... #-o (i.e. Code20, Code20+Integer, Code20+Code20+Code10+Integer etc.). That's even uglier :sick:

Whether you set primary key values to blank values or random numbers, you will always have to know which fields are part of the primary key. I think it would be nicer to have a unique record each time you do this, rather than have every user insert the same 'temporary' record, just to make sure nobody steps on eachother. I would not trust on LOCKTABLE to manage that bit. You can call it uglier, I think it is cleaner, it's a matter of opinion.

rocatis wrote:

Another thought, does recref work with temp records?

It does.

I'm just thinking out loud here, just brainstorming, trying to help you. Would it be possible to send in a temp record variable into your recref (at which point you know the table number, you know which fields are the primary key, you already have all this information. Remember you are programming this in OnInsert of any given table, so it doesn't have to be completely generic), then you do your stuff in the recref, insert it into the ether of the computer's memory space, you can get it back with the temporary record variable, and do a transferfields back into Rec from there? Does the recref retain that link to the record you pass into it?

Xypher

Honestly I am still unsure why you really need this functionality. I have ready your reason why you want to use Rec/FieldRef, but if a new field is introduced later on maybe you wont want this code of yours to touch/interpret data in that field.

Perhaps you can give us an example of what exactly you're trying to implement this code for.

rocatis

DenSter wrote:

rocatis wrote:

Well, Microsoft haven't given us the option to make it pretty ](*,)

You are trying to make NAV do something it's not designed to do.

This could very well lead to a heated debate

Anyway, I don't agree. Fact is, the code I'm trying to get to work will actually work ANYWHERE in NAV EXCEPT on the OnInsert trigger.

It could also be argued that SETTABLE/GETTABLE really should have a counterpart for moving information the other way (RecRef->Rec as opposed to Rec->RecRef).

But I'm sure we could debate this ad nauseum.

Whether you set primary key values to blank values or random numbers, you will always have to know which fields are part of the primary key.

No, I don't. Consider this code (which is part of the now infamous FunctionThatDoesStuff):

KeyRef := RecRef.KEYINDEX(1);
FOR Counter := 1 TO KeyRef.FIELDCOUNT DO BEGIN
  FieldRef := KeyRef.FIELDINDEX(Counter);
  CASE UPPERCASE(FORMAT(FieldRef.TYPE)) OF
    'INTEGER':
      FieldRef.VALUE(0);

    'TEXT':
      FieldRef.VALUE('');

    'CODE':
      FieldRef.VALUE('');

    'DECIMAL':
      FieldRef.VALUE(0);

    'OPTION':
      FieldRef.VALUE(0);

    'BOOLEAN':
      FieldRef.VALUE(FALSE);

    'DATE':
      FieldRef.VALUE(0D);

    'TIME':
      FieldRef.VALUE(0T);

    'BINARY':
      FieldRef.VALUE(0);

    'BIGINTEGER':
      FieldRef.VALUE(0);

    'DURATION': ;  // Haven't done this yet...

    'DATETIME': ;  // Haven't done this yet...
  END;
END;
RecRef.INSERT;

This clears all primary key fields regardless of how many there are and which types they are. What this means is that I can use a simple GET without any parameters at all for ALL tables.

I think it would be nicer to have a unique record each time you do this, rather than have every user insert the same 'temporary' record, just to make sure nobody steps on eachother. I would not trust on LOCKTABLE to manage that bit.

:shock: That's exactly what LOCKTABLE was made for... I would think not trusting it to do that would make your life unnecessarily cumbersome.

I'm just thinking out loud here, just brainstorming, trying to help you.

Yeah, and I appreciate it! =D>

Would it be possible to send in a temp record variable into your recref (at which point you know the table number, you know which fields are the primary key, you already have all this information. Remember you are programming this in OnInsert of any given table, so it doesn't have to be completely generic), then you do your stuff in the recref, insert it into the ether of the computer's memory space, you can get it back with the temporary record variable, and do a transferfields back into Rec from there? Does the recref retain that link to the record you pass into it?

Well, yes, but compared to your earlier suggestion where a separate function for each unique key type needed to be created, this is even worse: in order to get this to work you would need to pass the temporary record (as a VAR) to the FunctionThatDoesStuff. That means that you'll have to write a function for each table (although you'd get rid of the unique key type problem as you'll now have a function for each table).

Again, consider the code that I need to add to each of the tables I want to support my function:

LOCKTABLE;
RecRef.GETTABLE(Rec); 
FunctionThatDoesStuff(RecRef); 

DummyRec.GET; 
TRANSFERFIELDS(DummyRec,FALSE); 
DummyRec.DELETE;

I need to copy/paste these 6 lines verbatim into the OnInsert trigger on tables I need to support and create 2 local variables - RecRef (which doesn't change) and DummyRec which is a record of the table I'm currently working on. I don't need to change any code and I only have 1 function to maintain. DummyRec is inserted and deleted within the same LOCKTABLE, it's not COMMIT'ed, and FunctionThatDoesStuff is close to instantaneous.

As far as I can see there's simply no contest. But hey, we're talking religion here. [-o<

rocatis

Xypher wrote:

Honestly I am still unsure why you really need this functionality. I have ready your reason why you want to use Rec/FieldRef, but if a new field is introduced later on maybe you wont want this code of yours to touch/interpret data in that field.

Perhaps you can give us an example of what exactly you're trying to implement this code for.

It's part of a generic synchronization functionality I'm developing. That's why I need the code to be absolutely generic.

Consider this: I now have a system which allows any table to be synchronized across companies, complete with options to prevent users in the synchronized companies from creating, modifying or deleting records OR allowing them to create "local" records which they have full control over. This has been obtained with fully transparent code added to 5 functions in Codeunit 1 and the 6 lines shown earlier added to the OnInsert trigger ONLY for the tables where I want the users in the synchronized companies to be able to create "local" records. Additionally, a single non-editable Boolean field needs to created on these tables.

Specifically, I need the functionality we've been discussing because I don't know which tables will need to have "local" functionality (and I'm not very keen on creating a Boolean on all tables :roll: ) and I don't know what number that field will eventually have. So in my setup table I need to be able to specify which field is the "local" denominator for the tables that need "local" functionality - and I need to populate that field when the user creates a "local" record. I.e. in the OnInsert trigger.

There's some complex hokey pokey going on in order to get all this to work, and it's not flawless, but it solves a LOT of the problems we run into on a daily basis with a minimal amount of modifications.

DenSter

rocatis wrote:

This could very well lead to a heated debate

Nope, I agree that there could be a lot of additional features, but I guess I have accepted the limitations, and I don't have an urge to make everything generic. You still haven't explained exactly why you want to do this by the way.

rocatis wrote:

It could also be argued that SETTABLE/GETTABLE really should have a counterpart for moving information the other way (RecRef->Rec as opposed to Rec->RecRef).

But I'm sure we could debate this ad nauseum.

Nope, again. I agree, it would have been nice to have something that goes both ways. The fact is... we don't, and I'm fine with that

rocatis wrote:

Whether you set primary key values to blank values or random numbers, you will always have to know which fields are part of the primary key.

No, I don't.

Yes... you do... You say you call this GenericFunction from OnInsert, using GETTABLE(Rec). At that point you are in a particular table, and you know all primary key fields, and it would be a very easy thing to set the primary key fields with unique values.

rocatis wrote:

I think it would be nicer to have a unique record each time you do this, rather than have every user insert the same 'temporary' record, just to make sure nobody steps on eachother. I would not trust on LOCKTABLE to manage that bit.

:shock: That's exactly what LOCKTABLE was made for... I would think not trusting it to do that would make your life unnecessarily cumbersome.

LOCKTABLE is NOT intended to make sure that a record with all blank primary key values is not inserted by multiple users. YOU are USING it for that purposes, and that is a whole different discussion.

rocatis wrote:

compared to your earlier suggestion where a separate function for each unique key type needed to be created

I made no such suggestion, you are interpreting my suggestion that way.

rocatis wrote:

That means that you'll have to write a function for each table (although you'd get rid of the unique key type problem as you'll now have a function for each table).

No you are not getting what I am saying. You can still use a generic function that you pass a rec ref into, all you'd need to do in each OnInsert is set the primary key fields before you call that function. The function itself can be generic, the way you call it would be slightly different from each table.

Anyway this is starting to turn into a pissing match, and I have no desire to win that. Good luck with your solution, I hope you'll get it to work.