Test a value against a filter

jorgito · 2007-11-26

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

ara3n · 2007-11-26

If you are filtering against a customer, why are you looking at the gen. journal line. Shouldn't you look at the customer table instead?

ara3n · 2007-11-26

Your code should look something like this.

IF "Account Type" = "Account Type"::Customer THEN BEGIN
    IF UserFilter.GET(USERID) THEN BEGIN
    MyCustomer.FILTERGROUP(0);
    MyCustomer.SETFILTER("No.",UserFilter.Value);
    MyCustomer.FILTERGROUP(1);    
    MyCustomer.SETRANGE("No.","Account No.");
   IF MyCustomer.ISEMPTY THEN
        FIELDERROR("Account No.");
   END;
END;

kine · 2007-11-26

In generally, you can do that by using some temporary table, insert the code there, set filter - if table is empty, result is false, if you see the record, condition is true... ;-)

ara3n · 2007-11-26

is empty is faster than get, or is get faster than isempty?

kine · 2007-11-26

ara3n wrote:

is empty is faster than get, or is get faster than isempty?

It depends if you will use get if the table is not empty and how big is chance that some record will be there... ;-)

ara3n · 2007-11-26

I couldn't' understand what you meant. could you clarify?

kine · 2007-11-27

it means, that if you have something like:

if not Rec.IsEmpty then begin
  Rec.GET(...);
   ...
end;

it is better to do the GET without IsEmpty, if probability that the record exists is high and do GET in 100% where in 80% the record is found. If probability that the table Is Empty is higher, it is better to do IsEmpty in 80% of cases and in rest to do IsEmpty + GET. For me the IsEmpty and Get is on same level of performance (may be IsEmpty is for me little "easier" for the engine). But it is not based on measure, just on my personal feeling...

jorgito · 2007-11-27

ara3n,

thanx for the solution. I never thought of it this way until now.

Waldo · 2007-11-27

kine wrote:
it means, that if you have something like:
if not Rec.IsEmpty then begin
  Rec.GET(...);
   ...
end;
it is better to do the GET without IsEmpty, if probability that the record exists is high and do GET in 100% where in 80% the record is found. If probability that the table Is Empty is higher, it is better to do IsEmpty in 80% of cases and in rest to do IsEmpty + GET. For me the IsEmpty and Get is on same level of performance (may be IsEmpty is for me little "easier" for the engine). But it is not based on measure, just on my personal feeling...

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.

ara3n · 2007-11-27

jorgito wrote:

ara3n,

thanx for the solution. I never thought of it this way until now.

No problem.

ara3n · 2007-11-27

Waldo wrote:
kine wrote:
it means, that if you have something like:
if not Rec.IsEmpty then begin
  Rec.GET(...);
   ...
end;
it is better to do the GET without IsEmpty, if probability that the record exists is high and do GET in 100% where in 80% the record is found. If probability that the table Is Empty is higher, it is better to do IsEmpty in 80% of cases and in rest to do IsEmpty + GET. For me the IsEmpty and Get is on same level of performance (may be IsEmpty is for me little "easier" for the engine). But it is not based on measure, just on my personal feeling...
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.

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.

Waldo · 2007-11-27

ara3n wrote:

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.

When using temp tables you avoid unnecessary roundtrips to the server ... which is usely a good idea

ara3n · 2007-11-27

Waldo wrote:

ara3n wrote:

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.

When using temp tables you avoid unnecessary roundtrips to the server ... which is usely a good idea

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.

Test a value against a filter

Answers

Categories