A probably really dumb novice question about unrelated tables, relations and value lists...

[fmow]

I 'm setting up a database where one of the main tables (entities) is one called projects. This table involves a project number and related information including a client for the project and employees involved in each project. I 'd like to keep separate tables for our clientele and our employees. And of course use their data to populate the projects table. 

 

And I 've really confused myself as to how to relate these tables with keys. Each project can have one client, and more than one employees. Could I just keep them unrelated and populate the projects table via lists?

 

 

There's also another table for salaries where each employee will get a % involvement in each project and a salary for it. This is clearly a new entity, and I can relate it to the projects table via a foreign key of project number, and have fields for the name and surname of the employee, their % involvement and fee. Hmmm... ok I think I got this part, let me run it by you guys, I will relate the unique serial for each employee via a foreign key to the salaries table and keep the name and surname fields in the parent employees table and use these fields on the layout to populate... I think I lost it again...

 

Any help much appreciated. 

 

(as an aside I have to say I am really enjoying learning filemaker and using it, though the curve is steeper than I expected).

[KaosMaker]

To answer your first question:

 

Project-Client Relationship:

1) Every table should have a primary key to start with (here called Table::table_key for example).

2) Create a client_key (or whatever you want to call it) foreign key in in Projects.

3) Make a relationship between Clients::client_key primary key and Projects::client_key. This will ensure you never have more than one client per project.

 

Project-Employee Relationships (this is going to use a join table):

1) Create a new table (Tasks, for example), with the following fields:

     task_key (primary key)

     employee_key

     project_key

2) Make a relationship between Projects::project_key and Tasks::project_key.

3) Make a relationship between Employees::employee_key and Tasks::employee_key

4) Now you can have Employees with many Projects, and Projects with many Employees.

 

For your second question:

 

I'm not entirely sure I understood what you were saying. You will have one salary record for every project/employee pairing, I think? In that case, you want another join table, which includes the following fields:

 

salary_key (the primary key)

employee_key

project_key

percentage_involvement

fee

 

You will have one relationship from Projects::project_key to Salary::project_key, and another from Employees::employee_key to Salary::employee_key. You're right, you can pull in the name and surname via relationship.

 

Hope that helps, and welcome to Filemaker!

 

Edit: You might even be able to combine the Salary and Tasks tables I've described above, into one table. OR, you could relate Salary to Tasks, by primary key. Either way should be ok.

[fmow]

To answer your first question:

(...)

 

Oh it helps very, very much, many thanks and thanks for the welcome too!

 

Projects-Clients Relationship

Is there a way to populate the client foreign key in projects while not showing it to the user in the input window? That is I know I can create a list with Client ID keys (via the primary client id key in the client table) and show their name as a second field so the user can select based on that. But is there a way to populate the foreign client id keys without showing it to the end user. It really is a minor point this one. I am just wondering if it could be done. What's more tricky in my (novice) mind is the fact that some clients are companies and some are private individuals. So if I need to show a secondary field with this method wherein I can only show one secondary field, I can't have both "company name" and "name" and surname" there, so it will be one of the three, which creates a problem. 

 

Joint Table

Thanks! Should have thought of that myself, I am ashamed I didn't. And I'll then have each employee filling in the details, that is selecting their name and their assigned project. I hope I can figure out if via security settings I can allow each user to be able input their name only and assign it to a project. Possibly make a separate layout for each one of them. 

 

Second Question

Great idea about combining the two tables tasks and salaries I 'll do just that.

 

You 've been super helpful, thanks for taking the time to reply and look into my question. Much appreciated. 

[KaosMaker]

Projects-Clients Relationship

There is exactly that function, in Filemaker! When you create a value list, you can opt to only show the second field. What that does is, internally, the field *actually* stores the Client ID. But on the layout where you attach the value list to the visible field, what it *shows* is the related client name.

 

Joint Table

Instead of making a separate layout for each one of them, script the process. As in, make a button that basically says "Add me to this project", and in the script, use the Get(AccountName) function to determine who is logged in, find their Employee record, and grab their Employee ID.

 

You're welcome, and good luck!

[fmow]

Projects-Clients Relationship

There is exactly that function, in Filemaker! When you create a value list, you can opt to only show the second field. What that does is, internally, the field *actually* stores the Client ID. But on the layout where you attach the value list to the visible field, what it *shows* is the related client name.

 

Joint Table

Instead of making a separate layout for each one of them, script the process. As in, make a button that basically says "Add me to this project", and in the script, use the Get(AccountName) function to determine who is logged in, find their Employee record, and grab their Employee ID.

 

You're welcome, and good luck!

 

Project-Clients Relationship

Thanks for the heads up! Yeap I did manage to find it. Really useful, although I wonder why FM doesn't have an option to show a third field too. That would be hand in quite a few things too. But maybe it adds complexity that can be worked around anyway. 

 

Joint Table

Excellent idea!

[KaosMaker]

If you really want to show a third field, make a calculated field that concatenates the second and third field (like Full_Name, which would be a calculation of First_Name & " " & Last_Name), and use THAT as your second field, instead!