Relationship Based On Partial Multiple Record Match

[brokenmouse]

I've got a database of items (records) with various components. Many of these items share components. I'm trying to create a related table that will show the count of the items that share 3 of the same components. I can manually do this with a script, but what it has to do is loop through the various combinations and count the results. The script takes takes time and is annoying and of course does not update as additional records are added.

 

Example Data Table:

 

Part#    |    Component 1   |   Component 2   |   Component 3    |    Component 4   |   Component 5

 

PartNo1   |   A   |   B   |   C   |   D   |   E

PartNo2   |   B   |   C   |   D   |   E   |   F

PartNo3   |   C   |   D   |   E   |   F   |   G

PartNo4   |   D   |   E   |   F   |   G   |   H

PartNo5   |   E   |   F   |   G   |   H   |   I

PartNo6   |   A   |   B   |   E   |   F   |   G

 

etc.

 

I'd like the resulting seperate table to be something like

 

Components   |   Count

ABC   |   1

ABD   |   0

BCD   |   2

CDE   |   3.

ABE   |   2

EFG   |   4

 

Since I've got a dozen or so components and a thousand records the script takes a while to run even though the script is a simple nested loop.

 

I've toyed with assembling a value list for each record (instead of the components in seperate fields) but couldn't get anything to work right going that route.

 

Any Thoughts on how this can be done with either a calculation or related field setup? I've tried a bunch of stuff but I must be overlooking something simple. I'm working in FM10

 

[comment]

I must be overlooking something simple.

 

No, I am afraid this is not simple at all. And it's probably complicated further by your structure being all wrong - you have a many-to-many relationship between Parts and Components, and you should be using a join table to resolve it, instead of having 5 (? do all parts have exactly 5 components?) fields in the Parts table.

 

Eventually, you will need to have a field in the Parts table that lists all combinations of 3 out of 5(?) components of that part, and these combinations will have to be ordered (that is, "ABD", not "BDA"). And this field will have to be stored - so you'll need to run a script every time a part's components change.

 

Once you have that, you will be able to create a relationship between the new table that has a record for every possible combination of 3 components (again, ordered) and the Parts table and count the matches.

[brokenmouse]

No, I am afraid this is not simple at all. And it's probably complicated further by your structure being all wrong - you have a many-to-many relationship between Parts and Components, and you should be using a join table to resolve it, instead of having 5 (? do all parts have exactly 5 components?) fields in the Parts table.

Eventually, you will need to have a field in the Parts table that lists all combinations of 3 out of 5(?) components of that part, and these combinations will have to be ordered (that is, "ABD", not "BDA"). And this field will have to be stored - so you'll need to run a script every time a part's components change.

Once you have that, you will be able to create a relationship between the new table that has a record for every possible combination of 3 components (again, ordered) and the Parts table and count the matches.

Thank you for your response. I get that there are thousands of combinations of three components. I have a list in a seperate table. There are 5 different components for each item but the concept shouldn't change if there were 6 or 10. It just makes the file more cumbersome.

Can you clarify what you mean about the structure? Are you suggesting a joined table per field (5 other tables) or a joined table per component (about 2 dozen)? I don't see how that is more efficient unless I am misunderstanding you.

I am not sure if this is what you were leading to or not, but I had toyed with the idea of having a field with a generated value list of each part's components possible 3 combinations (there would be 10, ₅C₃) and then link it to my table with the thousands of combinations. I would use a summary field that would pass through and show the count for each various combination.

I had done something similar as I mentioned in the original post. Instead I had generated just a plain value list on the two linked tables. One contained a value list of each component of the combination (separated by carriage returns of course) and on my parts data table were all of the components in a value list. What I found it to be doing was matching only one of the components instead of all three.

[comment]

Can you clarify what you mean about the structure?

 

I mean that you should have three tables, arranged as:

 

Parts -< PartComponents >- Components

 

Each association of a part with a component would be an individual record in the PartComponents join table, comprising basically of two fields:

• PartID

• ComponentID

 

In your example, each record on the Parts table would have 5 related records in the PartComponents join table. This is the standard data structure for a many-to-many relationship in any relational database. See also:

http://fmforums.com/forum/topic/50942-portal-grouping-problem/#entry239210

 

 

Now, with regard to your current issue. There's a more primitive alternative, that might be easier to implement. Suppose you have a Combinations table, where each possible combination of 3 components is a record. Note that I say combination, not permutation - with 12 components, that would make 220 records. And suppose each of the component IDs would be in its own field in this table (the internal order of the 3 components does not matter in this case). This would enable you to create a group of three relationships (using additional TOs of the PartComponents table described above):

 

Combinations::ComponentID1 = PartComponents1::ComponentID

 

Combinations::ComponentID2 = PartComponents2::ComponentID

 

Combinations::ComponentID3 = PartComponents3::ComponentID

 

 

With all this in place, you can define a calculation field in the Combinations table =

ValueCount (
FilterValues ( FilterValues ( List ( PartComponents1::PartID ) ; List ( PartComponents2::PartID ) ) ; List ( PartComponents3::PartID ) )
)

This should return the count of parts that use all three components listed in the current combination. At least I think so (I am doing this in my head with a few distractions in the background...).

 

 

---

I am not recommending it, but you could implement this even with your current (flawed) structure, just by defining a calculation field in your Parts table =

List ( Component 1 ; Component 2 ; Component 3 ; Component 4 ; Component 5 )

and using this as the matchfield in the three relationships described above (again, untested).

[brokenmouse]

Thanks once more. As far as structure goes, I got what you're talking about. The purpose of this database is pretty narrow which is why I didn't set it up that way initially. I wasn't looking for two way comparisons like how your example file is set up and thought I could use a directly related table to accomplish it easily. Though, you're right in the end I may be better off going the proper route.

 

Your list calculation (for my barbaric file structure) was what exactly what I was contemplating in my earlier post on trying next when I get to work.

 

Last piece of advice, do you find that the additional join tables using an additional serialized id (like your file example) speed up the databases? I've got some pretty heavy databases that have a bunch of calculated field values based on multiple other related tables and other calculations. It's really only chuggy when I need to sort or find a set before exporting data. I had always assumed that it was natural part of Filemaker at that point (much like Filemaker is better at storing data vs analyzing it).

[comment]

do you find that the additional join tables using an additional serialized id (like your file example) speed up the databases?

 

I don't really know. The join table is required for basic functionality: without it, your database cannot answer even the simplest questions without jumping through hoops. For example, in your structure, finding parts that use a given component requires 5 search requests, and a report of parts by component is downright impossible.

 

As for using a meaningless value for a key, that too is a functional necessity: you want to be able to modify any (meaningful) attribute of an object without breaking the object's links in the process.

 

So these things are implemented with no regard to performance, because they are needed. If and when you encounter performance issue later down the road, you try to solve them, incl. by (some) denormalization. But speed without functionality is worthless.

[brokenmouse]

I appreciate your insight and time. For the most part the majority of my databases I've built typically match only a single field (like item number) in a central table to various sub tables such as a price list, inventory count, supplier inventory, availability schedules, etc. I've spent some time looking at your demo file and it makes sense yet at the same time it's going to take some time to get used to that way of thinking. The go between table seems like it offers more flexibility in the long term as with my specific situation here. It just seems wierd looking at your affiliation table (without the related fields) and seeing a bunch of "meaningless" keyID values. But I get their purpose after you point out the necessity of keeping them a neutral value. Thanks again.

[Rick Whitelaw]

To be obvious perhaps, but the whole idea of serial auto-entered and meaningless IDs is that nobody should ever have to see them.