Setting up repeating calculation fields

[Brudderman]

(Starting new thread here)

I'm trying to convert an existing cross-tab type report to the use of repeating fields and have a small problem. Here is the situation:

The report summarizes the votes for eight political candidates by precinct in two different election years. I have it arranged so that candidate names are the column headings. These are global fields (gCandidate1, gCandidate2, etc.) which I can change easily to produce a different set of comparisons.

The votes themselves come through a set of 8 different TO's (Vote_Cand1, etc.) which are based on relationships between a the global candidate fields (gCandidate1, etc.) and the Candidate field in the Vote data table. Then the body of the report has a set of 8 more fields (CandidateVote1, etc.) which pull in the votes for each canditate through the respective TO.

This is complicated, I know, but the client wants it displayed this way the resemble the spreadsheet that he originally provided.

In a nutshell, this works OK, but there are 8 sets of everything to clutter up the works. So can I simplify by using repeating fields for the calculations?

Here is what I've done. I have a global repeating field (gCandidate) to replace the 8 globals. I have a repeating field (CandidateVote) for pulling in the candidate votes through a single TO (Vote_Candidate). My problem, I think, is how to set up the relationship for the TO.

Right now I have a relationship between the repeating global (gCandidate) and Candidate (a non-repeating field of candidate names). This of course returns the right numbers for the first candidate but nothing for the others as the relationship is seeing only gCAndidate[1].

I've tried adding a calculated field (CandidateExtended=Extend(Candidate)) and basing the relationship on that, but it doesn't work, either.

So can I set this up with a single TO, and if so, how?

I'm sure that I haven't explained this clearly despite the many words compounded by ignorance.

Thanks for any help.

[Vaughan]

Use FMP8's Save as Excel command to give the boss a real Excel spreadsheet of the report.

[Brudderman]

Thanks, Vaughan! The report actually works quite well in FM, and I have delivered about 1,000 pages so far. Having read in another thread that repeating calc fields can be used to more easily add crosstab functionality, however, I'm trying to learn how to use them with this as a test case. Hence the question.

This project began in Excel, but when the record count exceeded 120,000 I moved the raw data to Access and produced pivot tables in Excel.

But with 1,000 pages of reports with various levels of sorts and summaries, page breaks and formatting become quite onerous in Excel. But such is easy in FM. So I shifted it all to FM and tried to simulate the crosstab functionality there.

End of history. :

So I suppose that this is my first question:

Is it possible to replace multiple TO's (whose relationships to a given table differ only in the global field used to filter them) with a single TO using a repeating global field?

Edited November 23, 2005 by Guest