Is posible to create a filter in a field with SETFILTER that retrieve only records with a substring of this field ? i.e.: table products whith 5 products with this codes: AB167YX1 AB167XX2 AB150XX12 CD123YX33 SA140XX4 and we want create a filter for retrieve only those that contain 167 in the 3 to 5 position (the 1st an 2nd). In (most) SQL is: LIKE ??167* Any sugestion will be appreciated. Thanks. AngelBel
The way I have done this is by creating a text variable i.e. filtertext filtertext := ‘167’; or filtertext := ‘’+ anothervariable + '’; setfilter(field,%1,filtertext); hope this helps.
No Problem ! Try this out ------------------------- Setfilter(products.Name,’??167*’); should work ?! ( i think) if this doesn t work try to define as an variable and now use following this code varname := ‘??167*’ Setfilter(products.NAme,%1,varname);
According to Navisions documentation and my knowledge then it is not possible to search for a specific sub string in a certain position as AngelBel asks. Yes “*167” would show you all fields with 167 in it, but also fields like ABZ167XX2. The only way I can think is that you do the search with the “*167” (not “167”) and the uses STRPOS to check the position of the 167 sub string. ============ Best regards, Erik P. Ernst, webmaster Navision Online User Group
It is not right. “*167” would not show you fields like ABZ167XX2, it whold show fields that ends on 167. And code:
varname := '??167*'
Setfilter(products.NAme,%1,varname);
work properly. Valentin Gvozdev BMI Inc.
guau!!! I think that this is the first developer forum where the answer may arrive before the question!!! Really, thank you very much to all for your atention. Well i’m fully convinced that ??167* not works (attending the 'plain’c/side documentation) that I dont try this possibility but… YES IT WORKS !! he,he,…Valentin and Alex you know some more ‘hidden wonders’?? this is for the admin: how about a new section ‘undocumented’ ?? Thank you again Angel Bel angelb@redestb.es
Wauh. That’s amazing. I love to admit that I’m wrong, if the result is good enough. I would like get a list of undocumented “features” like this. I just had one problem. I tried (with the old list of NOLUG members) to apply this filter ‘???@bfc*’ to the list of email addresses. That didn’t work. If I used this filter ‘???bfc*’ then it worked. I didn’t know that Financials also had a problems with @. ============ Best regards, Erik P. Ernst, webmaster Navision Online User Group
Going slightly off topic here, Is there a limit to the length of a filter string ??? reasoning I have database where we hold certificates of conformance that relate to the lot number This table will grow with time adn contain xxx hundred records or more. What i am tryingto do is return a list of these lot numbers and then apply them to the item ledger entry table i guess i am going to have to build a string of all the matches and then apply the string to the table, but how long can the filter be. Alternatively i could try following each string returned to see if it still existed as a positve stock quantity in the ledger table and then create a seperate string to hold the new filter ? any one done anything simillar before ? Bruce
I don’t know how in others versions but in 1.30 limit is 250 chars. Keep in mind that setfilter with complex filter string works N times slower that setrange. If you wanna sellect all rec from table B that matches recs from table A use like this: create tempory table var tB from table B. Repeat B.setrange(field,A.field); tB.insert all records from table B; until a.next = 0; in table tB you get all matched recs from B
Erik, Th problem that Navision has with ‘@’ in a filter is not really a problem. The ‘@’ functions as a control char and makes the search case-insensitive IIRC. Dennis van Es PerCom: Research & Development BV The Netherlands Edited by - Dennis on 2/28/00 9:17:22 AM
My suggestion is that if you are going to do a SETFILTER for more than 250chars you should MARK the records followed by a MARKEDONLY. Palle Arentoft, Team Manager Aston Naviteam A/S Denmark Email : par@astonitgroup.com