FlowFilter On List Form

madm

On the purchase header, we have total order weight, which sums the line quantites (not sure if this a customization).

If i wish to place this on the purchase order header list, then i notice performance issues. As i have read (on here i think!) place flow fields on list views is not recommended, then I am wondering if there is anything which i can do?

kriki

It shouldn't give THAT much problems. Too many flowfields can give problems but only 1 should not give problems.
-Check if you have enough objectcache on the client (the standard value should be enough)

madm

without the flowfilter on the form, the form loads instantly. with the flowfilter, it can take upto on average a minute :eek:, whereby this seems to affect the performance system wide

i have tried it on several machines, one of which spent longer than the minute, and spent its time "searching the purchase order line".. Does this indicate a key/index problem?

As we use the get receipt lines, we do have "alot" of purchase lines as these are archived say once a year (the buying department seem to enjoy to have everything in one place :rolleyes : )

something i forgot to add in my original post, i have created a new purchase header list to filter on non blank collect from codes, and set the form to be use a key "document type, collect from"

ara3n

Try this. Click on file->database->information->tables->filter on Purchase line and select the all the records and click on optemize. See if this will improve your performance.

madm

ara3n wrote:

Try this. Click on file->database->information->tables->filter on Purchase line and select the all the records and click on optemize. See if this will improve your performance.

as a weekly routine, i optimize all the tables overnight

David_Cox

have tried it on several machines, one of which spent longer than the minute, and spent its time "searching the purchase order line".. Does this indicate a key/index problem?

something i forgot to add in my original post, i have created a new purchase header list to filter on non blank collect from codes, and set the form to be use a key "document type, collect from"

It could be any of these, if the form runs ok without any flowfields then the Key selected for the view and the filters are fine.
If it is searching then this is normally the flowfield problem, a simple test will tell you!
First thing is to test if it is your field or the system, simple just add a standard flowfield.

Remove your new field, and add Amount or Amount Inc. VAT to the form and run it, if it is slow then it is your system, if it is fine it is your field.
If it is your field, it would be a bad key choice, or a bad Calcformula!

Which key do you have the sumindex field on?
It should be on the Purchase Line ~ Primary Key ~ SunIndexFields with the others
Amount,Amount Including VAT

(add your Weight field here remove it from the other key)
Amount,Amount Including VAT,Weight

Then the CalcFormula on the Purchase Header field should be:
Sum("Purchase Line".Weight WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.)))

Let us know how you get on!

madm

David Cox wrote:

have tried it on several machines, one of which spent longer than the minute, and spent its time "searching the purchase order line".. Does this indicate a key/index problem?

something i forgot to add in my original post, i have created a new purchase header list to filter on non blank collect from codes, and set the form to be use a key "document type, collect from"

It could be any of these, if the form runs ok without any flowfields then the Key selected for the view and the filters are fine.
If it is searching then this is normally the flowfield problem, a simple test will tell you!
First thing is to test if it is your field or the system, simple just add a standard flowfield.

Remove your new field, and add Amount or Amount Inc. VAT to the form and run it, if it is slow then it is your system, if it is fine it is your field.
If it is your field, it would be a bad key choice, or a bad Calcformula!

Which key do you have the sumindex field on?
It should be on the Purchase Line ~ Primary Key ~ SunIndexFields with the others
Amount,Amount Including VAT

(add your Weight field here remove it from the other key)
Amount,Amount Including VAT,Weight

Then the CalcFormula on the Purchase Header field should be:
Sum("Purchase Line".Weight WHERE (Document Type=FIELD(Document Type),Document No.=FIELD(No.)))

Let us know how you get on!

i have just tried the amount field, and the form loads fine.. so it must be due to the field.

i have checked the calcformula and this is as above, with the addition of a constant filter = item (we are only interested in items making the total up).

i have also checked the SIFT, and found no mention of the weight field in any of the keys (the field was added added at set up by our NSC) :eek:

so, my next step i guess is to add the weight field to the primary key SIFT (as per you last post?!)

but as sainsburys call, i shall leave it till i come back

madm

i have just looked at the SIFT, and did actually find an entry for the Weight, this was not in the primary key.

I did move this over to the primary key to see what it did.. bearing in mind i was the only user and in a test environment i *didnt* notice that much of an improvement - but this could have been due to the environment (unlike when i added the amount field to the form, i did notice a difference).

What i then tried was removing the item const filter from the field, and this noticably decrease the form load time.. how would i go about correcting that?

David_Cox

madm wrote:

i have just looked at the SIFT, and did actually find an entry for the Weight, this was not in the primary key.

I did move this over to the primary key to see what it did.. bearing in mind i was the only user and in a test environment i *didnt* notice that much of an improvement - but this could have been due to the environment (unlike when i added the amount field to the form, i did notice a difference).

What i then tried was removing the item const filter from the field, and this noticably decrease the form load time.. how would i go about correcting that?

Question:
The only Purchase Line ~ Type that would have a Weight value is an Item, so why do you need to filter out the other types :?:

However if it is a problem, a new Key with:
Document Type,Document No.,Type SumindexField = Weight

This will do what you want.

madm

David Cox wrote:

The only Purchase Line ~ Type that would have a Weight value is an Item, so why do you need to filter out the other types :?:

However if it is a problem, a new Key with:
Document Type,Document No.,Type SumindexField = Weight

This will do what you want.

thanks for that

the reason for the type is (whether this is the correct method or not..) is that when we raise purchase orders for "misc" items.. stationery etc we we shown to do this by using the account type.. i have just checked, and this quantity is filled in on the weight (quantity base) field. I have added your key and it seems to work. However, as it was a Sunday morning i was the only user on, so tomorrow will tell on that one.

I do have a similar one, so if i can fire that one at you too, that would be appreciated :

On our item table, we have (NSC custom for us) another flowfield, but this time its a look up..

the lookup is set to the production bom line as a link on Prod Bom No., and version No. There is then a filter, again for Type and No. (The No. has different sequences, so the filter is to pick out certain sequences.)

Again, we have lots and lots and lots (!!) of boms resulting in even more lines. As time moves on, we are now getting randomly searching prod bom line through out the system; which i guess is due to everything being connected to the item.

The keys the prod bom line are :

Prod Bom No, Version, Line No.
Type, No

With it being a lookup, could it be just due to the volume of data it has to filter through, or will there be something, say coded into the table setup which could cause the "searching.."

Either way, hopefully the purchase line issue is now sorted, for which i thank you