x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 74 subscribers
  • Views 565 views
  • Users 0 members are here
Browse Forums
Announcements
106
topics
32
replies
General Discussion
6k
topics
18k
replies
Scott-Plus

New custom entity with a many to many relationship

Posted By

Using the Advanced Customization Wizard, I have created a new entity above Company called "Committee". Out of the box, this new entity (Committee) can have multiple Companies belong to a single Committee. This is great, however, I need to be able to have a Company be able to belong to multiple Committees. Is there any way to use the Advanced Customization Wizard to create this type of many to many relationship?

As it stands, the wizard has added a field to the Company table with a comp_CommitteeID (or something like that) but what I would need is another new "linking" table that could hold multiple Company to Committee relationships. (And I don't want to use Relationships, because they need to ability to search by Committee... and have Committee be like any other normal entity, with tabs... not the least of which would be Companies.)

Thoughts?

-Scott

Client has CRM v7.1 SP2 integrated to Sage 300 ERP.

  • 0

    Hi,

    You can't really do this using the Wizard - if you select Owned by Companies and Has Companies, you'll end up with two 1:many relationships, rather than a many:many. You'll want to add a new link table in Administration -> Advanced Customisation -> Tables and Databases.

    I'd reckon that you can use the existing Address_Link table as your model (you'll probably ignore the link types). You'll need columns for the company ID and the committee ID. Once you get that done, pop in a view to join Companies and Committees using your linked data, and you should be good to go.

    The entity creation wizard doesn't restrict you in the customisations that you can do for a new entity - you can always extend an entity afterwards if it doesn't do exactly what you want.

    Hope this helps

    Rob

  • 0

    Thanks for the response, Rob. I figured that might be the case, but I was hoping there might be some way of doing it through the Wizard, as I am not looking fwd to putting together the logic to update the link table.

    Mind you, now that I think about it, maybe I just need a table level script to populate CommitteeLink whenever a new record is added to Committee. I'll have to research that a bit. Thanks again!

*Community Hub is the new name for Sage City