portal with related fields

[katsii]

I am a baby at FM 5.0 and I created an employee database and related it to a project db with project # as the key field.

We built a portal on the employee database that has a drop-down field that gives the project number..we would like the right project name to pop up in the standard field right next to it. I got it to work by inserting the drop down project # field on the master file (after relating it to the project number field in the related db). Well, sorta work, because the name pops up ok, but the number repeats itself in the next row of the portal? I have no idea why this happens or how to fix it. HELP!!!:

[Chuck]

If I'm not mistaken, the relationship between an employee database and a projects database would be many to many. In other words, an employee can be assigned many projects and a project can have many employees on it.

To do this kind of relationship, you need a join file, a file that goes between employees and projects. The employee file has an employee number field. The projects file has a project number field. The join file has an employee number field AND a project number field, and is related to employees and projects with those respective fields.

Also, employees is related to this join file with employee number and projects is related to the join file with project number.

Then you place a portal in employees that looks at the join file through the employee number relationship. This join file has also has a calculed field based on it's relationship to the projects file so that you can get access to the projects information (such as the name) in the employees database. Then you can use a value list to select a project number and the project name will fill in.

I know it sounds complicated, but once you do it the first time, it'll make more sense. Let me know if all of this helps.

Chuck

[katsii]

Thank you! No, it makes great sense; I kinda suspected that might be the problem.

katsii

[dabombsmom]

To Chuck or katsii,

I could use your solution to relate an employee file to a lists file, but I'm confused about the join file. I have 241000 employees (don't ask) and a list file that will be growing daily (it has about 12 records now). Do I add the 12 to the 241000? That doesn't make sense.

Thanks

btgt

[Chuck]

What does the list file do? What are the twelve records that it currently has? Are they somehow connected to particular employees, or can employees be connected to any one of the twelve records in the list file?

Chuck

[dabombsmom]

The list file contains the names of the lists. Any employee can be on any list. We've made 12 lists so far, because we're just starting, but it will increase geometrically once we figure out how to do it easily.

We also need to be able to easily move employees on and off the lists, and to see which list(s) any employees are on.

I was going to do this with clones and lookup fields, but I have a feeling I'm making it too dificult.

Thanks for your interest

btgt

[Vaughan]

The list database will be related to the employees in a many-to-one -- easy peasy.

Base the relationship on employee number (you do have some sort of employee id? if not then just make a serial number id).

In the lists database, each record is a separate "list". Create a layout with a portal on it, use the portal rows to build up the membership. A pop-up list or menu would be good here to speed things up.

Doing it this way you can have a (virtually) unlimited number of lists each with an unlimited number of members.