Need to get data from one table into another with no connectivity

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • In Progress
I have a vacation App.  I have 3 tables: Employees, Managers, Vacation Requests.  Everyone in the company is listed in the employee table.  All of the managers are listed in the manager's table.  Each manager has several employees, but never themselves as you can't be your own manager.  Right now I am using the brand new Text summary field to combine all of a manager's employees into one text field.  I then pass that field back to the employees.  This is basically a list of their co-workers who all share the same manager.

Then when the employee creates a new Vacation request, I create a snapshot of that text field in the new vacation request.  This allows all of the co-workers to see each other's vacation schedule.  If their name is contained in that list, they can see it without having to be able to see that employee's employee record as it may contain private information.

Now I have been asked to allow employees to see the vacation schedules of their manager.  This is proving to be way more difficult than I thought it would.  What I need to do is get a list of a manager's employees in his employee record, so that when he creates a new vacation request he can pass a snapshot of his employees (as well as his co-workers) to that request so everyone on the list can see it.  But that list is in the manager table and since you can't be your own manager, the two records aren't linked.

The two records to have the same user and the same name, but I can't find a way to use that similarity to merge the two.  The Merge field can't be a user or a formula field.  If it were just a one time update, I could do it manually, but as people get hired, promoted, or change managers, the lists will need to be updated.
Photo of David

David

  • 294 Points 250 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
1. "But that list is in the manager table and since you can't be your own manager, the two records aren't linked."
Is that a necessity? I understand that in real life a manager can't manage themselves, is there a workflow-related reason why they can't be in your app?

2. "The Merge field can't be a user or a formula field."
Mirror the 'manager' field with a 'group' field, which you can keep on another table if you like. Basically build out another Manager table but with Managers and Employees in the same pool, and assign them to groups which can be named arbitrarily or after the managers themselves. Basically the same as #1 but with more work, but the same functionality.
Photo of David Brogdon

David Brogdon

  • 1,222 Points 1k badge 2x thumb
I may be misunderstanding your issue, but can you not just create a text summary of the manager's vacation requests and then pull that down to the employee records via a lookup field?