1 2 Previous Next 16 Replies Latest reply on Nov 16, 2008 7:32 AM by mandrax

    multi-criteria relationships

    rjacksonla

      Title

      multi-criteria relationships

      Post

      I am trying to "join" records between 2 databases. The "master" database that I'm working from is not properly referencing the "slave" database I believe because the fields on the master database are actual calculations and not text. How can I get the "slave" to  reference these calculated fields?

        • 1. Re: multi-criteria relationships
          Vaughan
            

          It's usual to refer to "parent" and "child" tables rather than master and slave, but whatever suits I guess...

           

          The way two tables are related is by key fields. The "parent" table has a "primary key" field that is usually an auto-entered serial number -- the values in the primary key field must be unique and invariant (unchanging). Though it's possible to use, say, a person's name as the primary key, people's names are neither unique nor invariant.

           

          The "child" table has a "foreign key" field in it. The foreign key holds the value of the parent record. The relationship matches when the value in the primary key and the foreign key is the same.

           

          In the relationship graph, join the table occurrence of the parent and child tables together with the primary and foreign key fields.

           

          Following on from this, databases are only "related" when they have some kind of information in common. In your case, there should be a field in the "master" database that matches a field in the "slave" table. If there isn't then either the tables aren't really related, or you'll have to add the fields and then do the work of matching the related records up manually.

           

          For an example of two un-related tables, think about a Contacts table with people's details, and a Songs table. They'd only be related if the Songs table had a field with a person's name in it (being the artist or composer or something) and the names matched up exactly.

           

          Though as I said, names fields would be a poor choice for match fields because they are neither unique nor invariant. But it'd be a good start.

          • 2. Re: multi-criteria relationships
            mandrax
              

            Hi Vaughn

             

            The fields in both the parent and child match perfectly. The difference is the parent fields are of a calculated result, and the child fields are text. I did a test where I changed the parent fields to just plain text and typed the data in manually, and they did link. So how can "fool" filemaker into thinking these calculated results are text?

            • 3. Re: multi-criteria relationships
              scottm
                

              A couple of questions:

               

              Is the calculation result set to text?

               

              Is the field where the primary key resides indexed?

               

              If the above two answers are yes, can you put a set of brackets around the entire calculation and use the GetasText command?

               

              Scott

              • 4. Re: multi-criteria relationships
                rjacksonla
                  

                Calculation result is set to text, and the primary key field(s) are all indexed.

                 

                 I then used the getastext for the calculation(s), and it still didn't work.

                • 5. Re: multi-criteria relationships
                  Kundinger
                    

                   
                  This type of relationship will work.  I've done this many times for client projects and have no issues.
                   
                  I just created a small test file that I can send to you to show you that is does work...  you could review it to see if you've missed anything.
                   
                  Let me know...
                   
                  Kundinger 
                  • 6. Re: multi-criteria relationships
                    rjacksonla
                       cool! please send me the file and I'll see if it works
                    • 7. Re: multi-criteria relationships
                      TSGal

                      rjacksonia:

                       

                      I have sent a test file to our Technical Support contact.  He should contact you shortly by email and enclose the file.

                       

                      If you haven't heard anything by the end of the day, please let me know, and I'll make sure it gets sent immediately. 

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: multi-criteria relationships
                        Kundinger
                          

                        Hi "rjacksonla",

                         

                        Here is the link to my sample file...

                         

                           <<<  http://tinyurl.com/20081111kun  >>>

                         

                        Let me know if this helps!!!

                         

                        Kundinger 

                        • 9. Re: multi-criteria relationships
                          Vaughan
                            

                          The contents of the two fields have to match *exactly* for the relationship to work. If the parent record has "smith" the child record will not match if it has "smith " with a trailing space. The same applies if there is a leading space, or different spacing in-between words.

                           

                          Generally it's case insensitive though, so "smith" will match with "Smith". It only becomes case sensitive if the field's default language (in the storage tab of Manage Database dialog) is set to Unicode. It might be worth checking that the languages are set to the same for both tables.

                          • 10. Re: multi-criteria relationships
                            rjacksonla
                               Ahhh...I see you have both the databases incorporated into one. Mine are two separate Filemaker documents. Perhaps that's why they're not linking?
                            • 11. Re: multi-criteria relationships
                              Vaughan
                                 No, it does not matter whether the tables are in the same or separate files.
                              • 12. Re: multi-criteria relationships
                                rjacksonla
                                   Well, I'm still not having any luck with this one. I will be able to spend more time over the weekend to troubleshoot it.
                                • 13. Re: multi-criteria relationships
                                  mandrax
                                    

                                  OK, I think I've had a breakthrough on this issue. I apologize in advance if some of my terminology is incorrect or seems confusing.

                                   

                                   I was just poking around while in browse mode, and when I randomly highlighted a text field (with data in it) and then deleted it and hit "undo" it linked all the data for that record! It was if I had "refreshed" the screen if you will. It only did that for that one record. So I think I'm getting somewhere 'cause it now proves that the two databases are communicating with each other.

                                   

                                  I'd like for ALL of the records to "refresh" if you will. If anyone has any suggestions that would be wonderful. 

                                  • 14. Re: multi-criteria relationships
                                    mandrax
                                      

                                    Ignore my last reply - I FIGURED IT OUT! I just needed to turn on the Perform auto-enter options while importing and it worked. Problem solved!

                                     

                                    THANK YOU EVERYONE who  helped me with this! I really appreciate it!!!!!

                                     

                                    -Bob 

                                    1 2 Previous Next