Calculating a flowfield in another form

Business Central/NAV User Forum
1

I currently have a report that generates from a our “Sales Line” table. However, I have Item information contained in an “Item Unit of Measure” table which I would like to indicate on this report and run calculations with other fields contained within the Sales Line table.

I have created a flowfield within the Sales Line table linking the information in the Item Unit of Measure table, however, when I attempt to run a calculation on the report I keep getting returned 0. Is there a way to bring this information to the Sales Line from another table so that my report can run calculations on it?

2

Dear Friend,

I think i understand your problem, I am taking one example to pick item’s weight from “Item Unit of Measure” Table on “Sales line”.

Please follow this process:

Create a field in Sales Line → Item Weight with Decimal type.

Go on Properties of Item Weight Field and change following properties:

1.FieldClass–>FlowField

2.CalcFormula–>Sum(“Item Unit of Measure”.Weight WHERE (Item No.=FIELD(No.),Code=FIELD(Unit of Measure)))

Now go in table “Item Unit of Measure” -->Keys. Select Primary key of table which is “Item No.,Code”

Now add a field in SumIndexFields column → Weight.

Now add your “Item Weight” field of “sales line” Table in your report. Report will show weight of Item with the help of Unit of measure.

so try this i hope it’s good…[Y]

Regards

Amaheshwari

3

To calculate the value of a flowfield, you use the CALCFIELDS command.

4

IF the only purpose of this flow field would be to calculate something in ONE report, I would suggest to not create a flow field for that, rather code this in the OnAfterGetRecord trigger of the sales line dataitem.

For implementing this you should include a record variable “ItemUoM” in the globals of your report with the “SubType” “Item Unit of Measure”.

Add the following code to the trigger:

IF ("Sales Line".Type = "Sales Line".Type::Item) AND ("Sales Line"."No." <> '') THEN
ItemUoM.GET("Sales Line"."No.", "Sales Line"."Unit of Measure Code")
ELSE
ItemUoM.INIT;

After this you can use any field in the Item Unit of Measure table to calculate on it:

TotalWeight := "Sales Line".Quantity * ItemUoM."Weight";

The reason for not using a flowfield is just because of performance. When too many flow fields are defined, the system will slow down on write transactions and grow very quickly.

The FlowField technology in NAV is a very powerfull tool but using it for each “look up” for values in a different table would be like driving with your Ferrari the 5 meters to the post box.

5

This is not correct. Basically firstly, a flowfield is only calculated as needed, so it the flowfield just sits there it has no effect what so ever on performance. Secondly this would not be done with a SUM flow field, but with a lookup. So there is no affect at all on the database.

6

Hi Ansuhl,

this is not correct. The UOM is a specific lookup, so you would not use SUM in thic case, instead you should use Lookup.

7

You are right when you are talking about lookup flowfields only.

I’m talking about sum flowfields which definitely do have a huge impact on the system performance for write transactions as you surely know (more on SQL than on C/Side, but anyway).

What I wanted to point out here is that a flowfield is a nice thing, but not the solution for everything and that one should use flowfields with care.

8

Hi,

On the face of it, I would tend to agree with Thomas in this case. I have seen flowfields added on the fly and makes upgrading / maintenance difficult. A simple lookup, to me, does not warrant modifying a standard table.

9

I think then that the missunderstanding in terminology.

I can assure you right now, that FlowFields have no effect what so ever on Navision performance. You can add 1,000 new flow fields, where ever you want, and NAV performance will not noticably change. The issue is SIFT fiels, it is these that affect NAV performance, even in 5.00SP1 and later. Each new SIFT created need additional processing and server power to insert modify or delete a record.

In the case discussed here, we looked at a flow field vs client based code. In generaly the FlowField of type LOOKUP (for obvious reasons) would be faster than the code based solution. BUT the difference would be so negligible, that it really would not matter which solution was chosen. So in my case the choice would be made based on upgrade and support.

So in general I somewhat disagree with your statement:

But if it were reworded to:

Then I would agree.

PS in this specific case, I would also do it exaclty the way you suggest (with UOM.GET). I agree that this situation is over kill for a FlowField. BUT for the reson of convienience, not performance. SIFT an Indexed View technology are great solutions, and when used correctly are generally the highest performance solutions to the problems they solve.

10

Hi David,

Very sound advice - If only everyone put as much thought into modifications

11

Hi Dave,

obviously I mean the SIFT technology.

So in fact one should be careful in creating new keys and additional SumIndexFields as these are exactly the ones which do have an impact on performance in write transactions.

Using already defined Keys/SumIndexFields by defining new flow fields do NOT have any negative impact on performance as these have a very quick access to the SIFT tables in read mode (not discussing the issue of poorly defined SIFT index on SQL server where the summing could be very slow).

12

Wow, great responses…I am using the LOOKUP flowfield and running calculations on the sales line without any coding. Multiple reports utilizing the Sales Line table have been modified as such and fully implemented without any noticable impact in performance.