Possible to copy and paste values in sub-summary field

[Philip Jenks]

Hello

I have a layout which has a sub-summary field, displaying sales values when the data is sorted by Month. My script involves the copying and pasting of the sub-summary values into another file, but I cannot get it to work. The instruction 'Copy [sub-summary value]' fails.

I have a nasty feeling I don't understand how sub-summaries work and that their values simply aren't copyable. Can anyone enlighten me and, even better, suggest a workaround?

Thanks

Philip

[comment]

Use the GetSummary() function to obtain the sub-summary value of the current group. For example,

GetSummary ( sTotalValue ; Month )

will return the sub-total of December 2011, if the current record is in that month.

My script involves the copying and pasting of the sub-summary values into another file

Why is that necessary?

[Philip Jenks]

Hi Consultant - thanks very much for the quick and helpful reply. I'm being summoned to carve some ham, but will try this as soon as the Boxing Day party is over. Can't wait! Thanks

Philip

[Philip Jenks]

Hi again,

I know less about this than I thought. In order to use that GetSummary function, do I create a new calculation field defined with that GetSummary function? And then place that field in er ... Body, or Sub-Summary part? Or do I incorporate the GetSummary function within a script? Ultimately, I ant to copy the value from File 1 into another file (rather than do a lookup) because the figure involved is a projection and I want to be able to override it in File 2.

[Philip Jenks]

Hi Consultant. Solved it! Many thanks.

Philip

[khwp]

Hello Phillip

How did you solve your problem? I am trying to use the getsummary function (which works fine in a TO) but when I try to copy the value using a calculation I just get a blank. I a stumped! Could you shed some light please?

Thanks

Kevin

[comment]

Not sure what you man by "I try to copy the value using a calculation". You should (almost) never use copy/paste in your scripts. Instead set a variable to the desired value and use Set Field[] to deliver it to its destination.

[khwp]

Hi consultant

Thanks for taking the time to reply.

I'm only a beginner with FM so I'm uncertain about my terminology - I guess "copy" is the incorrect term. I am not using a script. What I mean is that I have been unable to use a value that the function get summary() has returned as a calculation value in one TO in another TO via a relationship.

To expand - lets say that I have used getsummary() as a calculation filed in table1 - and that works as expected by the way. I then want to use that field in table2 - that's where I hit my blank - the field in table2 is just blank. Very strange!

Many thanks

Kevin

[comment]

Summary fields summarize the found set and as a rule they do not mix well with relationships (that ignore the found set). Perhaps you should start at an earlier point and explain what are you trying to achieve.

[khwp]

Hello Consultant

Consider a table called items. It has three fields - date (text - yyyymm), tax (number), amount (number).

date

tax

amount

201110

1

10

201110

2

20

201110

0

50000

201111

10

100

201111

20

200

201111

0

60000

201112

100

1000

201112

200

2000

201112

0

70000

My aim is to obtain summary by date of tax and amount for all those rows whose tax is not zero. eg

date tax amount

201110 3 30

201111 30 300

201112 300 3000

I then want to use the above results (relationally) in other tables.

So, the method/approach I used was:

a) use a find (tax >0) to obtain a found-set containing a set of records whose tax is >0.

then add additional (work) columns to my table to enable me to use the getsummary() function e.g. sigmatax=getsummary(tax;date). (Note that I have not shown all of the ‘working’ columns for brevity - for instance, the field ‘tax’ cannot be used in the getsummary() function as it isn’t a summary field so I just used intermediary columns (not shown) to achieve the result below.)

date

tax

amount

sigmatax

sigmaamount

201110

1

10

3

30

201110

2

20

3

30

201111

10

100

30

300

201111

20

200

30

300

201112

100

1000

300

3000

201112

200

2000

300

3000

This worked perfectly except I cannot use the column’s/field’s called sigmatax and sigmaamount relationally in another table - I don’t get any errors - just blanks.

So, I guess my question (eventually!) is how do I achieve my goal to “ have a summary by date of tax and amount for all those rows whose tax is not zero, such that I can use the results relationally in another table”

Incidentally, I suspect an answer might be to use a self-join on the date field but I really could do with some help!!

Many thanks

Kevin

*******! the formatting went west!!

I hope you can make sense of the above post?

Kevin

[comment]

It is still not clear to me what you mean by "use the results relationally in another table". What exactly will the relationship be based upon? If it's based on matching date, then the entire group of records will be related, and can be easily summed on the "other" side using the Sum() function.

[khwp]

hi consultant,

Yes the relationship is based upon the matching date. The function sum() sums the whole table. But I don't want to include those rows that have a tax value of zero.

[comment]

Try one the following:

1. Define a calculation field in the Items table =

Case ( Tax ≠ 0 ; Amount )

then sum this field on the other side of the relationship.





2. Define a calculation field in the Items table =

Case ( Tax ≠ 0 ; Date )

then use this field as the matchfield instead of Date.

3. Define the relationship as:

AnotherTable::Date = Items::Date

AND

AnotherTable::cZero ≠ Items::Tax

where cZero is an unstored calculation field = 0.

[khwp]

ahhh Enlightenment!

Sorry for being so dense. Thank you for the efforts in deciphering my confused words.

Thank you again.

Kevin

[gczychi]

Hi,

I'm very interested in this topic but I didn't understand the proposed solution. How is it possible to reference (or copy?) the values of the sub-summary report? With the proposed relations from «comment»? Do you need to copy/reference each single value? Or can you get all together? How?

I'm actually having the same question (copying all sub-summary values of a given report into fields of another table) and would like to understand the solution.

Thanks for any insight,

Gary

[comment]

As you can see, each problem is a bit different. Why don't you explain yours in more detail - esp. why it's necessary to duplicate existing data.

[gczychi]

OK, thanks. I also wonder whether duplicating the data will be justified and if there is a more elegant solution.

My sub-summary report is based on totals of values of maybe 10'000 records per year and can grow to contain 5-10 years of data, maybe more. For each year the user can choose different time periods (12 months and 4 quarters). Depending on the time period the user chooses (not more than a year, though), the found set is adjusted accordingly.

I thought it would be reasonable to copy the complete set of sub-summary values (maybe 50 values) of any time period the user displays into a special table «Report», where these values are aggregated and furthermore processed, until they finally arrive in a special Report layout. (N.B.: Once a year is completed, no changes to the report will occur)

Browsing through such a table will be instant. I fear that it will take too long to fetch all the data on the fly to display the Reports -- if this is at all possible (which I don't know).

So this is why I would like to copy (or maybe reference?) the sub-summary fields. GetSummary () is only a little bit of help, because it would mean to traverse through the entire fund set to collect all the sub-summary values. Any suggestions?

Thanks a lot,

Gary

[Philip Jenks]

Hi Kevin

Only just seen your post. It seems we're all indebted to Consultant for helping us out.

Philip

[comment]

Any suggestions?

Two:

1. You could export the records grouped by your breakField, then import the result back into your table of summaries.

2. You could use Mikhail Edoshin's "Fast Summaries" technique to collect the sub-summary values. In a nutshell, it works by jumping directly to the first record of the next group, using GetSummary ( sCount ; YourBreakfield ) to calculate the number of records to jump.

OTOH, 100,000 records is not so many, so perhaps you should simulate this in your own environment and see how bad it really is.

[gczychi]

1. You could export the records grouped by your breakField, then import the result back into your table of summaries.

No, not really (I would rather loop through all of the records).

2. You could use Mikhail Edoshin's "Fast Summaries" technique to collect the sub-summary values. In a nutshell, it works by jumping directly to the first record of the next group, using GetSummary ( sCount ; YourBreakfield ) to calculate the number of records to jump.

Wow! This is way cool! And it turns out that I already have a count field that I can use. This technique is awesome. Many thanks to Mikhail Edoshin, to Kevin Frank (who explained it very well) and of course, to you.

Gary