Newbie to 7 Simple? Questions

[Todd Fantz]

I am "experimenting" with the new features in v 7, esp in regards to not needing to generate multiple/complex keys to set up relationships, but I am kind of stuck, and I think it is because the environemnt is so "visual", whereas I could work the problem out as a series of join statements in SQL fairly quickly...

Anyway, I have an Employees table. I have a Projects table with multiple fields for "People" (yes this needs to be taken to another table). The employee name goes in these fields...say three per Project... back in a Layout showing the Employee info I want three portals... each one showing a list of which projects they are in slot #1, #2 and #3 in respectively.

Where my brain stops is that I have made three Employee table "copies" (what is the correct term?) in the relations window... and related the first ones name field to the first slot, and so on... but now I can't figure out how to show these things in portals on a layout. If I start with a layout of my Employee table, the other tables are not related, so I can't create a portal. If I start out with a Layout of Employee Table Copy #1 I can create a portal view the other tables (that never show any data?) but I am using Table Copy #1 so I can't make a portal for it?!

I can go back and make keys for everything "the old way" and it will work, but I am trying to figure out with my limited knowledge how much of this is my ignorance and how much of it is Filemaker 7?

Any help would be much appreciated. The help that comes with FM7 obviously stops with simple relationships...

I will try and attach a very ugly file.

Todd Fantz

IMCNR2.fp7.zip

[Ender]

The employee name goes in these fields...say three per Project... back in a Layout showing the Employee info I want three portals... each one showing a list of which projects they are in slot #1, #2 and #3 in respectively.

It's not clear what you mean here. Are you wanting to assign one project to three people, or does one employee have three projects? Either way, this might be a place for a join table. Maybe you can say more about this and post a screenshot of your relationship graph.

Where my brain stops is that I have made three Employee table "copies" (what is the correct term?)

These are called Table Occurances.

but now I can't figure out how to show these things in portals on a layout. If I start with a layout of my Employee table, the other tables are not related, so I can't create a portal. If I start out with a Layout of Employee Table Copy #1 I can create a portal view the other tables (that never show any data?) but I am using Table Copy #1 so I can't make a portal for it?!

You can only show related data where there is a relational path between the layout's Table Occurance and the related data's Table Occurance.

[matt d]

Can you explain in a little more detail?

[hayesk]

Anyway, I have an Employees table. I have a Projects table with multiple fields for "People" (yes this needs to be taken to another table). The employee name goes in these fields...say three per Project... back in a Layout showing the Employee info I want three portals... each one showing a list of which projects they are in slot #1, #2 and #3 in respectively.

Where my brain stops is that I have made three Employee table "copies" (what is the correct term?) in the relations window... and related the first ones name field to the first slot, and so on... but now I can't figure out how to show these things in portals on a layout. If I start with a layout of my Employee table, the other tables are not related, so I can't create a portal. If I start out with a Layout of Employee Table Copy #1 I can create a portal view the other tables (that never show any data?) but I am using Table Copy #1 so I can't make a portal for it?!

OK, first, Table copies are called occurrences. I am making a few assumptions here. You have a Project table with three fields (and other fields): Person1, Person2, Person3, and an Employee table.

Where you goofed is making Employee Table Occurrences. You should have made three new Project Table Occurrences (name them Projects1, Projects2, etc.). Once you do that, make three relations from the name in the Employee TO (table occurrence) to the Person1 field in the Project1, Person2 field in Project2, ...

Then you have three relations off of your employee table. Make a portal for each with the project1,2,3 and put the appropriate fields inside.

That should do what you want.

Now, keep reading - unless the three slots have specific purposes (i.e. you only ever have three people working on a project, and assigning an employee to slot1 has different meaning than assigning to slot2), you should have a different setup. It's fairly inefficient. What if you have 4 people? What if you usually only ever have one or two people. Here's how I would do it:

Use Employee ID for relations - two employees could have the same name.

Have three tables:

Employees

Projects

EmployeeProject <-- this is only a join table with only two fields Project ID and EmployeeID

Make a relation based on ProjectID (or name, but make sure your names are unique) from the Projects table to the EmployeeProject table.

Make a relation based on EmployeeID from the EmployeeProject table to the Employee table.

Now, you can support any number of employees per project, and vice versa. To add an employee to a project, add a record with the project ID and employee ID to the EmployeeProject table. To remove an employee from a project, remove that record from the EmployeeProject table. You can script this and use pop-up windows to automate it and make it a bit more friendly too.

Also, now all you need is one portal on the Employee layout to show all of the projects, and one portal on the Project layout to show all of the employees.

[Todd Fantz]

"Then you have three relations off of your employee table. Make a portal for each with the project1,2,3 and put the appropriate fields inside.

That should do what you want."

Thanks, that was it.

Yes, I understand the other point about putting the employees assigned to a project into another table. The issue for me right now is that the project has "slots" that need to be filled up and it makes it easy to have a field for each job title where I can make a value list based on that job title and allow the user to choose from a list of employees who can do that job. Not sure I want a list where they have to choose a job title before assigning a person... but I am sure there is a compromise.

Thansk for the help

tf