global to aggregate calculation

[darno]

In the following example, I can easily get aggregate calculation results for the table occurence "DataReference2004", that indicate me stats about the records that match gSecteur... but I can't when I add a criteria in the relation that is based on a calculation field (GraphPosition30Parameter1) returning a number for each record and a global field (gCounter) in Viewer which contains integer numbers from 0 to 30

where

GraphPosition30Parameter1 = Floor ( Abs ( (MinParameter1 - Parameter1))/(SpreadParameter1/30))

and is unstored, and where

MinParameter1 is a calculation field = Min(Parameter1)

Parameter1 is a calculation field = field1 - field2, where field1 and field2 are numbers

and where

SpreadParameter1 is a calculation field = MinParameter1-MaxParameter1

I try to get the results in a calculation field in the table Viewer that would get the count of the values for the sector and only for the ones that have the indicated position defined in gCounter, that is

= Count ( DataReference2004_Frequencies::Parameter1 )

Yet it works when I set it to

= Count ( DataReference2004::Parameter1 )

but then I get the results for all the sector, and not the count of the portion that goes in the sector AND appropriate position...

I checked, and there should be results that match both the criterias... so there should be a result

What's wrong?

[Søren Dyhr]

This must be here the flaw is:

and is unstored, and where

MinParameter1 is a calculation

...the third word "Unstored" and your graph confirms it, if you can't turn on indexing will the relation not work!

--sd

[darno]

Ah! so the three little triangular lines indicate indexing, right? and when there is a vertical little bar on both occurences, the relation does not work... still right? it's good to know... i've been wondering for a while!

But that doesn't help me though. I am still stuck!

[Søren Dyhr]

First do we need to establish as to why the secondary key won't index. My guess is that the calc' gets one or several values from a global field, or something a relation away, which pulls every thought of indexing down the drains.

I think that tinkering with more TO's in order to get alterations to the primary key instead, is worth persuing. This means the primary key is likely to be a calc'field instead, or something optained via a script.

I'm deliberately keeping my resonse a litle vague, because we were better of looking into a template or a clone of you system...

--sd

[darno]

First do we need to establish as to why the secondary key won't index. My guess is that the calc' gets one or several values from a global field, or something a relation away, which pulls every thought of indexing down the drains.

It is based on a summary field... of course it is not indexable, but I need to find a way to get the value!

I think that tinkering with more TO's in order to get alterations to the primary key instead, is worth persuing. This means the primary key is likely to be a calc'field instead, or something optained via a script.

Big deal... it's a frequency (count summary on certain conditions), I can't see how i can get it otherwise...

I'm deliberately keeping my resonse a litle vague, because we were better of looking into a template or a clone of you system...

The database is pretty large, and contains confidential data, so all I can do is give you this other picture that should allow you to understand what I am trying to do. It is actually pretty simple. Then all you can do, I guess, is give me a feedback of your understanding, and some clues if you have any.

Thanks

[Søren Dyhr]

Alright it must come as a pleasant surprice that the breaker value in GetSummary( can manage unstored calc'fields values as well. I have made a food for thoughts template using Edoshins Fastsummary algorithm as well as the bidirectivity in relations.

I have in order to make the breaker values be pretty live based them on values a relation away, which must translate to something like your upper and lower boundaries for the graph creation. The portal sorts on the unstored field as well....

Hopefully can you follow the abstraction, and see how you can make 30 lines in a portal each recieving the frequency. Grapping specific lines is pretty easy with GetNthRecord( if you wish. Each line in the second portal is after the scripting each of the first occurences for each breaker value.

--sd

goodgracious.zip

Edited September 8, 2006 by Guest

[darno]

ok, I think I see how it works... The same logics should apply to my solution... I give it a try and then I give you a feedback.

The weirdest thing is how you set up "the breaker" with your "paramters". At first, it got me confused!

[darno]

Ok, I managed it a simpler way. Instead of adding scripts and table occurences, I removed one TO. it was simpler than I thought.

I just put the gCounter field directly in the DataReference2004 table, and defined two more fields: lowerlimit and upperlimit, that update everytime I change the gcounter value. Then, I added another field, always in the same table, that calculates if the current record is included between the two limits, and returns 1 if so, and 0 if not. A last summary field makes the total of these, which is the frequency I need. I am not done yet, but I guess that if I just go to related record, showing only related records (for the sector) and then set the counter from 1 to 30, commiting records each time, I can build my graph! So I wouldn't need the DataReference2004_Frequency TO anymore!

When I am finished, I give some more feedbak and, hopefully, close this topic.