calcsums and filtering table containing flowfield

hairyjim

Hi all,

Something I have never come across before.

I have a table of sales lead entries, the table contains a field that is a flowfield.

So when I run the code below I get the following error:

the sum of the values in the Estimated Value (LCY) field cannot be calculated because there is a filter on the following flow field.

Field: Product
Table: Sales Lead Entry

If I comment out the filter on the product flowfield the report runs just fine but obviously I am not getting back the results I need because I cannot filter on the right product. Any advice would be appreciated.

// Product group filters
// Filter for Volocity leads
SalesLeadEntry.SETRANGE("Sales Cycle Stage");
SalesLeadEntry.SETRANGE("Success Rating");
SalesLeadEntry.SETRANGE(Product, 1);
SalesLeadEntry.CALCSUMS(SalesLeadEntry."Estimated Value (LCY)");
SalesLeadEntry.CALCSUMS(SalesLeadEntry."Estimated Profit");
CategoryTotal[25] := SalesLeadEntry."Estimated Value (LCY)";
CategoryTotal[26] := SalesLeadEntry."Estimated Profit";
Number := SalesLeadEntry.COUNT;
CategoryTotal[27] := CategoryTotal[23] / Number;

kriki

You should select an index that includes also the field "Product" for using a CALCSUMS.
If you don't have any other filters than the ones in your post, I would suggest creating an index on "Product" with fields "Estimated Value (LCY)","Estimated Profit" as SIFTfields.
This way is the fastest to calculate the CALCSUMS.

hairyjim

Bah, forum did not send me a notification of a reply otherwise I would have replied sooner.

The unfortunate problem is that Product is a FLOW field and therefore cannot be part of the key.

I already have the key setup with the SumIndexFields as you suggest.

So is there a way for me to still filter using the FLOW field product and still do the calcsums?

Jim

kriki

No, it is not possible.
You will have to write a IF FINDSET-REPEAT-UNTIL.

Btw : it is strange the forum didn't send you an email. I never encountered the problem (or heard others complaining about it). Are you sure your mailbox was always available?

hairyjim

Hi.

Thanks for the reply. I have worked round the problem by re-writing the code for the rpeort so I do not need the product field now. When i come back to revisit the problem I will as you suggest do a FINDSET.

As for the forum not sure if it was because I was still logged into the forum when you replied therefore it presumed I would see the reply. Well anyway i got the notification of this reply.

Thanks for your advice.

Jim