Table with Lookup flow field, slow performance when filtered

bpweb123

Hi all,

I have a table with a "Trans Date" field which is a Lookup flow field to "Transaction Header.Date".

When I open the table and do a filter on "Trans Date", it will loop through all the table records which is very slow to go through millions record by record.
However when I do a filter on other field which has key set, it is very fast.

I tried to add a key on the flow field "Trans Date" but it does not allow. So, would like to ask how the performance can be improved if user want to do a filter based on this "Trans Date". Adding an actual "Trans Date" field will not be good as this may affect other operational areas.

I am using NAV Version 4 SP3 SQL.
Thanks in advance

kapamarou

You can't do much about it. The only solution is to add it as a normal field and update it manually.

David_Singleton

bpweb123 wrote:

Adding an actual "Trans Date" field will not be good as this may affect other operational areas.

Create an actual Trans Date field in the table and create a key based on that.

matttrax

Think about what a flowfield is...a calculated field. No way to know what it is without calculating the value. Now you want to place a filter on that field...how does the system know what falls within the filter and what doesn't? It has to calculate every value for every record.

As the other responders said, if you want a faster filter you will need to make this a normal field and update it accordingly.

bpweb123

Thanks all for the advice
Will take note of that