5 Replies Latest reply on Feb 8, 2011 11:27 AM by philmodjunk

    Relationship screwed up.. but how?

    synergy46

      Title

      Relationship screwed up.. but how?

      Post

      I am running FM 11 Advanced.

      I have these 2 tables:

      MEMBERS: KP_MemberID, LastName, .... , ZIP  (LEFTFIVE is a calculation field:  left(ZIP; 5)  )

      ZIPCODES:  ZIP, City, ST

      When I 'relate' Zipcodes::ZIP to Members::LeftFive I get  a many-to-many <<-->> relationship?  Huh?

      (The real problem with this is that I think this 'weirdness' is preventing my city, state lookups from working)

      I have an almost identical database with almost identical tables and when they are 'connected' they produce the expected

      Members:LEFTFIVE <<----> ZIPCODES::ZIP relationship.

      WZZZ going on?

      ishot-460.jpg

        • 1. Re: Relationship screwed up.. but how?
          philmodjunk

          FileMaker just doesn't have enough info to "know" that either field might be constrained to be unique and frankly, you haven't indicated that this is the case either. If Either "leftFive" or "Zip" were an auto-entered serial number, you wouldn't see "crows feet" on both fields. It isn't an indicator that your relationship won't work here.

          You'll need to dig deeper. Are both LeftFive and ZIP both of type text or both of type number? (May have to check return type of LeftFive if it is a calculation field.) If that's not the issue, you'll need to carefully inspect the values in both fields in both tables to see what might be keeping a record in members from matching to a record in US ZipCodes.

          • 2. Re: Relationship screwed up.. but how?
            synergy46

            Got it! 

            Since the zip code field has no FK, I needed to delcare the zip field as 'unique value'.

            This makes sense because FM can only deteremine the 'one' side of the relationship by the presence of the FK or a 'unique' field.

            "Are we there yet?"

            • 3. Re: Relationship screwed up.. but how?
              philmodjunk

              Well, it changes the look of your graph, but probably doesn't explain why it hasn't been working for you like you expected...

              • 4. Re: Relationship screwed up.. but how?
                synergy46

                After I made the ZIPCODE::ZIP field 'unique' and got the 1 to many relationship showing properly, the lookups worked. 

                Are you saying that the lookups should work even in a many to many?

                • 5. Re: Relationship screwed up.. but how?
                  philmodjunk

                  I would think so, but keep in mind that I only know what you have described in your thread here and that's not much to go on. If you did have several records in the secondary table with identical zip codes, the lookup functions would look up from the "first" such related record.