Users and Company relationship

[Devin]

I've been trying to create a quick demo file for a proof of concept.

I've got 2 Tables..

Users
pkid (serial Number)
UserID (serial Number)
Name

Company
pkid (serial Number)
CompanyID (serial Number)
Name

My end goal is having a portal in both Users and Company layout that will show all the users assigned to a company or all company assigned to a User and to allow to add/delete from the portal.

I know I'm missing a key part in how to relate them to one another?  What am I missing?

 

 

[comment]

17 minutes ago, Devin said:

all the users assigned to a company or all company assigned to a User

Can one user be assigned to more than one company? If yes, you have a many-to-many relationship between the two tables - and you will need a third join table to resolve it. See the demo here:
http://fmforums.com/topic/50942-portal-grouping-problem/#comment-239210

 

--
P.S. You should not need to have two serial-number fields in the same table. In a Users table, an auto-entered UserID is the primary key of that table - likewise a CompanyID field in a Companies table.

[Devin]

7 minutes ago, comment said:

Can one user be assigned to more than one company? If yes, you have a many-to-many relationship between the two tables - and you will need a third join table to resolve it

Yes a user can be in more then one company. Totally forgot about a 3rd table. PERFECT!!

And yes I did realize that after making the test file I did not need to have 2 serial numbers field for each table. Just in the habit in making the pkid first.

 

[Devin]

I want to take this one more step now.

I've added a new Table "Orders"

Each order is assigned a single Company and for every Company will have 1 or more Users assigned.

Is there away to limit (not show) the orders for Users that don't belong to the Company based on login?

My thinking that I would use the Get ( UserName ).

[comment]

27 minutes ago, Devin said:

Is there away to limit (not show) the orders for Users that don't belong to the Company

Do you mean as a security issue - or for convenience only?

[Devin]

20 minutes ago, comment said:

Do you mean as a security issue - or for convenience only?

More for convenience then security. But I would not want to see a list of <no access>.