I have some code on the item table that calculates a certain value, based on data from other tables, and writes the value into a field.
This code runs on the OnModify trigger and works fine for changes on the item table.
If i now add, delete or alter data from the other table, the Item table obviously gets not notified about the changes and does not recalculate the value. When I now call the calculation code from the OnInsert, OnModify or OnDelete trigger of the other table the code executes fine, but since the OnInsert, OnModify and OnDelete trigger comes BEFORE actual database actions occues, the calculation code from the item table still calculates it wrong.
How can I execute code after insert, after modification and after delete ?
I have a global function on table 27 item, that calculates a value and writes it to a field on table 27. Let’s say the function name is CalcTest();
This function needs data from the item table as of other tables as well to do it’s calculation correct.
On the OnModify trigger of the table 27 I call CalcTest().
Now I have the problem, that when on the other tables, data get altered, inserted or deleted, the corresponding record of table 27 needs to get recalculated. I now tryed to do something like this on the OnInsert trigger of table 50001
50001:OnInsert:
recItem.SETFILTER(“No.”, “Item No.”);
IF recItem.FINDFIRST THEN BEGIN
recItem.CalcTest();
END;
or you can try it with
recItem.MODIFY;
since the OnModify trigger from the table 27 calls CallTest();
It works, the code gets executed but, since the altered data from table 50001 gets written to the database AFTER the recItem.CalcTest() gets called, the calculation is wrong because it relies on data that has not been written yet.
the calculation must return the value 5, and it did.
now I added a record to 50001, the calculation must now return 6, but it did not since the calculation does a rec50001.SETFILTER(…) and it returns 0 results because the OnInsert code from table 50001 gets executed before the insert actually happens.
The most simple and robust solution to this problem would be to add a flow field in table 27. But I guess you’ve already ruled it out for some reason. Then, I’m afraid, there is no straight and easy way for you.
Probably you could try to calculate the value in the appropriate trigger OnInsert / OnDelete / OnModify and then adjust the result from Rec and xRec values.
For example,
OnInsert()
CalculatedValue := recItem.CalcTest();
CalculatedValue += Rec.SomeValue; // We know that Rec is not included in the result yet
But it’s all magic. Better still to keep to typical NAV patterns and recalculate totaling data when you need to use it, not anytime something is updated.
Yes thank you. I ruled out a flow field for the same reason I cannot calculate the values when needed. → they need to be selectable by sql, Webservices will not work either so no dynamics nav business logic available at this point.
I thought that there will be some downside to this problem but I came up with some kind of work around that works for now, and since there are only a handful external tables involved I will work for now with a long paramter list that either give me some pre calculated factors or -1 when the factors should be fetched from the database. At this way I can call the CalcTest function from the 50001 like this:
// collect number of rows of the 50001 and use this value for further calculation
END ELSE BEGIN
// use the value of arg1 for further calculation
END;
and so on for each external table involved
EDIT:
Ok screw that. This would work on some scenarios but not for all and i would have to do more workarounds to get the correct arguments to pass. i will look for another solution and use a cronjob like approach in the meantime
If I get you right, it is possible to create a flowfield with a formula that can calculate all you need? I mean that the calculation algorithm is not too complicated? I wild idea then. Maybe it is possible to create an index on the updated table (50001), and then use a simple SQL query to select data from the corresponding VSIFT view?
Thank you for your reply. I am afraid I cannot use a flowfield here, since the field must be able to get selected via a sql statement. flowfields are not “physical” on database layer. I could use the webservices tho, but they are too slow sometimes
Yes, I got it from your previous post that you need to select the field value from outside. That’s why I suggest using VSIFT views. Flow fields are not “physical” as “normal” fields are, but they can be easily retrieved from views created for each table index. That’s actually how NAV platform works - whenever you run a CALCFIELDS function, the platform runs a SELECT statement against the indexed view if there is one. And these views are automatically created when you create a table key.
@alexnir: Interesting suggestion, but I’m afraid SELECTLATESTVERSION won’t help either. SQL statement (INSERT / MODIFY / DELETE) is not executed until OnInsert trigger is completed. So, technically the last record is not in the database yet.
Thank you but as the documentation already says: “Forces the latest version of the database to be used.” this function won’t be suitable because the problem is that the data I want to use is NOT YET written to the database. Even with COMMIT the data will be written after OnInsert (or OnModify or OnDelete) has finished execting and with that every custom code including SELECTLATESTVERSION.
Hmm interessting. Does this execute C/AL code as well and does it perform better then using WebServices? I am thinking about to drop the requirement to make it selectable via SQL at this point. Making it fetchable via WebServices seems to be a tolerable alternative, just way slower. I will look into VSIFT views later that week and I would reply back if I know more.
Currently I am using a mixed approach. Executing at Item::onModify and, to have to kind of constitency I run a codeunit at a interval using job queues. In addition you can execute it manually for a specific item and for a list of items at RTC side, so users who work with the data have the chance to make it up to date and scripts/external software who will use the data are configured to run a bit after the cron job has been executed or rather trigger the project queue task a bit in before they fetch the data. I know it’s not that accurate but better than nothing for now.
This is my solution to your problem (one of them at least):
For this to properly apply, a value in the table 50001 must define if the record is valid for your calculation (quantity <> 0) or something.
All you would have to do is that the values are already written to the database when you are calling Item.MODIFY(true) or Item.CalcSomething();
In this case you could do the following in the “OnDelete” and “OnModify” trigger of Table 50001:
Quantity := 0;
MODIFY; // This line makes sure that the modification is written to the database
Item.MODIFY(TRUE);
In the OnInsert Trigger, the code could look like the following:
INSERT; // This line makes sure that the new record is written to the database
Item.MODIFY(TRUE); // Item can now calculate properly as the record is there already
DELETE; // This line makes sure that the new record is removed from the database as the database engine will write the record anyway at the end of the trigger
And in NAV 2016, this is not an issue anymore at all as there are events which can be subscribed to which are really happening “AFTER” the database transaction occured.
