I’m trying to display table data on a form. I need to filter on two date fields, excluding from view ONLY those records where the two date fields each contain data (I don’t care what the actual date is). Essentially, the logic would look like this: DateField_A DateField_B (Action) Null Null (view rec) Null Not Null (view rec) Not Null Null (view rec) Not Null Not Null (exclude rec) The end-user is able to insert a date into either of the fields, depending on what’s being done. When both fields receive a date entry, the form needs to update to remove the newly modified record from view, to satisfy the above logic. Although I’ve come close, I’ve not been able to find anything in the forum that quite fits my needs. Any suggestions? Thanks!
Ooops! Posting removed the formatting for the logic table, which makes it difficult to read. Let’s try this: DateField_A…DateField_B…(Action) Null…Null…(view rec) Null…NotNull…(view rec) NotNull…Null…(view rec) NotNull…NotNull…(exclude rec) Thanks!
I have ran into a similar issue before. One resolution to this that I have found is to write code to run through each record to test the criteria, it is slow when you have a large number of records but it is a start. I have added code below to show how I have accomplished this in the past. You will have to test where in the form this code works best, it might be useful to put this into a function and call it more than once. RESET; //reset the record //add more filters if needed //test the criteria needed and mark the record if it falls //within the criteria IF FIND(’-’) THEN BEGIN …REPEAT …IF ((“Date A” = 0D) AND (“Date B” = 0D)) OR …(("Date A " = 0D) AND ("Date B " <> 0D)) OR …(("Date A " <> 0D) AND ("Date B " = 0D)) …THEN …MARK := TRUE; …UNTIL NEXT = 0; …//set the filter to only show the marked records …MARKEDONLY := TRUE; END; Edited by - timw on 2001 Oct 19 19:01:52
You can write the IF-statment more simple: IF (“Date A” = 0D) OR (“Date B” = 0D) THEN …MARK := TRUE; or write the MARK-statement as MARK := (“Date A” = 0D) OR (“Date B” = 0D); Otherwise you can write own find-next in the OnFindRecord and OnNextRecored on the form.
Hi there, Filtering the records like this would be far the easiest way but has two disadvantages: 1. It is only a snapshot, a record that changes after he is excluded by the marking will never come back on your form. 2. Navision has to go through all the records before he knows which records he has to show and which not. This may take too long with a huge table. The first problem can not be solved when you choose to mark the records (unless you want to do the marking each time you go to another record). In some cases the second can. When for each field, you are filtering on, is a key present in de table you can speed up the marking to inspect only the records that are in range. First set the currentkey to “Date A”, set a filter “Date A” = 0D mark them all and do the same for “Date B”. You may also consider adding another boolean field which is checked or not when DateA and DateB are validated.
"In Filter" := ("Date A" = 0D) OR ("Date B" = 0D);
Now it is possible to set a filter on “In Filter”. But the one I like the most is the one that Bjarne suggested.
**OnFindRecord(Which : Text) : Boolean** Rec2.COPYFILTERS(Rec); Rec2 := Rec; IF Which = '+' THEN SearchDirection := -1 ELSE SearchDirection := 1; Rec2.FIND(Which); IF NOT Rec2InFilter THEN REPEAT UNTIL (Rec2.NEXT(SearchDirection) = 0) OR Rec2InFilter; IF NOT Rec2InFilter THEN EXIT(FALSE); Rec := Rec2; EXIT(TRUE); **OnNextRecord(Steps : Integer) : Integer** Rec2.COPYFILTERS(Rec); Rec2 := Rec; REPEAT CurrSteps := Rec2.NEXT(Steps); UNTIL (CurrSteps = 0) OR Rec2InFilter; IF CurrSteps <> 0 THEN Rec := Rec2; EXIT(CurrSteps); **Rec2InFilter() : Boolean** IF (Rec2."Date A" = 0D) OR (Rec2."Date B" = 0D) THEN EXIT(TRUE);
Does anybody made something like this where the Crtl+F function acts normal? In my example when you search for B, in the following recordset, the active record will be C. It should say that the search item was not found. A is in filter B is not in filter C is in filter Here are the examples: Download Reijer.
hello, You can simply add a new field to your table. For example : fldShowrec, type boolean, initialvalue = TRUE. Then on the validate-trigger of DateFieldA and DateFieldB you check if both or not null. If so, you change the field fldShowrec to FALSE. You make a key with the field fldShowrec. And then you set your filter SETRANGE(fldShowrec,TRUE) Regards, Jan van Puyenbroeck