1 Reply Latest reply on Apr 15, 2011 11:47 AM by philmodjunk

    Lookups do nothing



      Lookups do nothing


      I know this has been explained many times but I'm still very confused. I cannot for the life of me get lookups to do ANYTHING.

      I have 1 database containing 3 tables:

      Table 1: ACCOUNTS
      Table 2: EVENTS (that occur at the Accounts)
      Table 3: MASTER TRACKING (contains records of things shipped accounts and usually "related" to Events. )

      Tables 1 and 2 are already populated using imported Spreadsheets.
      Each record in ACCOUNTS has a unique ACCOUNT ID record.
      Table MASTER TRACKING is currently empty.

      I have created a relationship between ACCOUNTS and EVENTS using the ACCOUNT ID.
      I have created a relationship between EVENTS and MASTER TRACKING using the SAME ACCOUNT ID. (I have set the relationship to "Allow creation of records in this table via this relationship" on the MASTER TRACKING side.

      What I want to do is the following:
      Have records AUTO CREATED in MASTER TRACKING using lookup fields. NAME, INVOICE #, etc.  from the EVENTS table.

      I have created the lookup fields but nothing happens. How do I have FileMaker auto-create records based on the relationships/lookups?

      I have tried all of the tutorials I can find but none seem to do anything.






        • 1. Re: Lookups do nothing

          Accounts::AccountID = Events::AccountID

          Events::AccountID = MasterTracking::AccountID   (enable allow create for MasterTracking)

          Accounts::AccountID should be an auto entered serial number. The other AccountID fields should not be auto-entered serial numbers.

          If a record in Events has the account ID: 1234, and you've defined a Name field to use a looked up value field option to copy from a field in Events, then creating a new record in MasterTracking and selecting, or entering 1234, should cause the value to be looked up and appear in the name field.

          Your relationships don't look like they are set up right here. The relationships will only work if you have one Events record for each Accounts record and that seems unlikely. I think you should either have this relationship:

          Events::EventID = MasterTracking::EventID with Events::EventID defined as an auto-enter serial number

          or Mastertracking should link directly to the accounts table instead of events.

          The values you are looking up: Name, Invoice#, etc do not look like values that should be looked up. Looked up value settings should only be used when you need to copy the current value of a field from the look up table and don't want that value to change when the value in the lookup table changes at some future date. A classic example is to use Looked up value fields to copy the current unit price from a products table in to the line item entry of an invoice. When the business owner changes prices, the new invoices should copy this new value, but existing invoices should not show a change in value. In your case, once you have workable relationships, you can likely just include the fields from the related table directly on your layout instead of using looked up value settings to copy the values.

          Here's a thread that may help you sort this out. It discusses both approaches and how to implement them: 

          Auto Fill