Hi! I have Table 12401, and I need to select all records from it where Field “A” = Value OR Field “B” = Value. And after that I need to show records on the Form 12401. How to do this in Navision? In SQL it wiil be: Select * From 12401 where “A” = Value OR “B” = Value
You can apply filters to the table. Use record.SetFilter function in code or SourceTableView-TableFilter property in a form.
Hi, u can use setrange as tablenmae.setrange(“Field A”,value); tablenmae.setrange(“Field b”,value); i m not sure where u want to show on the form, while opening a form or later. if u want to show it when the user is opening a form, then u can write the code on “OnOpen” trigger of the form, else u can give 2 options on the form and let the user select the value and filter data accordingly. In second case, u need to write the code on “OnValidate” trigger of the textbox. Cheers
hi If I do so: u can use setrange as tablenmae.setrange(“Field A”,value); tablenmae.setrange(“Field b”,value); I have filtered Table by Field “A” ANDField “B”, but I need to filter table by Field “A” OR Field “B”
There is no such thing in Navision as an “OR-filter” (at least not to my knowledge [;)]). The best way to achieve the desired result is to use record marks, i.e. you need to loop over the source table and put a mark on each record that should show up on the form, then call MARKEDONLY and make the form only show the marked records.
Thanks, xorph ! I’ve Already do this and it’s worked! [;)]
I’m not familiar with table 12401. But in general, if you’re using the MARK functionalty with LOTS of records it’s crucial, performance-wise, to use the primary key. To illustrate: In your particular case the code might look something like this: // Filter on FieldA SETCURRENTKEY(FieldA); SETRANGE(FieldA, ValueA); // Mark records IF FIND('-') THEN REPEAT MARK(TRUE); UNTIL NEXT = 0; // Remove filter again SETRANGE(FieldA); // Same thing for FieldB SETCURRENTKEY(FieldB); SETRANGE(FieldB, ValueB); IF FIND('-') THEN REPEAT IF NOT MARK THEN MARK(TRUE); UNTIL NEXT = 0; SETRANGE(FieldB); // Important- Use the primary key SETCURRENTKEY(PrimaryKey); MARKEDONLY(TRUE), // Do what you have to do (present in form, print - whatever) Finally, Always consider using a temporary table as an alternative to MARK [;)]
I have met this problem too, and solved it using a different method. You can create a new field on the table (set in the OnInsert & OnModify triggers) which concatenates the values in field A and B. You then need to apply the filter to just 1 field. Andy
Dear avick, Did you search the forum before asking your question. I remember seeing this discussion a few times before…
Hi All! I’ve solved my problem 2 days ago. Thanks to All!