Hi all I want to delete some rows which are meeting some criteria from a table. This can be done through ProcessingOnly reports. It can be delete records through the following methods. I want to know which method is advisable and why ? Method 1. Dataitem sales OnAfterGetRecord() IF (Sales.DocType =‘Invoice’) and (Sales.Amount = 0 ) THEN DELETE; ELSE CurrReport.SKIP; Method 2. Dataitem: sales Variable: gltblSales Record Sales OnAfterGetRecord() IF Sales.DocType =‘Invoice’ and Sales.Amount = 0 THEN BEGIN IF gltblSales.GET(Sales.DocType,Sales.Nr) Then gltblSales.DELETE; END ELSE CurrReport.SKIP; What I believe is in Method 1. But I had seen the Method 2 in some places. Please let me know which is advisable and why? Regards Joseph MAthew
Sales.SETRANGE(DocType, Sales.DocType::Invoice); Sales.SETFILTER(Amount, ‘=%1’, 0); Sales.DELETEALL;
Sales.SETRANGE(DocType, Sales.DocType::Invoice); Sales.SETFILTER(Amount, ‘=%1’, 0); Sales.DELETEALL; This code will delete every invoice in your system!!! The Amount field is a flowfield. Unless it is calculated, it will be 0. Thus all the above code makes all invoices meet the criteria. Instead, on the OnAfterGetRecord of your report: Calcfields(Amount); If Amount = 0 Then Delete; Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117
He-He-He!!! :-))))))) Try it! It Works!!! One of the Navision’s tricks!!! Sales.SETRANGE(“Posting Description”, ‘Test’); Sales.SETFILTER(Amount, ‘=%1’, 0); IF Sales.FIND(’-’) THEN Sales.DELETEALL;
Interesting Questions Joseph_Mathew. I have a rule that goes something like “Never do delete with the record you use to travese a database.” So Method 2 would my preference. From year of programming in different environments, I’ve gotten into trouble when doing a delete on the record I’m using to traverse a database. I have a vague recollection of getting into trouble with method 1 on the character product, but have never done any real testing to see if Method 1 is a problem in the current version of C/SIDE. It just seems to me that is some cases is “might” cause a problem. Does anyone know for sure if it is safe in ALL cases to use Method 1? Jim Hollcraft NCSD, NCSP, MCSE, CNE, MCP, MST aka Skater http://drilldot.com Unauthorized Navision News
IF (Sales.DocType =‘Invoice’) and (Sales.Amount = 0 ) THEN DELETE; You do not say if Sales is a header or Line record! If you want to delete the currentrecord use a C/AL Global variable get the Record and delete that! // Sales Line Record IF (“Document Type” =“Document Type”::Invoice) and (Amount = 0) THEN IF SalesLine.GET(“Document type”,“Document No.”,“Line No.”)THEN SalesLine.DELETE(TRUE); // Sales Header Record! CALCFIELDS(Amount); IF (“Document Type” =“Document Type”::Invoice) and (Amount = 0 ) THEN IF SalesHeader.GET(“Document type”,“No.”)THEN SalesHeader.DELETE(TRUE); The DELETE(TRUE) will run the OnDelete trigger code, so if you should not be deleting the record it will ERROR! Polite Note: If possible use the same code style as Navision do then we will be able to give a better answer! SalesLine = “Sales Line” GenJnlLine = “Gen. Journal line” We will recognise these variables straight away! 
David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Edited by - David Cox on 2001 Jun 13 23:44:34
Invoice numbers need to be subsequent by legal requirements in many countries. By deleting entries you may run into some difficulties during audit. Anyway. If you delete documents, you need to delete not only headers, but also lines and comments. Regards, Alex
quote:
Originally posted by AlexZ: Invoice numbers need to be subsequent by legal requirements in many countries. By deleting entries you may run into some difficulties during audit. Anyway. If you delete documents, you need to delete not only headers, but also lines and comments. Regards, Alex
The DELETE(TRUE) will do all the above as passing the the TRUE parameter runs the OnDelete trigger code for the Header which will create the deleted document, Delete the lines and Comments. In the CodeUnits Navision often use the DELETE without the TRUE, to stop errors being generated as in CodeUnit 80, they then delete the Sub Tables within the CodeUnit! David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk
quote:
I have a rule that goes something like “Never do delete with the record you use to travese a database.”
Perfectly correct. You could end up in a “loop-and-never-come-back” as Navision cannot find the NEXT record of a record which doesn’t exist anymore. BTW: Same is true in modifying a field which is part of the sort-key you use to scan the records.
quote:
Does anyone know for sure if it is safe in ALL cases to use Method 1?
Yes. The following is simple and save: Mark the records you need to delete and delete the marked records after processing.
OnAfterGetRecord:
If Record_Must_Be_Deleted
Mark(True);
OnPostDataItem:
Markedonly(True);
Deleteall;
------- With best regards from Switzerland Marcus Fabian
quote:
Originally posted by fabian: OnPostDataItem: Markedonly(True); Deleteall; [/code] Marcus Fabian
Don’t forget to run the Delete Trigger OnPostDataItem: Markedonly(True); // DeleteAll; DELETEALL(TRUE); David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk
Hi David Cox I have still getting good answers and ideas from this fourm for my questions. Thanks for every one. I know David ,if I can give the exact object name is very convenient for all. But I am working on the German language version of Navision. So if I give the sales table name as ‘Verkaufskopf’(name for sales header table in german version), that may not be understandable for all than giving the name ‘Sales’. So sorry for the inconvience due to this. Thanks & Regards Joseph Mathew
Marcus, Just to clarify your comment, I think that what you said implies that method 1 exactly as written by Joseph Mathew in the first posting of this thread is generally not a good idea. Your example code is significantly different than the exact method 1. In terms of performance, I wonder whether spinning through the entire filtered table, marking records to delete, and then deleting all marked records with one call is faster or slower than deleting each record one at a time (with a 2nd record variable) as you go through the table. Also I wonder whether it would be a good idea to lock the table when using the “marked record” method. Jim Jim Hollcraft NCSD, NCSP, MCSE, CNE, MCP, MST aka Skater http://drilldot.com Unauthorized Navision News
Instead of deleting the record directly call a procedure which takes the record as a parmater (not Var’ed) and delete it there and you will still step through the loop. OnAfterGetRecord() IF (Sales.DocType =‘Invoice’) and (Sales.Amount = 0 ) THEN DeleteProc(rec); ELSE CurrReport.SKIP; PROCEDURE deleteproc(del : Record 27); BEGIN del.DELETE; END;
quote:
Just to clarify your comment, I think that what you said implies that method 1 exactly as written by Joseph Mathew in the first posting of this thread is generally not a good idea.
It is not a good idea as you are destroying Navisions internal file pointer during the process and leave it in an undetermined state! Let’s take an example: You have a list of entries ordered by a numeric key (1, 2, 3, 4, 5, 6, 7, 8, 9 etc.). Your task is to delete every entry where the key is a multiple of 4 (4, 8, 12 etc.) So let’s start: OnAfterGetRec the file pointer will point to key 1. You leave it and proceed to 2, 3, 4 Now you delete this record. Question: Where does Navisions file pointer point to? To record 3? To record 5? or to some unreferenced space in memory? If you are lucky enough it points to record 3 and next record read will be 5. If you are unlucky it points to record 5 and next one will be 6. That works for our example but in reality the report will never have a chance to see record 5!! If it points to unreferenced Memory you will have a program crash. No matter how Navision will handle this case, it is unsave programming and should therefore be avoided. ------- With best regards from Switzerland Marcus Fabian
Marcus What I said is completely different to what Joseph said, and show that you do not quite understand how navision deals with records. Navision only deals with copies of records and not the actual records themselves. When you us a GET, FIND or NEXT then the actual record is copied. When you use an INSERT or MODIFY the record is copied to the actual record. If you set up the key of a record and don’t insert it and call a next then the next record in the key is found. A report does the same as calling a NEXT statement after each record. Ok here is the bit that causes the trouble, when you call a DELETE then the actual record is deleted and the copy of the record is blanked out. If you call an NEXT on a blank record who knows what you would get! Presumably the first record, but that does not always seem to be the case. Navision could not possibly us a file pointer as you seem to imply as it is a multi-user system and the base table is always moving underneath it. If it did that then it would have to take a copy of the table, and that would be impractical. You should never think that you are modifying the actual record, but a copy of the record and all the multi-user stuff then start to make sense. Paul Baxter
Paul, I agree with you in all points except of course with your statement that I don’t know how Navision deals with records. Of course Navision works with copies. At least until a COMMIT is done. You can circle through a table, modify and delete many records, do an ERROR at the end and the rollback has restored al your data. The example with the filepointer was propably not the best one. I could have explained it using a list on a paper which is scanned manually to make it clearer that I’m talking about an example. But to put things right: Yes, of course Navision doesn’t work with flat files (and file-pointers) but with key-lists which reference to record entries. And yes, every transaction oriented db system has to work with temporary copies of data. Nevertheless I feel uncomfortable to delete data on who’s existance and integrity I’m going to rely an instant later. ------- With best regards from Switzerland Marcus Fabian
Something interesting to note that was mentioned waaay back in the topic was performance measurements with such operations. Even using keyed fields (on native keys), I sometimes find that on big tables (eg 1 Gb Item Ledger Entry tables), if you had to do some processing to find a result, or build a new journal based on whats in stock etc, it is often faster to set just some keys (eg document or entry type) and then to a FIND(’-’) and NEXT combination loop than to set the filters based on the fields you need to work with, rather than SETRANGE/FILTERing on all the fields you know. I think this depends on the performance of the drive and memory subsystems more than the database. That is, the amount of data the server can hold in memory at once before paging. When ILEs are of such a size its sometimes difficult to hold the whole thing in memory! Those that dont know about Navision locking and database commits should at least read the systems related PDFs on the CDs. Theres other docs out there too, but theres a good place to start in those few pages. Craig Bradney Technical Manager Navision Solutions & Services, Deloitte Touche Tohmatsu Email:cbradney@deloitte.com.au
Marcus It looks like I am going to have to try and convince you that what I am saying is correct. The client of Navision you are running only has a copy of one record in memory at a time. The rest of the records are held on a server away from your computer (well not strictly true as some caching takes place but the principle is correct). When you do an insert or modify then these changed records are held on the server and only incorporated into the database when a COMMIT occurs. This allows things to be rolled back if something goes wrong. So you only have a single copy of a record, if you do a NEXT navision goes to the server and gets the next record in the key. It does not matter that the record exists in the database or not. You can confirm this by writing the key of a record, which does not exist and do a NEXT and see what you get returned. So you do not depend on the integrity of the record when you do a NEXT (although you would if you did a MODIFY). Even your example of a list on paper is wrong. Navision works in a multi-user environment. The record on the table you are working on are constantly changing and the database engine deals with that. So what I am trying to say your client does not know any information about the table you are working on except the single record you are working on. That single record may not even exist in the database as you can init a record and set up the fields. That record may be different to the record in the database as before you do a modify, or even another user changing the record when you had it in memory. The record may be from a different company if you did a CHANGECOMPANY. So you can never think about the database in a single user way and that the client has knowledge that it does not. Only the server has full knowledge of what is in the database. You have to think about what the server is doing when you and other users are putting and getting data. Paul Baxter
One tricky part to this “NEXT” discussion is that any fields that are in the primary key but not in any secondary key are also used to determine the NEXT record when that secondary key is the current key. It is easy when developing to just think about the fields you explicit put into the secondary key. Jim Hollcraft NCSD, NCSP, MCSE, CNE, MCP, MST aka Skater http://drilldot.com Unauthorized Navision News
Yes Jim is right You have to think of secondary keys as having the primary keys apended to the end of them. This is because you can only ever have a single record having a primary key, but have multiple record having the same secondary key. Paul Baxter