Sharing field info between two tables

[Kayos]

I have a User database and a Software database. The User database has a specific ID for each user and a field for Software assets assigned to that user. The Software database has a specific ID for the software and a field for the user it is a ssigned to. In the Software database I want to be able to assign Adobe Photoshop to Jim Smith and have that record automatically populate the Software Assets field in the Jim Smiths record with the Software ID#.

Can someone help get me started? I could probably figure it out if someone can point me in the right direction...

Thanks!

[transpower]

Create a portal in the User table layout to hold the software assets of that user. The relationship in the Relationship Graph should show a line between the primary key (userID_pk) in the User table and the foreign key (userID_fk) in the Software table. If you add a record in the Software table with that user's ID, then that record will show up in the portal in the User layout. A field for Software assets in User is not needed--use a portal.

[Kayos]

Thanks transpower, that worked out for a single piece of software but I failed to mention in my earlier post that I would be assigning multiple pieces of software to one user. How do I allow for multiple pieces in a list format?

Also, could I make this backward compatible as well? If I enter the Software ID in the portal in the Users record, could it fill in the assigned field on the software record?

[Ender]

The relationship described above will work for assigning different pieces of software to each user. However, you may find it useful to be able to assign the same license to more than one user in addition to being able to assign multiple licenses to each user. This is because often software is purchased with volume licenses that use the same license for multiple machines (also some software may not have an actual license code, so it's best to avoid using that as the key field.)

This functionality can be achieved with a join table between Software and User. See the Attachment.

Either way, related records can be created via the relationship by setting the "Allow creation of records via this relationship" option in the Edit Relationship dialog.

Another thing to consider: If you also track computer equipment, it may make more sense to link the software to the computer rather than the user. In my system, I link software to the computer, the computer to the room it's installed in, then the employee to the room. This way when HR hires a new employee, they just assign them to a room, and the links to the equipment and software are already there (this works for us because most employees have a computer that is stationed in a specific office, though if everyone had portables, then it would make more sense to link the equipment to the employee.)