Discussions

Expand all | Collapse all

Add many records based on another table

  • 1.  Add many records based on another table

    Posted 08-21-2017 19:17
    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.  


  • 2.  RE: Add many records based on another table

    Posted 08-21-2017 20:05
    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.


  • 3.  RE: Add many records based on another table

    Posted 08-21-2017 20:16
    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.


  • 4.  RE: Add many records based on another table

    Posted 08-21-2017 20:30
    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.


  • 5.  RE: Add many records based on another table

    Posted 08-21-2017 20:40
    Exactly, Im just looking for a way to by default connect corporate contacts to all related facilities in this Corporate contact Facility Assignments.


  • 6.  RE: Add many records based on another table

    Posted 08-21-2017 21:00
    also how can i make a report link to include all contacts (if some have related corporate group and some have related facility?


  • 7.  RE: Add many records based on another table

    Posted 08-21-2017 21:30
    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.
     


  • 8.  RE: Add many records based on another table

    Posted 08-21-2017 21:44
    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..


  • 9.  RE: Add many records based on another table

    Posted 08-22-2017 13:49
    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.


  • 10.  RE: Add many records based on another table

    Posted 08-22-2017 13:52
    My previous answers cover that situation.