Note that I'm using five records as an example. The real numbers are significantly higher.

This is the last major calculation problem I'm having on my database and I could sure use some help.

Table #1 ("Software") has five records (A, B, C, D, E).

Table #2 ("Builds") has five records (V, W, X, Y, Z). Via a portal to Table #3 (see next), each of these five records stores a 'group" containing a set of records from Table #1.

Table #3 contains multiple records that "link" Tables #1 and #2. For example, one series of records may contain "V" and "A", "V" and "C", and "V" and "D". Another series may contain "W" and "C", "W" and "D", and "W" and "E".

Table #4 ("Hardware") has five records (f, g, h, i, j). Via a portal to Table #5 (see next) each of these five records is linked to a single record in Table #2.

Table #5 contains multiple records that "link" Tables #4 and #1. For example, "f" is linked to "X", "g" is linked to "X", "h" is linked to "Y", "i" is linked to "V", and "j" is linked to "Z".

Still with me? :

The "Builds" table records assemblies of software as listed in the "Software" table. So a "Build" record may have one item in it, or as many as 60 (or potentially more), linked via Table #3.

The "Hardware" table contains records of each computer and each computer has a "Build" on it. These are linked via Table #5.

I can already count how many times a certain "Build" is installed on the computers. What I REALLY need is a means of counting how many times a certain "Software" is installed on the *computers*.

Using my example above, I know that Software A is installed on one Build ("V"), Software B is installed on zero Builds, Software C is installed on two Builds ("V" and "W"), Software D is installed on two Builds ("V" and "W"), and Software E is installed on one build ("W"). That part I've got.

I also know that Hardware f and Hardware g has Build X, Hardware h has Build Y, hardware i has Build V, and Hardware j has Build Z. Again, that part I've got.

With that information, what I need to calculate is how many times each Software appears on each Hardware. Manually, using my example I know that Software A is installed once (Hardware i), Software B is installed zero times, Software C is installed once (Hardware i), Software D is installed once (Hardware i), and Software E is installed zero times.

How do I do that in FileMaker?

Still with me?

Not really. An ERD would be useful here. So would consistent and easily understandable table names. Calling the same table "Builds" in one sentence and "Table #2" in the next is not helping.

what I need to calculate is how many times each Software appears on each Hardware

If I understand correctly(?), these two tables are connected in a many-to-many through a join table (Table #5? Installations?). If so, the other two tables are irrelevant to this question.

The easy answer is: produce a report from the join table, sub-summarized by Hardware, then by Software.

To produce something similar "live" in each Hardware record is difficult. You can easily see which Software is installed, by placing a portal to Software on a layout of Hardware. But it's not possible to count the instances from the point-of-view of Software, because you want to do this individually for each record in Hardware.

One possible solution is to use the so-called "Ugo method" to de-dupe the portal to the join table, using a self join.

http://fmforums.com/forum/showtopic.php?tid/190559/

See also:

http://fmforums.com/forum/showtopic.php?tid/192677/

[color:red]NOTE: This seems to be a follow up to your previous post:

http://www.fmforums.com/forum/showtopic.php?tid/198463/

Not really. An ERD would be useful here. So would consistent and easily understandable table names. Calling the same table "Builds" in one sentence and "Table #2" in the next is not helping.

Geez, forgive me for trying hard to explain a confusing situation and apparently failing badly. I was unaware that it was a sin for someone who ISN'T a FileMaker expert to TRY and explain things.

NOTE: This seems to be a follow up to your previous post:

Yeah, it would seem to be. Too bad it's not.

You would be significantly more helpful if you weren't so focused on what I've done WRONG. Thanks anyway.

Edited October 3, 200817 yr by Guest

I think that you are misinterpreting Michael's post here.

It is a common mistake that new posters use abstract information when posting. However, when using such things as Table 1 and record B, it is very hard to follow. It makes more sense when you just call it what it is.

Also keep in mind that although you know your system, we have no idea what your table, graph, process, etc structures are. So we ask that posters give as much detail as they can without using abstract terms.