Updating an unrelated global field prior to a calculation?

[ClayH]

Hi! I have the following situation...

Table A has x number of records - names of people.

Table B is a join table between Table A and Table C. The relationships have an "priority" ranking associated with them. That ranking is stored in Table B.

Table C is a list of goals that the people in Table A can perform. Table C has has a portal to relate to entities in Table A by displaying the values in Table B, in a typical use of a join table. For each entity in Table C there is a number, n, of entries in the portal, where 0 <= n <= x.

A typical entry in Table C might be "Lower Healthcare Costs" and the portal entries might be something like:

Name - Priority

Joe - 4

Jane - 3

Dave - 4

I need to perform a calculation for each entry in Table C that is:

Sum(related "ability" rankings from related Table B records) / Total number of records in Table A.

Using the above as an example, assuming there are 12 people in Table A, the calculation I need to perform would be:

(4+3+4)/12

The issue here is that I cannot use the relationship to calculate the total number of records in Table A (x) , because for each entry in Table C, the Count of records in Table A is only equal to the number of records that define the relationship (n).

So, I did the following:

- Added a Summary Field to Table A that is a count of the primary keys. This equals the total number of records in Table A.

- Added a Global Field to Table A that is equal to the value from the Summary Field.

- Added an unrelated instance of Table A to the graph.

- Used the value from the Global Field in the unrelated instance as the total number of records in Table A (x).

This worked great and gave me the expected result... With each record added to Table A, the total number of records was stored in the Global Field.

The problem came when I deleted a record from Table A. The Summary Field updated to show the proper number (x). The Global Field does not update when the value in the Summary Field decrements -- only when it increments. Hence, my Global Field is correct until records are deleted, and then the Global Field = x - number of records deleted.

I hope this all makes sense.

My question, then, is how in the world do I get a reliable number for the total number of records in Table A (x) so that I can use it in the calculation for Table C? I can't go through the relationship (because I only get the number of related records) and the Global Field doesn't update when Table A loses records. I cannot use the Summary Field from the unrelated instance of Table A on the graph because it is not a Global Field.

FYI, I have a programming background, but I'm new to Filemaker, so I'm just lost in the interface and/or scripting while I try to figure this out.

Any help would be appreciated!

[bcooney]

You could look into creating a calc in Table C =

count ( cartesianRelationship::TableAprimaryKey).

Build a cartesian relationship from Table C to Table A using the "x" relationship which allows Table C to "see" all records in Table A.

By the way, summary fields are found set dependent, so not the way to go.

[ClayH]

Wonderful! One of those Filemaker quirks that I had yet to encounter. Thank you for your help. :