TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Multi reference
Is there a way to do a reference between tables where they both get information from each other, for example I have tables
"A", "B" and "C" so where I use "A" with following columns, "Employee Name", "Employee ID", "Date of Hire", "Position" basically I use it to store all employees data, then "B" and "C" are the position departments. For instance, when I hire a new employee, I input their information then, when I want to assign him a position where I see all the ones available form either table B or C. Once i select the position by reference whatever is under position goes to my record on table A not sure if there is a way to send or autofill what is in A ("employee Id" "Employee Name" and "Date of Hire") to be to the same record I chose position. I want to automate this procedure. I'm attaching an image below.


Guillermo Vazquez
Date Created
1/2/2024 4:53:01 PM
Date Updated
1/3/2024 7:54:35 AM
calvin peters 1/3/2024 7:54:35 AM
I'm looking at this a bit blurry-eyed on a phone screen so perhaps
I've missed something in your diagram, however it looks
like your table 'B' and 'C' are collecting basically the same information
but for 2 different departments; IT Dept and HR Dept.
If that's the case then the relationship becomes only 2 tables
with a filter on the table 'B' which filters by Dept and stores the
associated values from 'A' which appears to be Employee specific data.

So i suppose a 1-to-Many relationship between Employees and Positions
Where 1 Employee can select from Many positions and the
Position List can display a detail table of Employee records below it
that use it sorted by whatever criteria required for your use.

Since HR and IT seem to share job titles a third table for available departments with a 1-to-Many relationship to be used to enable filtering on Positions table
...or a simple drop-down on Employee table that allows choice of available departments to use as a filter for Positions.

That ought to gove you an Employee record with Department, Position
and a Position record with a list of all Employees who ever held it in a
table with Date of Hire, Active/Inactive Status if you wish, Department, etc.sorted
based on your desired criteria.

A summary column of the employee records on the Position Table (filtered using your preferences) would/ should allow for automating ...I assume payroll or recruiting... feedback loop for FILLED/VACANT status of any position in that table...I think...

If an Active Employee record is assigned a DIR position for IT (filter based on ACTIVE STATUS =true and POSITION = DIR and DEPARTMENT;= IT; then the position is FILLED otherwise POSITION is VACANT by virtue of the fact that a summary column
can display the EMPLOYEE number when the filter criteria are met.

A periodic trigger should handle the update fine with a nightly update since this type of
update is less time sensitive in terms of realtime accuracy...
Set the trigger to run DAILY if EMPLOYEE NUMBER is not null and
The FILLED/VACANT boolean can be used to trigger whatever dependant action you wish from there.

... assuming I understand the problem correctly from the diagram...

Back to Search Results