Customer changes company - affects look-up data

[Erin_Thiele]

My table catalogs specific actions/conversations that may or may not lead to awarded projects.

The main/first column is the unique action/conversation. The next columns are 1) who is the contact (main person or persons initiating the action) and 2) what company they are with (sometimes this may be multiple companies if it’s a collaboration). Other columns include attachments, budgets, etc that are connected to that action/conversation/project.

Here is my issue: my company column is linked to the contact column via lookup (I have a separate contacts tab). If the contact changes/leaves companies, then the action becomes modified (either labeled with a new company or no company at all). I don’t want to disconnect the lookup, but we can’t have an action item with the wrong company.

There is also a company tab that we use to track specific legal documentation by company.

Suggestions??

[Julian_Kirkness]

Hi

By the sound of it you’re changing the Contact’s record if they move companies (assume here that you also have a Companies table) to show the new company?

I would suggest that rather than doing this you should create a new record for the contact (presumably their email etc would change). You could add a field to Contacts to show their Status (Current/Left for example) and then could filter by Current.

Just some thoughts = let me know if you need more assistance.

[Elias_Gomez_Sai]

Or even create a History table form contacts! More complicated but more flexible :stuck_out_tongue: