Hi everybody, I am looking for a consistent way of logging changes to a particular table. (I am using Attain 3.60) The obvious candidates are: 1. Change log management. Problem: only logs changes done by user manually and does not catch changes done in code.[V] 2. The Insert, Modify and Delete triggers. Problems: not always the code is called with Insert(true). Also I need both Rec and xRec and what I found out is that if you call SalesLine.Modify(true) from Sales Header then Rec and xRec for Sales Line are the same and I need both to figure out what has changed. [}:)] 3.OnValidate code of the fields you are after. Problem: not always fields are validated both in standard and especially in custom code. [}:)] I need this for a Navision SQL db so I was wondering if SQL has a similar OnInsert, OnModify, OnDelete triggers for the tables. I was thinking that in that case you could do some stored procedures on those triggers to log changes since any code in Navision would have to go through them. I am not a SQL programmer so what I am asking may be pretty dumb [:I]. Any comments would be greatly appreciated … P.S. Do not ask why I need it because that is a very loooooooong story [:D]. And believe it or not it is my idea and not the client’s [;)] Cristi
Just to get the ball running I will answer first to my question [:D] Well after some looking in SQL documentation I found out that the triggers exist. [:I][B)] So I guess my question would become has anyone ever programmed something using those triggers to update some other tables back in Navision? Cristi.
Hi Cristi, well I can’t imagine how loooong that story must be, I know that you are the sort of person that would have investigated every option. The issue I see here, is not the actually doing, with SQL the task you are working on should be possible, but what about the data. Once you track every single data change in the system, the database will be huge. There just must be another solution. Why not let us into a short verson [:0]
And if You use triggers in SQL You will loose them when You redesign a table in C/SIDE. So. Let’s hear it [8D]
Hi guys, Thanks for your replies. I guess David knows me very well. That topic was written in a moment of desperation after running out of all the options I could think off. [;)] But now I am calmer … Plus since I do not really know SQL and since Lars says that I would lose the triggeres when redesigning I gave up that idea. I’ll explain quickly what I needed and what was the compromise. One our clients has a lot of orders (say 20000 - 30000 Sales Lines) at any time (they are a retail distribution business). So allocating stock to them is not very simple. So they do not care to look at a Sales Order at a time but they wanted to see grouped versions of the Sales Lines. Say : Sales Lines grouped by External Document No and Sell-To and then the totals or By Item Style a.s.o And then they had options to allocate stock from that level. Now since I am using 3.60 I did a setup where you can choose which fields you want to group by, filter by, which fields to total and what other fields you want to see. Pretty cool if I say so myself [;)] since this way there was only one form and all you had to do is choose which grouping setup you wanted to see. But going through each Sales Line and adding up the fields was feasible for 300 lines and not 20000 (it was taking hours and since it was using a temporary sales line it was taking up a lot of memory too). And this form is supposed to be on the fly. The next step was prebuilding the grouped values in another table and then just calcsum on them. If prebuilding was already done the calcsum part was taking at most a minute depending on what you were grouping so that was acceptable. But the prebuilding took at least an hour to run so you could not do it all the time. So then you do it once and then I had to write code that after that the prebult data would be updated any time one of the group by fields was done. So this brought me to my problem of trying to log changes for Sales Line (BTW David it was only Sales Line that I wanted in this case). There were more complications after that anyway … To keep the pregrouped data in synch you needed a count of each combination. So when changing a field its previous combination count needed to be decreased by 1 (and deleted if it became zero) and the new combination count increased by 1 (or created if it did not exist). This logic is similar with how Navision is holding SIFT values for SQL … If you used validation of fields to update the problem was not all the times the fields were validated. Say if you revalidate “No.” the system does INIT and assigns values and does not revalidate [xx(] Then I said I will use modify. But I found out that if you call SalesLine.modify(true) from Sales Header for instance, both Rec and xRec for Sales Line are the same [}:)] But if you were using both the Validation of fields and the Modify then you could potentially double count the combinations when both validation happened and Modify(true) took place …[B)] So at this point I wrote the topic [:D] Actually I went with the option of using both Validation and Modify and I wrote code that knows how to avoid double counting … Or at least so I think [:p] And since they will be running some routines at night from time to time we’ll run then the routine to recalculate the preprocessed data just so we correct any problems. Actually while writing this reply I realized that SalesLines are maybe deleted when posting and that there is no Delete(true) there [:(] Anyway this is the story gentleman. In case you were wondering how I had the patience to write this, besides the fact that I do not have a life [:D], I spent with this screen at least 2 weeks shaving off second after second, learning why some clever code worked fine in native Navision and it was slow like hell in SQL (1-2 orders of difference in speed). So I needed to get it off my chest [8D] Anyway I warned you that it is a loooooong story … Better do some work now … Problem is that I spent so much time on this thing that I forgot most of the stuff I knew about the actual system I was supposed to build [:p] Cristi
Sounds very interesting. Actually I had a client with a very similar need some time ago. I solved the issue somewhat differently, but with a similar effect. By the way, what line of business are this client in? Anyway good luck.
They are what is called Retail Distribution. They sell stuff to big chains (Macy’s, JC Penney a.s.o) How did you do it? Cristi
These people sold bedding pillows and accessories, so their needs were probably a lot more complicated. Probably not relevant to what you are doing.
Would it maybe be possible tp try a solution where you let the SQL server group and sum your saleslines on its own and not calculate the grouping and sums from within C/Side. In lots of situation SQL server will do this faster on its own then from within Navision. If you find that sql server is doing this faster then Navision you could f.ex. write a stored procedure that did the grouping and the call that sp everytime you need to update the data in question. The sp could update a Navision table that you will be able to handle form within Navision. I use this technique several places and it works very fine where Navision is slow and Sql server is fast at doing sum and groups.
Hi Inge, Besides the fact that I am not a SQL programmer I do not think that option would work for me. The biggest problem is that this screen needs to be updated on the fly. So running a stored procedure from time to time is not really good. I actually ended writing code to update data when Sales Line is changed. Thanks to everybody for their input… Cristi
Would be possible to run the SP every time the form is opened. But…nothing is better then a solved problem 