SETFILTER not working on temporary table

kaspin

Hi,

Tried to loop thru forum, but could not find the answer, so what am I missing with this code:

IF recGLEntry.FINDSET THEN REPEAT
  recTempGLEntry := recGLEntry;
  recTempGLEntry.INSERT(FALSE);
UNTIL recGLEntry.NEXT = 0;

recTempGLEntry.RESET;
MESSAGE('TMP 1: Count %1',recTempGLEntry.COUNT);//TMP 1: Count 18302
recTempGLEntry.SETFILTER("G/L Account No.",'106999..108998');
MESSAGE('TMP 2: Count %1',recTempGLEntry.COUNT);//TMP 2: Count 0

recGLEntry.RESET;
MESSAGE('DB 1: Count %1',recGLEntry.COUNT);//DB 1: Count 18302
recGLEntry.SETFILTER("G/L Account No.",'106999..108998');
MESSAGE('DB 2: Count %1',recGLEntry.COUNT);//DB 2: Count 43

I don't understand why count is 0 in TMP 2?

Using SQL 2008 R2 and NAV 2009 R2 on 64-bit windows 7.
](*,)

mohana_cse06

I am also using same configuration system and I am getting same Count for both temp and original G/L Entry..

Did you also try with

recTempGLEntry.SETFILTER("G/L Account No.",'%1..%2','106999','108998');

SPost29

you can't do a setfilter without a find of some kind.
edit -- sorry bad info count will do the work of find --- edit
Steve

koubek

Like mohana said, you should use different notation while filtering using SETFILTER. In every way i propose you to use in this case SETRANGE that will provide you better performance.

recTempGLEntry.SETRANGE("G/L Account No.",'106999','108998');

.Moreover you won`t have any problems with the notation and the return dataset.

gerrykistler

Did you provide the entire code? I copied your code and ran it (just changed the range of the account numbers to match the data I had) and I get the exact same count for the temp variable as the non-temp variable.

The comment about using SETRANGE vs SETFILTER is correct for performance, but would make no difference in the results.

Also a FIND statement is not required for the COUNT to work.

Reinhard

Also for debugging instead of

MESSAGE( COUNT )

type of statements, what I like to do instead is put in

FORM.RUN(0,recTempGLEntry)

That way you can see, is it a problem with the count? or maybe the filters?

David_Singleton

kaspin wrote:

Hi,

Tried to loop thru forum, but could not find the answer, so what am I missing with this code:

...

I don't understand why count is 0 in TMP 2?

Using SQL 2008 R2 and NAV 2009 R2 on 64-bit windows 7.
](*,)

When you use a Temporary table the sorting and filtering is done on the client using the same logic as in Native database, where the DB table will be sorted in SQL fashion. I am guessing from your location that maybe you ran this code on a Swedish database, and if I remember correctly Sweden uses Text for the GL account no. so that you can conform to Swedish accounting requirements for sub accounts.

So if you have accounts like

12
121
12100
12123
123
12388
13
139

etc they will sort differently and thus filter differently and give you a different count.
12
13
121
123
139
12100
12123
12388


I think the best advise in this thread is that from Reinhard, follow his suggestion and you should be able to solve this.

gerrykistler

David Singleton wrote:

kaspin wrote:

Hi,

Tried to loop thru forum, but could not find the answer, so what am I missing with this code:

...

I don't understand why count is 0 in TMP 2?

Using SQL 2008 R2 and NAV 2009 R2 on 64-bit windows 7.
](*,)

When you use a Temporary table the sorting and filtering is done on the client using the same logic as in Native database, where the DB table will be sorted in SQL fashion. I am guessing from your location that maybe you ran this code on a Swedish database, and if I remember correctly Sweden uses Text for the GL account no. so that you can conform to Swedish accounting requirements for sub accounts.

So if you have accounts like

12
121
12100
12123
123
12388
13
139

etc they will sort differently and thus filter differently and give you a different count.
12
13
121
123
139
12100
12123
12388


I think the best advise in this thread is that from Reinhard, follow his suggestion and you should be able to solve this.

This type of action is true whether it is Text or Code and is because of SQL. In the example provided by the OP, it does not appear that the G/L Account Nos. vary in length. Also whatever sorting issues there are will be the same whether it is a temporary variable or not. It has to be something else.

kaspin

You are right David, it is about sorting somehow, it works differently. Running form like Reinhard suggested confirmed that.

Gerry, if sorting does not affect filtering, what could be reasons for this behouvior? Account numbers are different length.

Is there a way to make sorting similiar on client side temporary table as it is on SQL? All accounts are numerical values on Code Data Type (20) field.

udayrmer

my suggestion plz reset recGLEntry variable, because it may have some filters applied.

David_Singleton

kaspin wrote:

You are right David,

kaspin wrote:

it is about sorting somehow, it works differently. Running form like Reinhard suggested confirmed that.

Gerry, if sorting does not affect filtering, what could be reasons for this behouvior? Account numbers are different length.

Is there a way to make sorting similiar on client side temporary table as it is on SQL? All accounts are numerical values on Code Data Type (20) field.

As far as I see it this is a bug, and one that needs to be fixed. At least before the release of Nav7.

in Fin.exe this sorting is correct and logical, but in FinSQL.exe, when sorting and filtering on a Temporary table, Navision should look at the SQLDataType field and the collation of the database and apply sorting according to this property.

David_Singleton

Reinhard wrote:

Also for debugging instead of

MESSAGE( COUNT )

type of statements, what I like to do instead is put in

FORM.RUN(0,recTempGLEntry)

That way you can see, is it a problem with the count? or maybe the filters?

:thumbsup:

ufuk

I am not sure if sorting order is the problem but assuming it is the reason:
As David said, temporary record works as native and this may change the sorting order. One workaround solution is copying the No. field value to Name field when you create temporary record and filter it using Name field. This way the sorting logic can change.

gerrykistler

Well, I stand corrected. I learned something new.

You can fix the sorting so SQL acts like native if all the G/L Account Nos. are integers (no alpha characters). You can change the SQL Data Type to integer then it will sort as you would expect.