Table with Lookup flow field, slow performance when filtered

bpweb123
bpweb123 Member Posts: 19
edited 2009-11-06 in NAV/Navision Classic Client
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

Answers

  • kapamarou
    kapamarou Member Posts: 1,152
    You can't do much about it. The only solution is to add it as a normal field and update it manually.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • matttrax
    matttrax Member Posts: 2,309
    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.
    Matt Traxinger - ArcherPoint
  • bpweb123
    bpweb123 Member Posts: 19
    Thanks all for the advice :)
    Will take note of that

Categories