4 Replies Latest reply on Sep 12, 2011 11:59 AM by philmodjunk

    newbie here, need a little help with a relationship auto-fill



      newbie here, need a little help with a relationship auto-fill


      I've got two tables. 1 is a list of properties and one is a contact list. The list of properties imports from an excel spreadsheet. I created a button on the property list that runs a script to create a contact based on the information from the property list.

      BUT a contact might have more than 1 property and I don't want to create contacts based on every property. I put a yes/no dropdown list on the property page and thought that the script should basically be: if "yes" create new record on contact table, field x = field x .....

      So I need help doing that, also the issue that doing that successfully would duplicate contacts if more than one property is owned by the same person.

      From the contact view, I want to be able to see what properties the contact owns - probably through a portal I guess.


      New to filemaker, first database design. thanks in advance.

      using FM 11 pro advanced.

        • 1. Re: newbie here, need a little help with a relationship auto-fill

          And what data, in what fields fo Properties do you want to use to create the contact record?

          Let's assume you have a single field, Name, in the Properties table. It's simple to modify the following script to work with more fields so that will serve for this example:

          First define an auto-entered serial number field in Contacts to unqiuely identify each contact. Don't rely on names as they aren't unique and people change their names. Using the serial number which will always be unique and will never change avoids issues with duplicates and changes to a contact's name.

          Define a matching number field in properties and define this relationship:

          Contacts::ContactID = Properties::ContactID

          Now you can create a script for your button that works like this:

          Freeze Window
          Set Variable [$Name ; Value: Properties::Name]
          Go To Layout [Contacts (Contacts)]
          New Record/Request
          Set Field [Contacts::Name ; $Name ]
          Set Variable [$ID ; Contacts::ContactID]
          Go To Layout [original layout]
          Set field [Properties::ContactID ; $ID ]

          This script copies the imported name from properties to a new name field, then brings back the new records serial number and assigns it to the property record to link them together. A portal to Properties can now be placed on the contacts layout to list all properties for that contact. If you have more/other fields in properties that contain data for your contact, modify the parts of the script shown in blue, adding more steps for each such field.

          You can also format the Properties::ContactID field with a value list of existing contacts and contact names so that you can link a newly imported property to an existing contact instead of generating a new contact record.

          Also, it can be very common for there to be a need to link more than one contact to a given property. This can be done with a more sophisticated table structure where a third table serves as a "join" tble between properties and contacts. Let me know if this is something you also need.

          • 2. Re: newbie here, need a little help with a relationship auto-fill

            I would have a portal on the property layout so that you can see if that contact already exists in the contact table.  If it does you can set a button to set the Propoerties::contactID to that value and not bother creating a new record in contacts.  If it doesn't the button can copy the contact data over into a new contact record, setting both the Properties::contactID and Contacts:contactID to the same value.

            Then from the contact layout you can have a portal showing a list of properties related to each contact by contactID

            • 3. Re: newbie here, need a little help with a relationship auto-fill

               thanks, I'm really trying hard to figure this out. Let me revise what I'm trying to do -


              Table - Lease List

              Key Field - LEASELISTID

              fields - True Owner Company, Lease Number, address, phone, square footage...


              Table - Lease List Verified

              Key Field - LEASELISTVERIFIEDID

              other fields - True Owner Company, Lease Number, address, phone, square footage


              Table - Contacts

              Key Field - CONTACTID

              other fields - Company, address, phone, square footage


              I currently have a portal on the Contact layout that shows the records from the Lease List Verified table


              I import data from excel into the Lease List table and modify some of the data. Once I'm done modifying the data, I want to click a button "Verify" that looks up the field Lease Number in the Lease List Verified table. If the Lease Number exists, I want it to update the record with the data in the corresponding fields from the Lease List Layout. So it goes from Lease List to Lease List Verified by clicking the button. If the Lease Number doesn't exist, I want it to create a new record in the Lease List Verified table and input the data from the Lease List table. 




              • 4. Re: newbie here, need a little help with a relationship auto-fill

                Do you know how to define relationships between your tables?

                If you link your first two tables in a relationship that uses the Lease number, you can use that relationship to add new records to the 2nd table with a single script step.

                Define this relationship:

                Lease List::Lease Number = Lease List Verified::Lease Number

                Doubleclick the relaitonship line and enable "Allow creation of records via this relationship" for Lease List Verified.

                In each matching field in Lease List Verified, use the auto-enter tab in field options to specify that the field use the looked up value setting to copy the value of the matching field in Lease List.

                Now this script step, performed from the Lease List layout, will create a new record in Lease LIst Verified if a record with the same lease number does not exist and will trigger an update if it does:

                Set Field [Lease List verified::Lease Number ; Lease List::Lease Number]