1 Reply Latest reply on Mar 6, 2013 2:50 AM by LyndsayHowarth

    relating CON to related finance co, mort co, supplier...portal


      I have a CON table for customers, and also seperate table for personnel, suppliers, mort co, finance companies, ins co, subcontractors, (all seperate related tables) I wanted to do one or two tables but I lost the arguement. They are however related tables so...


      I want to have a tab on my CON Data Entry layout to select a related mort co or fiannce co for example, and the data for that related ins co will populate along with additional fields to add specific to that customer such as their account number, ins adjuster name, etc.. I assume the ins co field foe example would bet be a popup menu that comes from the ins co related table but not quite sure how to best do this. I hope I am giving enough info for you to understand what I am asking.


      That way when I select the customer, I can see thier finance co, mort co, sub, supplier, other contacts, all in a portal called Related Contacts, but I need to be able to show additional fields for account number and such specific to that customer.

        • 1. Re: relating CON to related finance co, mort co, supplier...portal

          WOW... They are all people or organisations or entities which could exist in a single table.

          What I am going to suggest would be a lot simpler if you had only one CON.


          You need a joint table... or 2 or 3 or...

          The table would joint individuals in a relationship by 'role-type' and a 'service'. One is the 'supplier' and one is the 'customer'. Let's call the join table "Roles".


          Con::ID       =     CON_Roles_Supplier::ID Supplier  for one relationship instance called "CON_Roles_Supplier"


          Con::ID       =     CON_Roles_Customer::ID Customer  for one relationship instance called "CON_Roles_Customer"

          will give you the basic relationships you need.

          With those 2 pieces of information you can access all the information which exists in their CON (or other) table. The more tables... the more relationships you have to build to the Roles table.


          Roles table can keep date fields for first and last contact or such and status fields for being able to archive data... as well as all the other field info you want to keep.




          - Lyndsay