AnsweredAssumed Answered

How to identify child with given parent and grandchild?

Question asked by zack_fmform on Sep 15, 2018
Latest reply on Sep 21, 2018 by zack_fmform

Hello. I think I’m trying to calculate a child record when I have provided a parent and grandchild record. But I can’t quite figure it out.


I have a repair database with parts (that are used in repairs) and multiple vendors that could supply any part. When making repairs—and when I soon build a parts inventory angle to all this—I don’t care about the vendor for any part.


But when I get to the point of ordering parts, I want to choose a vendor. I’ll have saved data about a specific vendor-part combination that can be reused.


The important tables:


TESTING is line-item table I view in a portal for a specific repair item. It stores or references info for one specific test, its status, results, and more.


PARTS holds info about a part, its name, datasheet/specs, and more.


PARTS_JOIN_VENDOR is where I decided I need a join table for the intersection of PARTS and PART_VENDOR_DATA. This table links to a vendor’s page for a specific part, has the cost, and vendor part number.


PART_VENDOR_DATA is only for the basic, universal-to-parts vendor info, like name and contact details.


I’ve arranged them



I can reverence the PARTS primary key in a TESTING foreign key field. Then I can pick a vendor from a value list that pulls PARTS_VENDOR_DATA names and keys, and store that in TESTING. But I can’t figure out how to automatically choose the single PARTS_JOIN_VENDOR record that shares those two keys.


If the the TESTING table knows the parent, PARTS,  and grandchild, PART_VENDOR_DATA, how can it look up the child, PARTS_JOIN_VENDOR?


I think I need one or more occurrence table relationships to TESTING, and I tried a few combos but haven't succeeded. Could an occurrence table help? Is there a calculation to solve this?


Thanks for any thoughts. And please let me know if there are any terms I haven’t quite figured out; I tried searching for this in a bunch of ways, but there might be a Filemaker term/concept that would point me in the right direction.