Many to Many (entering data)

[drumorgan]

Just when I think I am getting it, I try something new and feel like an amateur all over again.

I was doing fine with

One Customer to Many Homes

One Home to Many Jobs

Now I want to get

Many Employees to Many Jobs

so I have

Many Employees to One Bridge

Many Jobs to One Bridge (the same table)

Take a look at my relationship diagram to make sure I did it right.

But, now, How do I enter employees for a job? I have a table of jobs and added a field for name of employee, and tried various iterations of value lists based on existing employees, but I can't actually enter any value.

Please start with me from the basics. What layout would you use, and which table would you base it on for this type of data entry?

I really want to enter a new job as normal (I already have it reflect which home and customer the job is for) and I'd like a pull down menu to choose the employee(s) for that job.

At the very least I need the "key" employee (who is a manager) so I can pull up a report of the daily jobs for each manager.

But, I'd also like a record of ALL the employees that worked on each job, presumably designating one of them as the manager.

I'll take it a step at a time, but I wanted to describe my end goal so I don't have to double back and redo steps.

[bcooney]

Data model looks correct. I notice you use the term, "bridge," for a join table. Interesting, but possibly confusing to another developer. Seems like you could call this table "Crew."

I'd enter a new job from the Home table (since jobs require homes).

I'd have a portal on the Job Form to the join table. Assign a popup menu with a value list to the __fk_EmployeeID field. The value list should be based on the Employee table and consist of ID and FullName fields, showing only the second field.

Add a _kF_RoleID field to the join table. Select from a table of Roles (Manager, etc.).

[drumorgan]

Thanks. I updated the table name to use Join rather than Bridge.

I created a layout based on Jobs table to simplify. (my main data page is based on customers and includes the ability to add a home and a job, so I'll incorporate my solution on that layout eventually)

I added portal based on the "join" table.

I added fields into that portal based on the "employee" table.

I STILL am not able to even click inside the field to enter data. I made sure I had checked editable within browse mode.

[comment]

See also:

http://fmforums.com/...210#entry239210

Note the "Allow creation of records..." in the relationship settings.

[bcooney]

" added fields into that portal based on the "employee" table."

No, the portal on Jobs is to the join table.

[drumorgan]

The only fields in the join table are the keys to show the relationships and the "role" for that job.

The portal is definitely based on the join table.

I put fields in that portal (well within the lines) from any table, including join (only the three fields mentioned above) and the employee table, and NONE of them will let me even click in them, much less edit data.

[drumorgan]

I'm doing everything I can to match each section with the JoinDemo.fp7 file linked to. I can't see where I am different and am still unable to even click in any field in the portal.

[drumorgan]

I added a record directly to the Join table and it shows up in the right place and all fields appear (and are clickable/editable).

The sticking point is actually being able to click in a non-existing row of the portal and have it create a new record. Am I supposed to be using a script? I have a portal of jobs on the house layout and that just makes a new record whenever I click in the first empty row.

[comment]

As noted earlier - If you want to create new join records by entering data into the portal, you must enable "Allow creation of records..." on the join table's side in the relationship settings.

[drumorgan]

I downloaded your link, and was so excited to play with it, that I didn't see your next sentence telling me what I needed. Thanks for repeating this. Man, this has made for a long, frustrating day.

I really appreciate the help.