10 Replies Latest reply on May 14, 2012 10:51 AM by wbanks

# Calculations and Summary Reports.

### Title

Calculations and Summary Reports.

### Post

I'm using FM11 and (more or less) a newbie.

I've searched the forum post, but don't quite understand which direction I should be going here.

Table A contains Name, Field001, Field002 Field003.

I would like a report in which I receive something similar to the table that follows.

Thanks.

 Name Field01 Field02 Field03 Charles Apples - 14 Oranges - 12 Toyota - 14 34 Jane Oranges - 16 Peaches - 20 Mazda - 10 22 Bob Ford - 70 GM -3 58

• ###### 1. Re: Calculations and Summary Reports.

What does the data look like in your table?

From the example above, do you have two records with "Charles" in the name field (One where Field01 = Apples-14 and another where Field01 = Oranges-12) OR do you have 26 records and the numbers after the dash represent the number of records with each value?

This looks like an abstract example. It's usually better to use and example of the actual data you are working with as otherwise there is a chance that we will suggest a solution that works for the abstract example but which fails for the actual situation.

• ###### 2. Re: Calculations and Summary Reports.

I'm sorry to be so abstract, but I assumed if I understood the concept, then I could handle the heavy lifting.

I have a table of Names that I'm using as a lookup table.

I want to loop/cycle through these names and count the results for each field.  In the example above, Charles selected 'apple' 14 times and 'oranges' 12 times from Field01.  Bob made no selections in Field01.

I originally thought that I could create a new relational table that would do the following:

(1) Add the unique Name to the table

(2) perform a search for the field variations

(3) count the results

(4) insert that information into a specific field in the new relational table.

(5) repeat

I'm sure there are better ways of handling this, but I'm performing a search because I may need all instances of Charles that are both 'apples' and 'toyotas'... resulting in two records.

• ###### 3. Re: Calculations and Summary Reports.

In the example above, Charles selected 'apple' 14 times and 'oranges' 12 times from Field01.  Bob made no selections in Field01.

Please clarify what you meant by 'selected'. Does that mean you have 14 records with Charles in the name field and apple in field 1 with 12 more records with Charles in the name field but with Oranges in Field1

And how do these choices related to fields 2 and 3?

Keep in mind that if we are talking about individual records for each "selection", then you may have one record where Charles selected:

Apple | Toyota

and another where he selecte:

Orange | Toyota

There are ways to use summary fields to get counts for each category, but I'm still evaluating whether or not they will work for your data.

• ###### 4. Re: Calculations and Summary Reports.

The choices in Field01 are chosen using a radio button.  The choices in Field02 are chosen using checkboxes.

There are 14 unique records in which Charles chose apples and another 12 in which he chose oranges.

There are somewhere between zero and three records in which Bob chose both Ford and GM in Field02.

If this were a pivot table, I'd only need the sum/count of values one level beyond the name field.  It would not need the number the count of both 'apples and toyota' and 'orange and toyota'.

Thanks

• ###### 5. Re: Calculations and Summary Reports.

That leaves one question unanswered: Is there any relationship between the columns where the data we need to count is selected?

Does the choice in field 1 have any bearing on what choice is made in field 2?

What I'm getting at is this:

In your example, there's only once choice in Field 2 shown for Charles and Jane while they have selected 2 choices in field 1. Bob shows the opposite, listing no choices in Field 1 and two different choices in Field 2. How should your report look if a person made 2 or more choices in Field 1 and also 2 or more choices in field 2?

It's very likely that I'm about to suggest you restructure your data so that each column of choices is defined as a separated related record in order to get the needed totals to appear in columns. In that case, portals can be used to arrange the results into the columns like you have here.

• ###### 6. Re: Calculations and Summary Reports.

There is no relationship between the columns other than they are of the same record.

In your example, the results would appear as follows.

 Name Field01 Field02 Field03 Dave Peaches - 10 Apples -5 Lexus - 1 Chevy - 3
• ###### 7. Re: Calculations and Summary Reports.

Hmmm, the challenge is that you have two completely independent totals separate columns.

I would presume then that your report could also be:

 Name Field01 Field02 Field03 Dave Peaches - 10 Apples -5 Chevy - 3 Lexus - 1

In other words the fact that Peaches and Lexus appear in the same row has no significance from what I can see.

If these are radio button selections in each column, is it safe to assume that the number of possible choices for each field is fixed or might the maximum number of possible choices vary?

• ###### 8. Re: Calculations and Summary Reports.

Exactly, there no significance between the rows Field01 and Field02 per named individual.  I can see the confusion as it might it appear that way in the table.

In instances where there are radio buttons, the choices are fixed.  In the instances of checkboxes, the list may expand.

• ###### 9. Re: Calculations and Summary Reports.

And with checkboxes, you may be storing multiple values in the same field separated by returns and this will not lend itself to any kind of tabulation of the individual choices. Single value to a field is critical if you are to count this data. There are ways to use single value checkboxes with a related table listing the choices in the checkbox group that can provide a check box group for the user, but store the data individual records.

I'm mulling over several approaches right now.

Option 1: Restructure your data into  a table where one record consists of three fields:

NameID | FieldName | Value

This table would link by NameID to a serial number field where the person's name and other info specific to that one person are stored.

This table combines data from all your columns into a single field with multiple records, but uses Fieldname to identify the source. Thus your Apple and orange type responses might have "fruit" in the field name field and your field 2 responses might have "cars" in the fieldName field....

Filtered Portals can then be used to arrange for a grid of columns and rows for each person with summary fields defined in this table counting the responses.

Option 2:

Create an interim table that combines the list of possible values with the ID of the person making those choices to serve as a Filter such that a portal to this table links by Person's ID and response category (Apple, orange, etc.) in order to compute and display the needed count for each selected value. Records in this interim table would be created during the data entry process that collects the individuals selections.

I haven't worked out all the details to either one of these, but perhaps that's enough to suggest a possible approach for you.

Option 3:

This may be seen as heresy, but your reference to a Pivot table suggests that you might have a good working knowledge of Excel. As with all applications, there are some tasks that a given application does well and others that it does not do well. If you know how to do this in excel, you can export your data from Filemaker into excel and create the report there.

• ###### 10. Re: Calculations and Summary Reports.

Thanks for your assistance.  Through this conversation, I figured out how to resolve the problem.  This is an abbreviated version, but you'll get the point.

Go To Record [First]

Loop

Set Var [\$Name; Name]

New Record

Set [Table_Name; \$Name]

Go To Record [First]

Loop

Set Var [\$Field01; Field01]

Enter Find Mode

Set Field [Name; \$Name]

Set Field [Field01; \$Field01]

Perform Find

Set [Table_Field01; Table_Field01 & Value:Get(FoundCount)]

Go To Record [Next; Exit After Last]

End Loop

Go To Record [Next; Exit After Last]

End Loop