Trying to organize a company->company->employee database

[Hoytopher]

Apologies if this is in the wrong forum, new to FileMaker and unsure of how to articulate what I'm trying to achieve.

I am trying to reorganize a database using FileMaker. My client's work is literary, and wants to be able to sort agents/editors by their publishing house/imprint. I am using the Personnel Files starter solution, as it comes with some of the features I'm looking for, particularly the "Personnel List". A quick summary of relationships:

Publishing Houses [House] are parent companies. Sometimes they do their own publishing, but it's often left to children companies, or Imprints. There are House-specific publishers, editors, etc [People].

Imprints [Imprint] are composed the exact same way, with Imprint-specific People. They are owned by parent companies. For simplicity, lets assume that every House has 5 Imprints.

Editors/Publishers/etc [People] have their own specific pages. House/Imprint use duplicate layouts (companies), same with different categories of People.

I want to achieve the following:

The ability to use the Personnel list to sort either alphabetically by name (as is done currently). I also want to be able to sort by Publishing House -> Imprint -> [People]. What do I need to know to do this? I envision a list of all Houses. Then, clicking on one, the imprints show up in an adjacent list. Clicking on the Imprint, People working for that Imprint pop up, and then clicking on one of them leads to their Details page (currently set up). In order to view details on the House itself, the House's own page would be set up as an Imprint.

What are the gaps in my knowledge here? Where should I start? I am trying to create a system of increasing specification for finding data quickly.

 

[Hoytopher]

Hey all, I posted this in earlier in another forum but nobody responded.

In short, I am trying to create a tree of companies and employees. My problem is, I'm unsure of how to organize my fields and how to display my sorting. There's a main branch of companies, owning their own employees and also other companies. Using the Personnel List starter solution, I'm trying to make it so that the "List" layout can be used to sort through this all. Clicking on a parent company would display both its employees and their imprints/child companies. Clicking on one of those companies brings you to their employees/further children companies, etc.

I tried using a blanket "child company" field, but that doesn't seem like it would work because even though the children companies are obvious when viewed in the parent company's file, I don't think FileMaker can perform my specific task because they're all grouped together (unless I tie the entire thing to the record? New to FileMaker). I also thought of creating company-specific fields (e.g. Scholastic Imprint, FSG Imprint, etc) and throwing them into their own layout simply so that the data is recorded, but then it's not linked to other pages.

I'm unsure of how to complete what seems like a simple task. Would love any sort of response, links to information, advice, anything! I'm enjoying FileMaker, and being able to organize information like this would be tremendously helpful in the future.

[comment]

33 minutes ago, Hoytopher said:

I'm unsure of how to organize my fields

33 minutes ago, Hoytopher said:

I'm unsure of how to complete what seems like a simple task.

It's not exactly a simple task. And it's not about organizing your fields - it's about your tables and the relationships between them.

 

35 minutes ago, Hoytopher said:

There's a main branch of companies, owning their own employees and also other companies.

There is an important detail missing from this description: can a company be owned by more than one company (i.e. have multiple parents)?

 

38 minutes ago, Hoytopher said:

Clicking on a parent company would display both its employees and their imprints/child companies.

What does "imprints" mean in this context?

[Lee Smith]

Hi @Hoytopher

I merged your two posts.

Please do not multiple post your questions. If you have not received a reply to your question in a reasonable amount of time (usually 24 hours), just make a Reply to your original request by bumping it i.e. “Bump or, “Do I need to give more information” or something else that fits your question”, etc.

Lee

[Hoytopher]

On 2/16/2016 at 11:25 AM, comment said:

It's not exactly a simple task. And it's not about organizing your fields - it's about your tables and the relationships between them.

 

There is an important detail missing from this description: can a company be owned by more than one company (i.e. have multiple parents)?

What does "imprints" mean in this context?

For the purposes of this database, no. For example, Scholastic owns Scholastic Children's, Scholastic UK, etc. These children companies are called imprints.

I was thinking of creating a drop-down menu reading "IMPRINT OF: ____________", that would then be easier to sort in pulling all the results into a table.

Quote

 

[comment]

Basically, you have two choices here:

1. Define three tables: PublishingHouses, Imprints and Employees. Define a relationship between PublishingHouses and Imprints, matching on PublishingHouseID. Then use two occurrences of the Employees table to relate it to both companies tables.

2. Use a single Companies table for both types, with a self-join relationship to link an Imprint to its parent PublishingHouse. Then just link Employees to this table, matching on CompanyID. This would be the better choice, IMHO - unless you have radically different fields you need to track for each company type.

On second thought, the first option might be easier for a beginner to implement.

 

22 minutes ago, Hoytopher said:

I was thinking of creating a drop-down menu reading "IMPRINT OF: ____________",

I don't see how this would help with anything.

Edited February 17, 2016 by comment