Cross-referencing Parent and Child Company Records

We have a Clients table that has the most basic info (company name, ID and status) and then what we refer to as Inventories, where there can be multiple Inventories under a client. That can include different products defined in the record, or in this case, different affiliates with the same product but separately licensed with different configurations.  Those are differentiated in Inventory with a name under SubSystem (in the form for that product group, we've applied an alternate label of Named Affiliate). So, the Clients table defines the Company Record ID, and each affiliate (including the parent) has it's own Record ID in the Inventory table.

If a company is not part of an affiliate group, GroupName and SubSystem are left blank.  Currently, the only way to group affiliates and navigate between them is filter a report on GroupName to see all and go back and forth from there. And the team is manually maintaining a list of entity names in a field, having to update that any time an affiliate is added, removed, spun off as an independent entity, etc.  Only serves as a reference.  In the old system, there was a field similar to SubSystem where they could tag the different affiliates, but again, only for reference. With QuickBase, hoping to streamline and automate to make it easier to manage these complex clients, which can have a few affiliates, or I think in some cases 20 or more.