1 Reply Latest reply on Feb 22, 2010 9:32 AM by philmodjunk

    Viewing 'Look up' fields through portals

    LaurenYoung

      Title

      Viewing 'Look up' fields through portals

      Post

       

      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.

       

      For example...

      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

        • 1. Re: Viewing 'Look up' fields through portals
          philmodjunk
            

          I believe your last sentence describes the answer:  "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)"

           

          You should either link your Customer Enquiry by a Customer Organization ID field so that personnel changes do not redefine the link to match it to a different company, or you put in more look ups to copy the company information like you do with the other fields.

           

          If this IS looked up data, then something in the design of your database is triggering a re-lookup of the data. A re-lookup will take place when the match field is edited or if a script or menu command uses "re-lookup" while specifying the match field in the child record.