Portal With GetSummary Sorting Problem

[Poruchan]

Hi,

I was here a while back learning how to build a horse racing form. Thanks to those that helped, I've been producing a form for several months now. However, I never did figure out how to make the track averages and have FM calculate my speed figures. I'd like to give it another shot, as it will save me a tremendous amount of time. Right now I am doing all the dirty work in excel and importing the track averages and speed figures into FM -- so I'm using FM as a desktop layout program, which I suppose sounds crazy.

I can get FM to produce averages in a summary report, but I need separate summaries to get my speed ratings (I think).

I'll try and attach a photo of a mock-up layout that might be nice. A few things: There are ten tracks, and over 8oo possible race types i.e Tok_A1_12D (Tokyo, Allowance 1, 1200, Dirt). And I only indicate race types for races on dry tracks, so there is that attribute. Also all my records  are imported into a single table - so 150,000 race records so far. Anyway, I thought things like 'do I really need to make 800 separate summary calculations?' But, if that's the way it is, then fine.

And as before, I still do think I have some pretty basic conceptual misunderstandings about FM -- I appreciate all the help I can get -- even the slightest pushes in the right direction.

R Ave = Class Average (for race type)

W Ave = Win Average  (for race type)

 

Thanks,

Paul

 

Edited September 29, 2015 by Poruchan
incomplete

[comment]

even the slightest pushes in the right direction.

I am afraid that's the most I can do here, since I did not understand most of your description.

In general, you would use a sub-summary part to show summary values for each sorted group. For example, if you have records showing the individual times of runners at various events, you could sort them by event and display them this way:

Event     Average Time
A         10.56
B         11.02
C         10.71
Overall:  10.68

A single summary field is all that is required to produce such report. You place it a sub-summary part (when sorted by event) to show the averages per event, and in a grand summary part to show the overall average. In this example, the layout has no body part. Nothing is stored in this method.

Note that Filemaker is not good at producing cross-tab reports.

 

 

Edited September 29, 2015 by comment

[dwdata]

Hi Poruchan,

I agree with Comment. Cross Tab reports require a few backflips to get the results you need in FileMaker. Generally, it requires some strategic setup and scripting. I tend to like to output to HTML so that I can compile my report on the fly.

Here is a little sample attached of what I mean. Feel free to tear it a part. Post any questions, for clarification.

Good luck!

dwdc_CrossTabAvgs.fmp12

[Poruchan]

Don, Comment - Okay, and thanks -- besides  the issue of a cross tab report, which I guess is an issue of comparing data with a particular perspective, can you help me with this thing about getting a summary from a found set and then "using that summary in other calculations". That's the real problem I can't get my head around. 

As I said, I have been able to make a report of all the summaries, but I can't use the results. In fact, I don't need to even see the summaries. For every race I need to get to a number that begins with subtracting the class average from the race average. And there are I few other calculations that use class average, but the first one is so important. 

This is why I think I have a very basic concept problem since what I want appears to be simple, but it has driven me crazy.

Sorry Comment, I wish I could speak the language. I had always wanted to show the original data in excel and the end product, but I thought that might be too much. Can I upload a partial image of the data I start with, and the end result. Would that help? Maybe you will spot a glaring error in my thinking process.

Thanks,

Paul 

[comment]

can you help me with this thing about getting a summary from a found set and then "using that summary in other calculations".

For the summary of the entire found set, just use the summary field in the calculation formula. To reference sub-summary values, use the GetSummary() function.

Edited September 29, 2015 by comment

[Poruchan]

Comment, I'm reading about GetSummary now -- that's it -- thanks!

Paul

[Poruchan]

Comment,

I'm actually getting the numbers, and I foresee a lot of time being saved thanks to you, but I am getting into a kind of sort battle -- going back and forth a bit until I hit the right combination. Is this normal? I worry that I will have to assume I've got it sorted right.

Paul

[comment]

 I am getting into a kind of sort battle -- going back and forth a bit until I hit the right combination. Is this normal?

I don't know how to answer that.

[Poruchan]

Hi,

I have not explained myself well, so I am going to give it another go with pictures. I've creating a nifty racing form -- actually, you have. Anything other than the basics was supplied by members of this forum. I have a completed product, but the speed rating is tediously worked out in Excel.  Here is a picture of the result.

The columns with the red 82 in Italian Neo and the red 83 in Silk Brooklyn are the speed figures. I get those in Excel through some calculations that involve race averages and class averages. I have reproduced the numbers in FM in a summary report. But first, it might help to show you how I receive the data in Excel:

What I actually receive is from columns Date to Time (I left a few things out as they were not essential for this problem). On the right you can see 'Class Speed' which is the magic number I need in the form. Here is the layout:

The portal is everything - the past performances which are of course sorted by date. As I said, I reproduced the speed rating via the summary report using GetSummary in the calculation, but I can't use the speeding rating in the portal.

This is the last piece of the puzzle -- getting the speed rating in the portal -- do you have any suggestions? (there are about 150,000 records in the table 'Performance' which the portal is showing records from)

Thanks,

Paul

Edited October 6, 2015 by Poruchan

[Poruchan]

Don,

That Avg Race Report is really something and has given me some new ideas. I've been going over it and trying to figure out how to put it to good use. I probably will have some questions.

Thanks,

Paul

[comment]

I am afraid that's a bit too much for me to take in. Let me try and provide a general answer:

Summary fields provide sub-summary values only when records are sorted by the breakfield. This applies also to calculation fields using the GetSummary() function. If you need to use these values while records are not so sorted, you will need to write them somewhere: variables, global fields, or records in another table.

I recommend you get familiar with a technique known as Fast Summaries by Mikhail Edoshin.

 

[Poruchan]

Thanks, I downloaded the sample file 'fast summary' from FileMaker Hacks (Kevin Frank). Very interesting and seemingly simple (though I don't understand the script). I want to use it somewhere else. To me it seems like a cool way of doing a report, but the end is  the same. I don't see how I would take the class average from it, use it in a calculation to get the speed rating and then drop it in the portal I'm using to show past performances. Perhaps it is possible, but I just can't see it.

Since it is a given that I cannot use the sub summary value if it is not sorted by the breakfield, then I suppose I have to find another way.

And since I'm mathematically challenged, I must assume I have convoluted this discussion. I want to continue this, but I'm afraid I would ask the same question. So I have to ask a different question ... I'd like to ask how would you do it? And if you say 'Do what?' Perhaps we could start there.

 

Edited October 7, 2015 by Poruchan

[Wim Decorte]

There is an approach that I have not seen mentioned in this thread before: instead of trying to sort and summarize the records themselves, you could loop through them (not even physically - see the GetNthRecord() function, or use ExecuteSQL() and loop through the result in a variable) and aggregate your results in variables (check out repeating variables, 'named buckets',...)

and then dump all of those results in a scratch table or a 'virtual list' table for display.

so lots of concepts here that you can google and leverage....

[Poruchan]

These race averages do not change all that much once you get a dozen or so races averaged. There are 10 racecourse and 2 or 3 are holding races at a time. They rotate the races seasonally. I think I'm just going to create fields for each race type average and update them monthly. An inelegant solution, but I'll be able to move onto other projects that I desperately need to get to.

Thanks for all the help -- I will be using the ideas I got here for some of those other projects.

 

Edited October 7, 2015 by Poruchan