4 Replies Latest reply on Jul 12, 2012 11:40 AM by AlastairMcInnes

    Multiple Lookup Fields

    AlastairMcInnes

      Title

      Multiple Lookup Fields

      Post

      Hello,

      I've inherited a db containing data about books. Amongst many other fields, each book has a Subect Field, which is linked via a relationship to a so-called BISAC code. When the user enters the subject (in English) the system automatically fills in the related BISAC code.

      So far, so good.

      What they've asked me to do is to allow up to three codes. They're happy for me just to triplicate the existing pair of subject-code fields and I'm happy not to get involved in portals if I don't have to.

      Which leads me to my question. I can, of course, create three relationships:

      Title::Subject_1 <=> BISAC_Codes::Subject
      Title::Subject_2 <=> BISAC_Codes::Subject
      Title::Subject_3 <=> BISAC_Codes::Subject

      but I don't see how the system would know which relationship to use when calculating the values of BISAC_1, BISAC_2 and BISAC_3, so I doubt this would work.

      I had a look at the Lookup function but that only lets me look up via a relationship so would suffer the same problem as the previous paragraph.

      What I really want is something like a DLookUp in Access, or a VLookUP in Excel - I can supply a table name and the value of one field and extract the equivalent value from another field.

      I'm sure this is simpler than I'm making it, but I can't see how to do this in FM. I'm using version 9 if that makes a difference.

      Thanks in advance
      Alastair 

        • 1. Re: Multiple Lookup Fields
          philmodjunk

          Let's start with this:

          Title::Subject_1 <=> BISAC_Codes::Subject
          Title::Subject_2 <=> BISAC_Codes::Subject
          Title::Subject_3 <=> BISAC_Codes::Subject

          This has some possible problems that could just be the way you typed it up or could be a real issue if you tried to set it up this way in Manage | database | Relationships.

          If you did it this way, it will work:

          Title::Subject_1 = BISAC_Codes::Subject

          Title::Subject_2 = BISAC_Codes 2::Subject

          Title::Subject_3 = BISAC_Codes 3::Subject

          You'd need 3 table occurrences of BISAC_Codes. In Manage | Database | relationships, make a new table occurrence of BISAC_Codes by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Repeat these steps to get the third ocurrence.

          Then you can set up 3 BISAC_Code fields--each with a looked up value setting that references a different occurrence of BISAC_Codes.

          But what do you do if they then decide they want 4, 5 or even more subject/code pairings? Will you keep modifying the design of your databse to add more fields and more relationships?

          What you have here is a many to many relationship and if you set up a join table between Title and BISAC_Codes, you could link a given record in Title to as many BISAC Codes as you need and not need to do any further redesigns of your database:

          Title----<Title_BISAC_Code>------BISAC_Codes

          Title::TitleID = Title_BISAC_Code::TitleID

          BISAC_Codes::Subject = Title_BISAC_Code::Subject

          But this WILL require using portals to implement. A portal to Title_BISAC_Code on a Titles layout could list all the BISAC Codes for that title and a portal to Title_BISAC_Code on a BISAC_Codes layout could list all Titles linked to that subject.

          I know that you said:

          I'm happy not to get involved in portals if I don't have to.

          But designing database solutions in FileMaker really can't get very far without portals. Can't remember the last time I created more than a simple test file that didn't have at least one and usually I need a bunch of 'em to get the job done.




          • 2. Re: Multiple Lookup Fields
            AlastairMcInnes

            Many thanks, Phil. You explained to me once before how to duplicate a table instance on the relationships form but I'd forgotten that they ended up with different names so, of course, they can be referred to separately.

            I know that setting up a proper many-to-many relationship is the way to go but the database is in such a mess, it hardly seems worth the hassle. For example, there are 4 sets of fields for authors, each set of which has first, last and full name. It seems a little late in the day to be trying to impose order on the thing. There isn't a single portal on any of its 100 or so layouts.

            At some point, they may ask me to tidy the whole thing up (start again, essentially) and then I'd be happy to do it properly. I've developed a completely new database for another company in the same group (with a lot of help from this forum) and I'd like to think that it's a far better design with portals all over the place!

            Cheers,
            Alastair

            • 3. Re: Multiple Lookup Fields
              philmodjunk

              It seems a little late in the day to be trying to impose order on the thing. There isn't a single portal on any of its 100 or so layouts.

              Ouch!!

              I'd consider an incremental approach to cleaning up the structural issues with frequent testing and pausing to let the users get used to each change as it becomes a reality. If you can get their support to do that, you can gradually "grow" their system into something that's got a decent design.

              • 4. Re: Multiple Lookup Fields
                AlastairMcInnes

                I know you're right ... <sigh>   Frown