I have a "client" file and an "employee" file. For each client there are many employees assigned. I have an "assignments" file as my middle file. There is a key serial number for the clients and the employees. I work in the "client" file mostly and I add or remove employee assignments in a portal (which views the "assignments" file). Occasionally I run a list for an employee that has all of their clients, obviously from the "employee" file.

How can I add an employee assignement in the portal by selecting their name, not typing a serial number?

I want to maintain using the serial numbers as the key (not the name) because names change and occasionally someone takes over another employee's client load.

Any help would be appreciated.

I think I am understanding you correctly and I had a similar conundrum. But thanks to someone else on here (maybe Jason Wood? can't remember now) this is how I solved it. You can create a new field in the assignment file that is employee name (make it a value list that populates from the name field in your employee file). Then create a second relationship from the assignment file to the employee file using the name as the link. Then set the employee key field of the assignment file to be a lookup based on the newly-created name relationship. Now when you enter the name in the portal, the key field gets auto-entered and then use the key relationship for all the other portal fields. Now if the name changes the link is still there on the key!

Good luck.

Courtney

Thanks, Courtney. That makes a lot of sense, I hadn't considered the drop down being populated by the name field in the employee file, but that would ensure that the person exists and is active.

Cheers,

J

Ideally, it'd be good to keep these selected names up to date if the master record changes. The way to do this is to tightly control the interface where the names are entered: if a name is changed, re-lookup the "select name" field in the other file to update everything.

The method you describe works but will fail to do so when two John Smiths will be present in the Employees file ... also changing the person's name will wreck existing relationships

I'd use a value list showing the EmployeeID AND his/her name: the value list will be populated by the values present in the relevant fields, the name of the employee will be clearly spelled out but only his/her ID will be entered, ensuring uniqueness

If you still have some place on your layout, just display another portal next to the one you are actually using. This portal will be related to Customers through a One to One relationship, will display John Smith, San Diego, Street + a button next to it will export from one portal to another.

As Pupiweb says, Names are difficult to work with as they can be duplicates. If you really do not want to show the ID, this is the solution you'll need.