Effect on performance when adding new FlowField in a table

Andwian

Dear Experts,

Is there any effect on performance when I add new FlowField in a table?

Thank you.

rhpnt

It depends...

kine

Yes, it depends on how the flowfield is defined and mainly on how it is used.

kitik

Flowfields are calculated when used, so it is when used that consume time.
Try to show lot of flowfields on a form for a large table and you'll see how slow it gets...

Salut!

kine

And because for SUMs you can define SumIndexFields, they could have effect when inserting/modifying the records too... it is not just about displaying ;-)

Andwian

Thank all of you for all your responses.

kine wrote:

Yes, it depends on how the flowfield is defined and mainly on how it is used.

Would you explain more detail, if you don't mind? Thank you in advance.

kitik wrote:

Flowfields are calculated when used, so it is when used that consume time.
Try to show lot of flowfields on a form for a large table and you'll see how slow it gets...

Could I conclude that: flowfield just affect performance when they are shown up in the form, for example?

Andwian

Actually, what I am going to do is to check whether the customer have any Balance at one Date.

So there are two ways in my head:

1. Create a new FlowField "Balance at Date" and make it invisible in the Customer Card form.

EXIST("Cust. Ledger Entry".Open WHERE (Customer No.=FIELD(No.),Due Date=FIELD(UPPERLIMIT(Date Filter))))

Then in my code, I just:

CALCFIELDS("Balance at Date");
IF "Balance at Date" > 0 THEN ERROR('Overdue Balance');

2. Do the SETFILTER on Cust. Ledger Entries, and then CustLedgEntry.ISEMPTY

CustLedgerEntries.RESET;
CustLedgerEntries.SETCURRENTKEY(Customer No.,Open,Positive,Due Date,Currency Code);
CustLedgerEntries.SETRANGE("Customer No.","Sell-to Customer No.");
CustLedgerEntries.SETFILTER("Due Date",'<%1',"Document Date"));
CustLedgerEntries.SETFILTER(Open,TRUE);
IF CustLedgerEntries.FINDFIRST THEN //or can be: IF NOT CustLedgerEntries.ISEMPTY THEN
   ERROR('Overdue Balance');

From those two approaches, please everyone kindly advice which one is a greater idea.
Kindly let me know your thought. Thank you.

kine

First case: You will need sumindexfield added for the appropriate index to have SIFT view for calculating the sum. This will need some maintenance during inserts/modify/delete for each record, but will give you the sum quickly when needed even when you will have many entries.

Second case: You will need good index to be able to quickly filter the correct entries (of course, you need that in first case too)

All depends on possibility to need to know exact amount or calculate the balance to some given date. If you need the amount or to calc it to some date, the flowfield is better.

vijay_g

kine wrote:

This will need some maintenance during inserts/modify/delete for each record

could you explain it more if you don't mind? what type maintenance require when i am inserting record in relevent table that is being used in flowfield.

Andwian

kine wrote:

First case: You will need sumindexfield added for the appropriate index to have SIFT view for calculating the sum.

What is it mean?

kine wrote:

Second case: You will need good index to be able to quickly filter the correct entries (of course, you need that in first case too)

What do we need from the first case?

kine wrote:

All depends on possibility to need to know exact amount or calculate the balance to some given date. If you need the amount or to calc it to some date, the flowfield is better.

Maybe I just want to know whether there is overdue balance exist for this customer.

kine

If you need only exists/not exists, than just set the filter and use ISEMPTY to check if there are some records inside the filter or not. If you have good index for SQL to apply the filters (index with good selectivity), the answer will be quick. No need to define flowfield for that. But again, all depend on other things like if you plan to test this condition on more places in the solution, if you want to disply check box with the value to user etc. Regarding other things- I recommend to learn more about the flowfields and indexes from the documentation.

Andwian

Thank you, Kamil, and Happy New Year!