I have a database with 3 companies en I’ve created a table where the total sales, total overhead costs,… of each company are stored in. This table has the data per company -property on NO. The table contains 3 records, identified by the field NameOfCompany wich contains the companyname that is shown on top of the screen (databasefunction COMPANYNAME).
When i post for example an invoice, some lines are inserted in the G/L Entry table. At that moment i want to recalculate the values of the fields in my new table. Here is the problem situated:
I can’t calculate the values with a flowfield because i need to use the databasefunction COMPANYNAME (wich can’t be used in properties).
I could calculate the correct values with this code but it is in the onvalidate trigger of my field so it is not automatically updated, only if i adjust the value in the table it wil be recalculated:
Just a small question to you. Why do you have the code on the onINSERT or onMODIFY trigger? I think that I would have created a batch job to be run periodically to update this instead. It would be much better performance wise also.
I agree with Erik. Every insert of a G/L Entry will trigger your code (at least that’s what you are wanting) an as almost every transaction posting will create a G/L Entry …
About your …
… in principle all postings to G/L Entry table are done by means of COD12 (Gen. Jnl.-Post Line). Here you will only find insert statements for the G/L Entry table using the default parameter which is by default FALSE, meaning that the OnInsert trigger is NOT called. So your code will not be triggered. As far as I always understood this it’s for the same reason I am pointing out above (on my agreement with Erik).
Have a look at for example the trigger InsertGLEntry in COD12.
I see, it’s indeed CU 12 that Writes the lines in the G/L Entry table (now it’s clear why even a simple message wasn’t displayed during posting [:D]).
If i understand of what both Erik as Luc mean is that performance will slow down if i write those code-lines in the codeunit, because they will be executed on every post (multiple times as there are multiple inserts on GL entry table per posting). But will running batch jobs be faster? I want to have my information always up to date so let’s say that i execute this batch every 10 seconds, won’t it be harder to have a lag every 10 sec than just wait a little longer during the posting? Or am i wrong?
Yes it is. The data in the new table must in fact be a instant replication of the data entered in the G/L Entry-table or must be as close as possible to that goal.
Pieter-Jan, why not build a function that’s actuality calculating the total G/L entries amount as by your code above. So do not store the total amount in the table you have created.
Instead build a form based on your table and add a text control that has as SourceExpression the function I am suggesting you to build. Actually you are now more or less creatring a FlowField yourself.
To answer Erik’s question, I’m actually developping a business process game in NAV for educational purposes. A quick adjustment of data is therefor essential. It’s a nice poroject, but some game-environment configuration sometimes aren’t following the business logic. Quite a challange[:)]
To answer Luc’s suggestion, It is in stead possible to do that (i’ve already tried it). But in the Page (i use the RTC version) you can only call the values of the company where you’re actually working in. What is for this problem enough, but the same table will also be used to calculate Market shares and to give the trainer a tool to compare evolution of all the companies.
