Using FlowFields Efficiently

Business Central/NAV User Forum
1

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:

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

[/code]

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!

2

This must be slow.

The filter “Description=FILTER(*Arch)” causes a full table scan in the sales line archive table.

You rather should create an additional field in the Sales Line and Sales Line Archive table which is set to a certain value when the condition “description contains Arch at the end” .

Then change your flowfield to check for this new field instead.

You will see it will be much, much faster.

3

So in the Sales Line and Sales Line Archive Table, what data type would this new field be? Would it be a boolean type Exist FlowField?

Thanks for your help Thomas!

4

Hi

First you may check to see if there is any other fields available for you that could let you omit filtering on Description!

How Arch comes at the end of your description? Does user enter it or that description is pulled from somewhere?

What that Arch standas for? Is it abrv. for Archive? If positive, then when you are already reading from Archive Table, aren’t all your lines archived lines already?

Filtering by Type and Item could be an option?

5

I could also filter by No. such as this:

 Min("Sales Line Archive"."Shipment Date" WHERE (Sell-to Customer No.=FIELD(No.),No.=FILTER(ASK1000)))

The description is manually entered I believe, it covers a range of items so I used it instead of FILTER(ASK1000|ASK2000|etc) but now I realize it’s a slower search than No., correct?

Do I need to edit any of the keys/SumIndexFields to make these FlowFields more effective?

Thanks Jay!

6

your welcome!

I believe your table is already Indexed on “Sell-To Customer No.” you may select this key by record.SETCURRENTKEY(“Sell-To Customer No.”) in your report before calculating flowfields to see if you get better performance otherwise you could update the key and add “Shipment Date” to it as well.

sumindexfield is not applicable for this scenario.

7

Never do the flowfield creation on Description field rather than use another field.

8

I would suggest to use an option field rather than a bool field as it allows to extend the filtering groups for the future.

For your case I would do the following:

Add the same Option field to Item, Sales Line and Sales Line Archive Table, having a blank option + the option you name as to be filtered on (such as “Arch”).

Then you can just go and assign the “Arch” option to the item - make sure to transfer that information on the “OnValidate” trigger of the number field to the “Sales Line” table.

When archiving, this field should be transferred automatically to the Archive table as well.

Create a key in the Sales Line Archive table which starts with the shipment date, followed by Sell-to Customer No. and then the new Option field.

This should give a much better speed on your reports.

9

It would be good if you write code in the report ,where you have options of sorting the data and the performance will be G8.

Cause you are going to develop various reports.

10

Thank you all for your help! I have a much better understanding of flowfields now.