Viewing 'Look up' fields through portals
I'm using FileMaker Pro 10 on a Mac running Mac OS X (Version 10.6.2) - I'm a complete rookie (This is my first attempt at creating a database ever).
I am creating a database for my company - handling customer/supplier data as well as basic functions such as our product catalog, Loan unit catalog and stock.
4 Tables; Customer Organisation, Customer People, Customer Enquiry & Customer sale.
Customer Organisation & Customer People tables > these show 'up to date' info
Customer Enquiry & Customer Sale > These show a record of something unchanging (has happened in the past)
In my Customer Enquiry/Sale tables I have set up 'look ups' for customer info (Name, phone, email, Organisation Name, etc) in order to prevent the information changing if a record is changed in the Customer People table (e.g. Joe Bloggs from Company A makes an enquiry in June 2009 - in June 2010 Joe moves from Company A to Company B - this is edited on his record in Customer People. As the enquiry was made by Joe on behalf of the company (the enquiry belongs to the company and not the person) he was working for at that time (company A) I don't want the organisation information on the enquiry to change from Company A to B)
This is all working fine - my issue is that on my Customer Organisation layout, I have 3 tabs with portals to Customer People (showing the people within the org), Customer Sale (showing the units that have been sold to the org) & Customer Enquiry (showing the enquiries we've had from that org). I have had no issues with the people portal, but the sale and enquiry portal doesn't work as required.
Taking the Joe Bloggs scenario detailed above - when i look at the enquiry through the enquiry portal on the Customer Organisation layout the fields which are 'look ups' on the enquiry layout are displaying Joe's new company details, not the organisation detailed on the enquiry.
Joe Blogs from Company A makes an enquiry in June 2009 - in June 2010 Joe moves from Company A to Company B - this is edited on his record in Customer People. The enquiry record for June 2009 shows Company A's details, which is correct, but if you look at Company A on the Customer Organisation layout and click 'Enquiries' the June 2009 Joe Bloggs enquiry is missing - If you click to Company B > 'Enquiries' the June 2009 enquiry has moved companies with Joe Bloggs.
Basically I need the portal to reflect the look up fields and connect the enquiry with the organisation and not the person. (for both Customer Enquiry and Customer Sale)
Can anyone help with this??
Many Thanks, Lauren Young