Add many records based on another table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am trying to create a database of many contacts. We service many medical facilities which each have many contacts. Most of these facilities are part of a corporate company. On a corporate level there are also many contacts. I would like to be able to choose any facility and see its contacts and its corporate contacts together.  
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
You have a couple of choices.

One is to make a extra report link field and to use the field Record ID of the corporate company as the field to configure on the report link field.

Then you would have two embedded reports, one for the Corporate contacts and one for the facility contacts. 

The alternative is to configure the report link field to cover both sets of contacts all on one embedded report.

Post back once you know which way you want to go, and if you are stuck on configuring the report link field.  Note that you do not need to have a direct relationship to have a Report Link field.
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb
Thank you for prompt answer.
I actually did the first idea already. Regarding the second idea how can I create  a report link to both in one link.
But i have a bigger issue that I want to filter on one facility in a report and see everyone related to that facility even corporate.
I was thinking along the lines of having another table that links contacts and facilities and will map all of the corporate contacts to the facilities. My issue was to automatically create those records. One major reason for this is that  a corporate level contact can possibly  be related to only some related facilities and not to others.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
It sounds like you may have a Many to Many requirement for those Corporate Contacts.

One Corporate Contact has many Corporate Contact Facility Assignments
One Facility has Many Corporate Contact Facility Assignments.

So you would need to set up a middle table called Corporate contact Facility Assignments.
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb
Exactly, Im just looking for a way to by default connect corporate contacts to all related facilities in this Corporate contact Facility Assignments.
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb
also how can i make a report link to include all contacts (if some have related corporate group and some have related facility?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Well, that list of contacts is going to be a report off a single table.  If you wanted there is be a single table to report on, then then you would need a table of Contacts, and then a Many to Many relationship to Facilities and also a separate Many to Many relationship to the corporations. (that that correct that you have many Corporations and each has many Facilities?

It would mean that when you just wanted to add a contact to a facility, you would need to create the contact in the consolidated Contacts table and then also make an Assignment of that Contact to the Contact Facility Assignments table.  That that would take extra data entry for the regular Facility contacts which i assume are only associated with one facility.  But I suppose that you may find that there are exceptions where the same contact is the contact for multiple facilities in which case this setup is the most flexible to only maintain the source of truth for that contact details in one record.
 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
There may be a simpler way if the corporate contacts are to show on each facility contact list.  I thought that different corporate contacts appear on different facilities.  But I think that this solution still has two report link fields.

Have a table of Contacts.
1 Corporation has Many Contacts.
1 Corporation has Many Facilities
1 Facility has Many Contacts

On the Facility record you would show a report link field where the where Related Corporation matches the Related Corporation of the contact.  Then you would also have a Report link field where the Record ID# of the facility matches the Related facility on the contact record..
Photo of ARYEH ZELCZER

ARYEH ZELCZER

  • 332 Points 250 badge 2x thumb
This is what I currently have. But this would limit me when i want to relate a contact of a corporation to only some of its facilities and not to all.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
My previous answers cover that situation.