Forced inserts/modifies/deletes

Rob_Hansen

I've hit another issue where the RTC behaves differently than classic and I'm trying to figure out the best way to approach it.

Here's the scenario... Assume you have a table that contains entries that the user can directly edit (could be schedule entries, capacity entries, whatever). Now, let's say that anytime a change is made in this table, you need to run a process based on the new data. For example, OnInsert in the table, you need to call a routine to either (a) run through all existing entries in the table and check whether the new entry caused problems, or (b) take the data and generate records in another table that summarize the data (as an arbitrary example).

The challenge in this example is that if you have a call OnInsert (for example) to run a function in a codeunit that will read all the records, the function won't see the new record yet, because the insert doesn't actually occur until the END of the OnInsert trigger logic. Same thing for modifies (the function would see the previous record values) or deletes (the function would still see the record being deleted).

My workaround for this has always been simple. In the OnInsert trigger, I would temporarily force the insert, call the update routine, then delete the record (so the implicit INSERT would not error out on the record already existing:

OnInsert:

INSERT;
SomeCodeunit.RunUpdateProcess;
DELETE;

Similar code would be used OnModify (force the MODIFY explicitly and then call the routine) and OnDelete (the opposite of OnInsert - explicitly DELETE, then call the update routine, then INSERT). Nice and simple and worked great.

In the RTC this approach does not work. It ranges from actually raising errors, to just causing flukey behaviour when inserting records (the new record temporarily disappears, presumably because of the DELETE call OnInsert...then you can press F5 to refresh and it appears fine). So...does anyone have any alternate approaches to this? Before I took the above approach, I used to write complex code to pass in the updated record to the update routine and then have it look at the passed-in version, but that was not nearly as clean (or simple) as the above approach. I'm hoping there's a good (simple) way to handle this that is RTC-friendly.

kine

Best is to handle this in you business logic. Pass the old rec and new rec and when calculating e.g. sum of values, subtract the old one and add new one. Of course, when deleting and inserting you will remove old or just add new to the value...

Rob_Hansen

I can do that, but it's not nearly as clean. It's just ugly to have to pass a flag into the function to tell it that one of the records it "sees" has been modified, and to use the passed-in record instead when it hits that point. It's not complicated coding, but it sure was nice and simple (and again, very clean!) when you could simply force the update to happen and then call a function. I'm really disappointed the RTC can't handle this approach when the classic client can, and this is all table-based logic (nothing related to pages or RTC-specific functionality).

I have other places I've done stuff like this where it is far, far more complicated to do it by passing in an updated record. My example in this post was a simple one (scan the table and read the records). Where it gets really ugly, is when you do this sort of thing in a multi-level record structure (like production BOM lines) and you want to automatically call functions at each cascading level up to sum the underlying components. Now you're looking at passing an overridden record up through the chain each time...like I said, it's just ugly.

kine

Yes, but for me inserting and deleting the record in OnInsert is much more ugly than passing the record and handling the logic in this way. This concept is used e.g. in function calculating the Available Inventory. The quantity change is passed to the function to calc with this change.

beran

The insert, <something>, delete during OnInsert is a bit problematic. The middle tier server is assuming that it is inserted, since it gets no errors and now it is not there. Same goes for doing update during OnValidate when it has not been inserted.

So I would take the same approach as some of the basic application does. Insert the row and then clik a 'Calc yyy' button. Not need fully for the user but the code becomes simple and it works.

Rob_Hansen

I have a number of cases where running a function for the update just isn't feasible. If the user forgets, there will be serious data issues in the database that will cause processes to calculate and show incorrect data. So, the updates have to happen in real time. For now I have no option but to write code to pass updates through manually, but it's just another case where the classic client does something fine and the RTC does not.

I'm not sure why INSERT, <something>, DELETE causes issue for the service tier. You say it will think the record is there but it isn't. At the end of the OnInsert trigger the record IS there because the implicit insert occurs at the END of the OnInsert trigger. So, when coding this way, the following occurs:

OnInsert:
INSERT; <insert - the record now exists>
<code>
DELETE; <delete - gets rid of the new record>
<Implicit insert occurs here so the record DOES exist at the end>

beran

Discussion this a bit with the another dev it appears that it is infact saved to the database. The client gets confused and a fix is that the client needs to refresh to get the proper result. It is now noted as a bug in V7. Please file a hotfix request for 6.0SP1.