Jump to content
Sign in to follow this  
Scott Pon

Universal (employee) list to use in any table

Recommended Posts

I have an employee table, where the employee has a status of "Active" or "Inactive".  I'm trying to create a Value List of Active Employees that I can use anywhere within the database, in multiple tables. 
 
I though I could create a table "TableA" with a Calc Field "Static Active" = "Active"  then create a relationship between TableA->Static Active with Employe Status.  Then create a value list based on TableA.  but that did not work.  I end up with a blank dropdown box.
 
In the same database, I have multiple tables that are going to need this value list.  I originally created multiple instances of the Employee table and linked to each table. but that's multiple relationships, rather than 1 universal relationship
 
But is there a better solution?  Let me know if you need more explanation
 
Thanks
Scott

Share this post


Link to post
Share on other sites

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

  • Like 1

Share this post


Link to post
Share on other sites

The simple solution here is to define a calculation field in the Employees table =

Case ( Status = "Active" ; EmployeeID )

and base your value list on this field.

  • Like 1

Share this post


Link to post
Share on other sites

I agree with all the  suggestions. It is possible that that the OP meant more table occurrences. Bad enough; but not the same as duplicated data (new tables).

Share this post


Link to post
Share on other sites

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

 

Yes: I'm not duplicating data. 

I thought that was what I was doing.  So I'll try it again.

Thanks for the suggestions.  I will let you know how it goes

Share this post


Link to post
Share on other sites

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

 

IT WORKS!

I don't know why that didn't work the first time I did it.  I must be over thinking it.  Thanks for your help!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.