Calculate Current Found Set in Count field?

[rmalott]

Hi, oh-so-helpful-ones,

I have a "report" which has a lot of calculated fields (doing counts from other fields), but I would like to calculate the counts based on the current found set. The relationship between the current found set and the ending "count" field is actually a few tables deep. In case it makes a difference, I have respondent table (with my filtered values), which is related to a table with all respondents and their responses, for each question/answer. That table is related to another table which I have all of the counts on.

 

Thanks!

Robin

[David Head]

You just will not get a reasonable answer to that 'question'! There is no detail to work from. I suspect that the answer involves the use of a summary field but I have no idea where you want to draw that summary count from!

[rmalott]

Ooops. Ok let me try again

 

I guess my question is, can I make a Count calculated field count only the numbers within the current found set from a different table? I looked into summary fields but it appears that you can only use those on the current table. The filtering is occuring on a different table than where I need the counts (there's a one-to-many relationship between the two)

 

For example, I have a respondent table with various attributes about the respondant, stuff like institution size. If the user queries for large institutions, she'll return, say 40 records. Then I would like the respondent question/answer count table (which groups questions with the number who answered 'Frequently' together) to show the number of answers only for the 40 respondents, not the full 70 respondents. Hopefully that makes more sense.

Thanks!

[rmalott]

OK well in case anyone's interested, here is how I ended up doing it. I don't know that this is the best method, and it is pretty slow on 1600 records, but hey, it works

 

First, in the child table where the resopnses are I added a display indicator Y/N

Then I added a summary-like calculation that used GetNthRecord but would only increment if the display ind = Y

 

(question_answer is the unique question/answer combo that I wanted the count for)

 

Case(GetNthRecord(question_answer; Get(RecordNumber)) GetNthRecord(question_answer; Get(RecordNumber) - 1) and display_ind = "Y" ;1;

GetNthRecord(question_answer; Get(RecordNumber)) = GetNthRecord(question_answer; Get(RecordNumber) - 1) and display_ind = "Y" ; GetNthRecord(question_subsummary; Get(RecordNumber) - 1) + 1;

GetNthRecord(question_answer; Get(RecordNumber)) GetNthRecord(question_answer; Get(RecordNumber) - 1) and display_ind = "N" ;0;

GetNthRecord(question_answer; Get(RecordNumber)) = GetNthRecord(question_answer; Get(RecordNumber) - 1) and display_ind = "N" ; GetNthRecord(question_subsummary; Get(RecordNumber) - 1)

 

 

 

 

Then, when the user presses the button to view the count report for only the current found set, I reset all the rows to display ind N, and then loop through the found set and set those child rows to the display ind Y. This isn't the part that's slow, it's the durn calculation.

 

I don't know about you, but it seems to me that FMP is fantastic for making some more difficult things easy, but you've got your work cut out if it's not something directly built in.

 

Robin

[LingoJango]

I thought you *could* use a summary field from a related table in a sub-summary report. What you can't do, of course, is see the results in browse mode.

 

But I'd better find out soon if I'm wrong... ;-)