Properly Using FlowFields

ggkohl

Hey everyone,

I'm trying to understand how to efficiently use FlowFields. Right now I'm building various reports that pull from the Customer table. Within the Customer table I have created a few FlowFields but performance seems to be an issue. For example one of the FlowFields has the calcformula:

Min("Sales Line Archive"."Order Date" WHERE (Sell-to Customer No.=FIELD(No.),Line Discount %=FILTER(100),Description=FILTER(*Arch)))

I know this isn't the best way to pull the first date of a specific type of free order. Could anyone suggest a better way of pulling the same information?

Also, what Keys/SumIndexFields need to be set with a FlowField such as this?

Thanks for all your help! P.s I've been searching the forum for a while for a question similar to this but with no luck. If I'm reposting a similar question, let me know!

FlowerBiz

Without a doubt, your performance issue is directly related to your Description filter having a wildcard at the beginning. This forces virtually every single record in the table to be examined. Is there any way you can search using a range of product numbers instead? If not, then the way I would address this is to create a new field in your table and assign a value to this new field during inserts, modifies, and renames (if applicable) based on the last part of the description. Then use this new field in your flowfield without any wildcards. You'll have to run through the existing data one time and populate this new field after its creation.

The number one way to instantly improve performance using filters in an SQL environment is to avoid wildcards and <> statements. And in code, anytime you can use SETRANGE instead of SETFILTER, your results will be (generally) better. Of course these are blanket statements and there are always exceptions.

FlowerBiz

Regarding your question about the best key to use in your example, I would create one where the "Sell-to Customer No." is the first field in the index, "Line Discount %" is the second, followed by "Description" and finally "Order Date". As I said in my earlier post, I would do everything I could to avoid using a filter that had a wildcard in the beginning. Using MIN or MAX should not affect performance if your index includes the field you are referencing (preferably located near the end of the fields in the index).

Regarding Sum Index Fields (SIFT) and their Indexes, these are only used to keep track of numeric totals at each complete index value. Simply put, Navision can derive totals very quickly between differing index values as opposed to traversing the actual table data, one record at a time. It is very impressive technology and implemented brilliantly in the very latest versions of Navision. You must include each field used in the SIFT filter(s) in the SIFT index. In other words, you are only allowed to filter fields that are part of the SIFT index. If you try to filter using a field that is not part of the SIFT index, Navision will give an error and will not allow any further progression. Also, you can not use wildcards in SIFT filters...only ranges. Navision documentation states that the order of fields in the SIFT index does not matter but this is not really true. I try to arrange my SIFT indexes in a top down manner so the most general index that I would ever total by (like general business posting group) is at the beginning and the most specific is at the end (like posting date). Performance is never an issue and my G/L entry table has over 22 million entries spanning many years.

I'll finish this post by stating that I am only a heavy user and developer of SQL-based Navision applications and the above comments are based on my observations of working with databases that are over 50GB in size. I'm sure that more seasoned professionals can give better direction and insight. You can learn tons from this forum and I wish you luck.

ggkohl

Thank you so much for your advice! It's all starting to make a lot more sense and I'm sure by browsing the forums I will become increasingly better at NAV development.