Address lookup advice required
I've got three tables (Organisations, Clients & Quotes) which I need to link name/address/phone details through.
Each organisation has it's own unique file number (0001 through 9999) "kp_FileRef" and each client is linked to an organisation through a =relationship between Organisations::kp_FileRef and Clients::kf_FileRef so the plan is to be able to view related contacts using this field/relationship to sort by organisation. Each contact has it's own unique serial number so that each record is unique in some way but this number is never used in our running of the quotes/invoices.
When I create a quote I have all the lookup fields for the name/address/email etc based on what is entered in Quotes::kf_FileRef (also =relationed to Organisations::kp_FileRef) so all three tables are linked, and if I enter 0057 in kf_FileRef on quotes, it happily looks up the related Organisation information and the FIRST client linked to that organisation but the problem comes when I need to pick a different client:
For example, say I have four contacts for file 0057, contact one might by chance be the Chairman, so his details come up when I enter 0057 on the quote, but I want to send the quote to the Treasurer so this isn't any good.
I was thinking of having a dropdown list of related contacts once you enter the file number and then when you pick a name it fills in the rest. I have accomplished this roughly by having Quotes::Name_Full defined as a lookup from Clients::Name_Full and then formatted the data entry as drop down list from Field: "Clients::Name_Full" and "Include only related values starting from - Quotes" This now brings up all for contact names (perfect!) but when I click it, obviously all the other lookup fields for address, etc are still on the Chairmans contact details. I think maybe it needs to somehow lookup from FileRef but then pick out the record relating to that one name?
Also, on a side note, it would be handy to have a list of related contacts on the Client layout so we can see who else works for that organisation and what position they hold. I've seen this done in the ContactManagement solution you can download so I'm about to have a play around with that. I suspect the above might be a continuation of this sorting?
Can anyone come up with a quick master plan to solve this using what I've already done or is this sorting business like in ContactManagement going to be the better way to go?
Thanks in advance.