Create new child records based on a parent table report

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
Hi,

I have 4 tables, Employee, Assignments, Organization and Site. The Organization table is the parent of the Site Table. 

The employee and organization tables are a many to many relationship where Assignments is the intermediary table.

If a user would like to assign an employee to multiple sites from its parent org, what would be an easy programatic way for them to do so, without having to make an assignment record for each site under each org.
Photo of Giuseppe macri

Giuseppe macri

  • 544 Points 500 badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

I would think that to make these assignments more dynamic the relationships would be like this::

Site >> has many >> Assignments

Employee >> has many >> Assignments

Organization >> has many >> Assignments

This will allow more dynamic selections of Organizations to Sites, Sites to Organizations, Employees to both Sites and Organizations all via the many-to-many Assignments table.

Your thoughts?



Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,784 Points 20k badge 2x thumb
I think this ER Diagram (created with creately.com) represents your existing schema:


Are you trying to modify this schema or do you want some type of automation to fill in child records using the existing arrangement?
Photo of Giuseppe macri

Giuseppe macri

  • 544 Points 500 badge 2x thumb
Yes, so if Employee A was assigned to Organization Z (who has Site 1, Site 2, and Site 3 Child records), I want to programmatically add additonally assignment records for all the sites under Organization Z.

So If A->Z
      then A->1
              A->2
              A->3 records should be generated.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
Are you saying that you would create a new many to many relationship between employees and sites called employee site assignments, and then want to automatically have those Assignment records created when in employee is assigned to an organization, for example an assignment organization Z.

Is that what you’re looking for?
Photo of Giuseppe macri

Giuseppe macri

  • 544 Points 500 badge 2x thumb
Yeah that. I was initially thinking it was possible via QB Actions but i cant figure out how to pass the Site IDs to the assignments when this action would be triggered.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,908 Points 50k badge 2x thumb
I do have a way to do this but it would probably take me an hour and a half or two hours of consulting time to set this up for you. But you would need to push a button on the employee record in order to trigger the process to create the employee site assignment records.

Contact me via the information on my website if you want to pursue this off-line. QuickBaseCoach.com

I do know that Quickbase has some new and wonderful things coming down the pipeline might let you do this with Automations,

It’s possible that if I combined my techniques with their techniques that this could be made to happen automatically. With no button push.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,784 Points 20k badge 2x thumb
I would change your schema to the following and possibly set the record picker fields for the Sites table to be the [Name] of the parent Organization and the [Name] of the Site.



This will allow you to join an Employee with a Site and independently manage Sites as children of Organizations. This new schema also implicitly joins Employees to Organizations.