5+ Self Joins or?
|Part #||Component Part #||Count|
I have a spreadsheet document like the above table. The Part # column contains many duplicate values. The component column has many values that are contained in the Part # column and also have their own components (this relationship can be nested 3+ deep). My question is, how can I display the data in a format like the following.
- B, 1
-- F, 1
- C, 1
-- X, 1
- D, 2
I have taken the initial table and self joined it on the part number and then created another self join on the component. This can give me somewhat of a desired result but to go further, I would have to create more self joins.
I was playing around with the following code but I don't know how to take the $component value and find the matching value in the Part # column... and then do it again for for any components of that component... and so on.
Set Variable [$cnt; Value:Count (Parts::Component)]
Set Variable [$i; Value: 1]
Set Variable [$component; Value: GetNthRecord(Parts::Component; $i)]
Exit Loop If [$i >= #cnt]
Set Variable [$i; Value: $i + 1]
I can sort what I require in outside languages but I am not sure how to do this in Filemaker and have it display cleanly and organized. Again, adding repeated self joins to get the desired result just doesn't feel right.