Displaying Sums in a Tablebox

dorenthas

Greetings,

I need to create a form that would display, in a tablebox, a few hundred lines. The first column is an Employee No., and the other columns (8-9 other columns) are sums calculated from a Journal-like table where records have a Category option field. Kinda like creating a pivot-table, actually, in such a way that the user can easily navigate the data, filter it, drill-down...

The table providing the data will typically have a few thousand lines, but not more. It has the following (relevant) fields:
Employee No. (Code), Amount (Decimal), Category (Option)

Here's a more visual "explanation" of what I'm trying to achieve...
Column 1: Employee No.
Column 2: Sum of entries w/ cat. X or Y
Column 3: Sum of entries w/ cat Z or W
Column 4: Sum of entries w/ cat. V --
... (a couple more columns)

The sums fields should allow the user to drilldown into a form displaying the original lines for that Employee No. and the selected entries (I'm aware I may need to do this with programming.)

I thought of doing this using flowfields (with or without flowfilters), but DenSter pointed out that flowfields shouldn't be in tableboxes (next-to-last post), so that gave me pause. But the only alternative I see is to perform the sums through programming when the user loads the form, which, I suspect, is likely to take much more time than to calc/filter flowfields. (Then again, I'm not sure if 4-5K lines in the data table should be considered "a lot of data" or not in terms of possible performance impacts vs. either solution. It's hard for me to tell as I don't have much NAV experience yet...)

I'm curious as to what would be the best approach for this problem. Any suggestion or comments?

Thanks!

garak

From where you get the datas? Which Entry table?
How many Flow fields? Is the base only one Entry table or more?
You can also create your own statistic table for this and there your create / modify a entry after posting. So you have a compress statistic table for your Infotable

DenSter

Maybe saying "flowfields con't belong in tableboxes" is a bit of an exaggeration. Flowfields can be put into table boxes, and there's nothing wrong with that, as long as you are aware of the impact of putting flowfields on list forms. They are calculated as records are displayed on forms, and the more flowfields you have, the more calculations must be executed. If you have hundreds of lines on your form, with 10 flowfields, then the form has to calculate thousands of values. Having that many flowfields can easily kill your performance. What you want to do is keep a close eye on the form's performance, and let the users know that it might perform badly and that you might have to do it differently.

If I were you I'd suggest a statistics form to the users, just like Garak suggested. If they accept that you can put as many flowfields on it as you want, and it won't be such a big problem if it is a bit slow. BUT if you go down the road of a list form with many flowfield, it will be more difficult to change it later.

dorenthas

Thank you for the quick replies! I will keep your advice in mind as I move on.

Here's some more information, in case it makes you think of something else or see the problem in a different light.

The source table is a table I created where current Payroll entries are stored so the payroll manager can review data for the next Pay Period before submitting it to the external company that is managing Payroll for us. Data in that table comes from weekly timesheets exports, and adjustment lines can be added by the Payroll manager (e.g. bonuses, retros, pay/vacation adjustment, etc.) Once payroll data has been submitted to the external company, the current payroll entries are posted to a historical payroll entries table, tagged with the pay period, and the current payroll entries table is emptied. As a note, we have less than 500 employees at the moment and each timesheet typically ends up producing 1-2 lines, so I expect a typical pay period to have about 2.5-3K entries.

As for the form with the tablebox I intended to create, I don't know if it falls under the statistics form label. The objective is to allow our payroll manager to have an overview of the current payroll entries without seeing the details, so she can see one line per employee, with a column for total hours worked, total salary paid, total vacation paid, a column for Adjustment A , a column for total Adjustment B, and so on.

Thanks again for sharing your time and wisdom!

dorenthas

I just had one more question, and I'm afraid it's a newbie question... Is there a way to leverage the SIFT advantages without using flowfields? (I don't know if what I'm saying makes sense, because the inner workings of SIFT and flowfields are still somewhat of a mystery to me.)

For instance, let's say my current payroll entries table has a secondary key on the Employee/Category pair (where Category is one of Regular Hours, Vacation, Adjustment 1, ...) and I set the two SumIndexFields, Hours and Amount Paid. Then I already have my sums maintained when records are inserted, updated or deleted, and exactly the values I want to display in my grid. Right? Or are flowfields the only way to access Sum Index Fields?

kapamarou

Flowfields on list forms can be a drag for your system. But if your Business scenario requires them, then you must do something about it.

Whenever I've faced such an issue I've followed one of two solutions:

Either a new "Statistics" form as mentioned that shows your data, or if it is a Generally used form then I add code to force the fields to become invisible (So that they are not calculated) and if a user "really" needs these fields, he can simply show them on the form and use them, being aware of the consequences.

In general I think the problem is huge when it comes to forms being used constantly from many users. A form that is used by some specific user , let's say a few times a month, is not such a big drag.

But all of this can only be tested in a "real environment".

kapamarou

Flowfield values where stored in secondary tables in versions prior to 5. In new versions they are Views (In SQL). If maintaining these flowfields delays your system during insertions of records, you could disable them and get the values through code, by looping through your records and performing the calculations that the flowfield formula performs...

dorenthas

kapamarou wrote:

In general I think the problem is huge when it comes to forms being used constantly from many users. A form that is used by some specific user , let's say a few times a month, is not such a big drag.

Thankfully, only one or two people will use it, a few times a month.

kapamarou wrote:

Flowfield values where stored in secondary tables in versions prior to 5. In new versions they are Views (In SQL). If maintaining these flowfields delays your system during insertions of records, you could disable them and get the values through code, by looping through your records and performing the calculations that the flowfield formula performs...

So in essence, flowfields are useful to pre-amortize (by maintaining indexes) most of the calculation so that, when accessing the aggregated data to display it, there isn't much calculation required. Is my interpretation correct?

kapamarou

Yes.

(I hope here we are discussing on a SQL basis)

But a lot of factors affect the system behavior.

An example is the following. In one project, a client uses Serial Number tracking in some few items. At any given time, there are not many entries in the reservations table. So, and since the table is "Hot", I have disabled the Maintain Sift property. SQL server is still able to calculate the sums by querying the table itself, and not it's sum index table.

dorenthas

kapamarou wrote:

Yes.

(I hope here we are discussing on a SQL basis)

But a lot of factors affect the system behavior.

An example is the following. In one project, a client uses Serial Number tracking in some few items. At any given time, there are not many entries in the reservations table. So, and since the table is "Hot", I have disabled the Maintain Sift property. SQL server is still able to calculate the sums by querying the table itself, and not it's sum index table.

Yes, we are running SQL Server 2005 and running NAV 5 SP1. Sorry, I forgot to mention these... er... small details

So if I got this right, setting the Maintain Sift to false disables the SQL index on the table so that data manipulation is faster at the cost of slower performance when performing flowfield calculation (GROUP BY queries?). Then it's all a matter of judging if overall performance gets more improvement from having that index or not.

In my case, a table where there is fairly little data manipulation (and most of it is done by scripts) but a fairly high volume of data, using flowfields with Maintain Sift set to true sounds like the better option. But then again, I'll need to test that under "realistic" conditions.