Hi all.
How can I check if a value complies with a filter? I would really like to have a function like
TESTFILTER(Filter, Value) that returns true/false. For example,
TESTFILTER('CA*', 'CA.00234') would return true and
TESTFILTER('CA*', 'CB.00234') would return false.
I have implemented data security on the customers table using a table called User Filters. The table contains 2 fields: User ID and Filter. For example there is a line User ID: USER1, Filter: CA*. So, when USER1 posts a document or journal, the system checks if the specific user can post for the specified customer.
I have put the code in Codeunit 11 (Gen. Jnl.-Check Line). Whenever a journal line is checked, I check if the Customer is in agreement with the User Filter. The code is:
IF "Account Type" = "Account Type"::Customer THEN BEGIN
GenJnlLine3.RESET;
GenJnlLine3.SETRANGE("Journal Template Name", "Journal Template Name");
GenJnlLine3.SETRANGE("Journal Batch Name", "Journal Batch Name");
GenJnlLine3.SETRANGE("Line No.", "Line No.");
IF UserFilter.GET(USERID) THEN BEGIN
GenJnlLine3.SETFILTER("Account No.",UserFilter.Value);
IF GenJnlLine3.ISEMPTY THEN
FIELDERROR("Account No.");
GenJnlLine3.SETRANGE("Account No.");
END;
END;
The code works well when the user posts journal lines.
But it doesn't work when I post a Sales Invoice. The Journal Line is created by the Sales Invoice on the fly, so it doesn't actually exist. So the ISEMPTY function ALWAYS returns false, even if the customer is within the specified filter.
Any ideas?
Thanx
Jorgito
Answers
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
It depends if you will use get if the table is not empty and how big is chance that some record will be there... ;-)
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
thanx for the solution. I never thought of it this way until now.
May be my two cents...
Can't we just say: if you need the data of the record, go for GET, if you don't need data, but you just have to know if any record exist ... go for ISEMPTY?
ISEMPTY + GET are two roundtrips to the server ... which I would avoid in any case. (this is also only my personal opinion)
Anyway, if I'm not mistaken, the SQL Statements are something like:
ISEMPTY: SELECT TOP NULL FROM Table
GET: SELECT * FROM Table
So in general, GET is slower.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
No problem.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
In this case there is no need for the get, because we don't need any info.
But it is a good idea if you are in a loop and you are checking mulitple records, is to insert them into a temp and do the filter on it.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
When using temp tables you avoid unnecessary roundtrips to the server ... which is usely a good idea
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
If the number of records are in 1000's the records will be cached anyways so even when you do a get twice on a record it doesn't go the server.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n