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.