Create Form with multi-filter

adl62

Hello everybody,
i use Navision attain 3.6, i have to create a form that shows the records from table, filtered by three fields (field1, field2, field3), at least that one or more of that fields is not blank.
For example in language SQL is like if i have to execute this: " Select * from table where filed1<>'' or field2<>'' or field3<>'' ".
Thanks in advance.

kriki

No, it is not possible.
You can read the records (you need to loop the records 3 times (=once for each filter)) and then
a)mark the records and then show only the marked records.
b)or put the records in a temptable and show only the temptable. If you are using SQL, this is better for performance, but if you need to change the records, it is better to use method a)

matttrax

There is unfortunately no easy way to do this.

Filter on Field 1, mark all records. Do the same for the remaining 2 fields (don't clear the variable in between). Set marked only to true on your form.

If you don't want to mark them you could use a temp table also.

adl62

Hello,
i find the possible solution at my question, and write the solution code:

LocRecTABLE.RESET;
LocRecTABLE.SETRANGE("key1","key2");
IF LocRecTABLE.FIND('-') THEN
BEGIN
REPEAT
IF (LocRecTABLE.field1 <> '') OR
(LocRecTABLE.field2 <> '') OR
(LocRecTABLE.field3 <> '') THEN
BEGIN
LocRecTABLE.MARK(TRUE);
LocRecTABLE.MODIFY;
END;
UNTIL LocRecTABLE.NEXT =0;
END;
LocRecTABLE.MARKEDONLY(TRUE);
FORM.RUN(FORM,LocRecTABLE);

I hope you can understand my solution.
Thanks everybody.

Belias

LocRecTABLE.MODIFY;

you don't need this instruction to mark records:
it slows down the process, it is useless, and it can be dangerous.

matttrax

Definitely take out the modify. Depending on how many records you're processing you'll probably notice a good improvement in execution time. Each modify is a write to the database and can slow things down considerably.

adl62

Hello,
it's true that my solution could be slow on many records, but the "Modify", even if it seems unnecessary, it is NOT dangerous because is "FALSE" and so it doesn't start the trigger on the record of the table .

garak

adl62 wrote:

Hello,
it's true that my solution could be slow on many records, but the "Modify", even if it seems unnecessary, it is NOT dangerous because is "FALSE" and so it doesn't start the trigger on the record of the table .

BUT u modify the record! So, with every mark and modify u fired up an "Update" command to the sql server.
Remove the modify statement. It's not needed when u mark a rec.

Belias

i said:
-DANGEROUS: some other developer could have to modify your code in the future and maybe he doesn't expect a modify in that place...and who knows if that developer write some code which can be dangerous with that modify?
-USELESS: see garak's post
-SLOW: see garak's post (you don't have to modify if you mark a record, "MARK" is not a field