7 Replies Latest reply on May 13, 2009 4:01 PM by Polarpro

    Filemaker as a reference database / simple cross-references

    Polarpro

      Title

      Filemaker as a reference database / simple cross-references

      Post

      Hey there,

      I've been searching the web up and down for examples of using Filemaker as a reference database. I am working on a solution that can be used for encyclopedic purposes, so to speak.

      In particular I'd be grateful for hints about how to implement simple cross-references ("Record A is of related interest regarding record B." ) So, the records I am talking about are of the same kind (reference entries); there is no (classical parent-child) relationship between them.

      Thank you for your input,
      Mike




        • 1. Re: Filemaker as a reference database / simple cross-references
          Jade
            

          Hi Mike,

           

          You may be able to achieve your cross-references using what is called a "self-join".  That is the record ID's are linked to one another in the same table.  There is still a primary and foreign key used.

           

          But in your case, there will probably be a many-to-many relationship so a second "join" table is required to hold the primary and foreign keys.  The relationship graph looks something like: Table A <= Join Table => Table A2

           

          By placing a portal of the Join table on your layout of Table A, you will be able to link different records together.

           

          By way of example, I've used this technique to link family relatives together in a table of people. 

           

          Hope this helps… 

          • 2. Re: Filemaker as a reference database / simple cross-references
            philmodjunk
              

            Mike asks,


            "I've been searching the web up and down for examples of using Filemaker as a reference database. I am working on a solution that can be used for encyclopedic purposes, so to speak.

            In particular I'd be grateful for hints about how to implement simple cross-references ("Record A is of related interest regarding record B." ) So, the records I am talking about are of the same kind (reference entries); there is no (classical parent-child) relationship between them."

             

            If I read you right, you want to store a series of articles in text fields. (one record = 1 article). If the reader wants to view a related article, you want them to be able to select from a list of related articles. In this same situation, it's likely that you would want them to be able to start off the process by locating a group of articles that are all about a subject selected by the reader. (If the reader wants to know about "frogs", they would enter/select "Frog" to see a list of "frog" articles.)

             

            That sound right?

             

            I think the best tool for you to use in FMP is sometimes called the multi-value key. In your case, it is possible to store a list of keywords in a 2nd field in the same record as your article and then use those keywords to link to articles with at least one key word in common. Here's how you set up a multi-value key field.

             

            Define a text field as part of your articles table. We'll call it KeyWords.

            Put the field on a layout, make it several lines of text tall and format it to have a scroll bar.

             

            Type in the following key words:

            Apple

            Orange

            Kiwi

            Switch to a different record and type in

            Apple

            Strawberry

            Switch to a third record and type in

            Strawberry

            Kiwi.

             

            Use manage database/Relationships to create a self join from KeyWords to KeyWords. Here's how:

            Click on the KeyWords field, drag out into an open area of your Relationship Graph and then back to KeyWords without releasing the mouse button.

            When you release the mouse button, an "Add Relationship" dialog box appears. Name this new Table Instance "Cross Reference"

             

            Click OK twice to leave Manage Database.

            Locate your layout where you list your articles and enter layout mode.

            Use the portal tool to create a portal on your layout.

            In the show related records from drop down, select "Cross Reference".

            Place a field that displays the article titles in this portal.

            Return to Browse mode.

             

            Now, if you view the article from Record 1 above, you should see Article 2 listed because both KeyWord fields contain "Apple". You should also see Article3 because both Article1 and Article 3 contain "Kiwi". Following the same logic, if you view the record for Article 2, you'll see the title for Article 1.

             

            You now have a cross reference between your different articles.

             

            Hope that works for you.


            • 3. Re: Filemaker as a reference database / simple cross-references
              Polarpro
                

              Hey Jade & PhilModJunk,

               

              Thank you very much for your detailed answers! Both solutions (self-join/multikey and join-table) work fine. I've tried them and played with them for a while.

               

              I think I'll go with self-join/multikey, as in this case there is only one relationship between the two related records. (I like working with join-tables, but as I am going to filter the portal in which the related records are displayed I try to keep my data tunnels as short as possible. Every additional hop makes the filtering process slower, it seems to me.)

               

              I've added a filtered portal where the user can search for the records he wants to cross-refer to. Clicking on a record's name in this portal runs a script that adds the IDs of these two records to the MultiKeyID fields of both records. Works very well!  :)

               

              Thanks again for your great help!

               

              :) Mike

              • 4. Re: Filemaker as a reference database / simple cross-references
                philmodjunk
                   Glad you worked it out. I liked the multi-value key approach for you because you have some Filemaker functions that can help you automate the process of building your keyword value list.
                • 5. Re: Filemaker as a reference database / simple cross-references
                  Polarpro
                    

                  Hey PhilModJunk,

                   

                  Thanks again for your advice from a while ago. My cross references work fine with the scripts I use to manage my multi-line key lists.

                   

                  Now, I'm curious: You mentioned that there are "some Filemaker functions that can help to automate the process of building a keyword value list". May I ask you what you had in mind?

                   

                  :) Mike

                  • 6. Re: Filemaker as a reference database / simple cross-references
                    philmodjunk
                      

                    I was deliberately vague, because a lot depends on your database and what you want to see in your value list.

                     

                    Example: Set field [tablename::KeyworkField, Substitute (tablename::ArticleField, " ", "¶")]

                     

                    Will load your key word field with every word that exists in your article. That may be overkill for your database, but it should work.

                     

                    There are also other expressions that return a value list:

                     

                    List(Field1; field2; field3) will return a value list of those three field contents.

                     

                    List (relatedtable::field) will return a list drawn from all the related records in another field.

                     

                    If you already have a defined value list, ValueListItems ( fileName ; valueList ) can return a list of those values.

                     

                    You can also perform finds for specified key words on your database and use Replace Current Field Contents with a specified calculation to append a key word to the keyword field of all the records in your found set. (Example; search for "Catfish" in your database, then use the tool to append "Catfish" to the keyword field of all the records you just found.)

                     

                    That should get you started. You can look these functions or tools up in the on line help for more information.

                    • 7. Re: Filemaker as a reference database / simple cross-references
                      Polarpro
                        

                      Hey.

                       

                      (Wow, that was a quick answer!)  Thanks for pointing to the "List & Co." direction. Very helpful!

                       

                      :) Mike