Relationship or script to auto generate records in another table with a different relationship

[gerg nnud]

Hope someone can help. I have a number of related tables.

 

Companies

Company_addresses

Personnel

Personnel_addresses

 

Companies relates to Personnel and Company_addresses

Personnel relates to Companies and Personnel_addresses

Company_addresses relates to Personnel_addresses (sort of)

 

The intention is to have unlimited addresses for both companies and the personnel related to those companies

 

In the companies' form layout I have a portal that shows the related personnel. This relationship is based on a company_ID key.

 

In the companies' form layout I have a portal that shows address fields. This relationship is based on a multikey drop-down global field. In this way I can select from a value list that includes 

Address 1 - Street, 

Address 2 - Postal, 

Address 3 - Billing, 

Address 4 - Shipping, 

Address 5 - Other.

This displays each address without requiring any more real-estate on the layout.

 

In the personnel's form layout I also have a portal that shows address fields. It's the same concept as above but with a separate value list that includes

Address 1 - Work, 

Address 2 - Home, 

Address 3 - Postal,

Address 5 - Other.

 

What I would like to achieve is an auto-enter calculation or lookup in the personnel_addresses table so that when a personnel record is added in the companies' personnel portal the address info for the company's Address 1 - Street is added to the personnel_address table as Address 1 - Work.

 

Just thinking about this makes my head spin so I thought I may be able to lean on smarter people than me via the forum. I could put together a simple db with just this functionality if it would help. Possibly needs a script trigger on the personnel portal's fields? Any thoughts?

[comment]

What I would like to achieve is an auto-enter calculation or lookup in the personnel_addresses table so that when a personnel record is added in the companies' personnel portal the address info for the company's Address 1 - Street is added to the personnel_address table as Address 1 - Work.

 

Once you have this in place, will you ever need to modify it? I mean as long as the individual keeps working for the same company, and the company stays in the same address.

[gerg nnud]

Sorry for the delayed response. Settings wrong in my profile so I didn't know there was any response.

 

I did get this working via a script trigger from memory. I will re-address it when I can because I am still not convinced this is the right approach.

 

In response to your question.

 

The person is related to the company and visa versa. This can be via a record ID or a contact ID or whatever. I am working on making this more idiot-proof but in the past I have setup the relationship via a contact ID field. For various reasons I am not doing that any more. I agree with your concern. The problem is that if a Company (contact) changes name or a person changes companies then the relationship is broken. Same problem with any other related table - notes etc. In the past my solution has been via a laborious work method of first changing the personnel record/s relationship field then going back to the company record and changing the relationship field there to match. Not at all elegant. The other way of course is to just keep data entering and end up with duplicate records. Again not elegant. I don't have a good answer to this yet but I'm working on it (and 100's of other things). I will post my findings if I ever get to fully idiot-proof this.

[comment]

in the past I have setup the relationship via a contact ID field. For various reasons I am not doing that any more. I agree with your concern.

 

Actually, my concern was not about that at all. It didn't even occur to me that a relationship between one Company and many Contacts could be based on anything other than matching CompanyID. If you are doing anything else, you are doing it wrong.

 

My real question was why do you need to duplicate the company's address to the contact's record, when the original address is available through the relationship? The "concern" here is that if the company moves to another address, you will need to update the address in all of its employees records (in addition to the change in the company's record). Conversely, if a contact moves to another company, you will need to modify their CompanyID foreign key field and their work address. This is a redundancy leading to potentially conflicting data.