3 Replies Latest reply on Mar 4, 2012 4:51 PM by kedikat

    Relationships For crying out loud

    kedikat

      Title

      Relationships For crying out loud

      Post

      This is getting ridiculous. I have read various articles. Watched video tutorials. Searched all over.

      But I cannot get the simplest relationship action to operate.

      Is there a tutorial, for Filemaker, that shows a SIMPLE construction of two tables and making a working relationship? The video tutorial for "basic relationships" is not in the least basic. I have tried to populate fields via relationships between two stupidly simple tables. Nothing happens.

       

      Table Names has fields nameID, and name. NameID is automatic serial unique. Name is just names. I have kept them unique during entry.

      Table Phone has fields phoneID, nameID, name and number. PhoneID is automatic serial unique, name is a drop list generated from Names table, field name. numbers is numeric field.

      I wish to select a name from the drop list and enter a phone number. The nameID should fill in via relationship. The saved record will have a unique phoneID, nameID can be repeated in this record, it will match the name and of course the number.

      Seems simple enough. I have tried making the field name in Phone table a drop list. Works fine. But the nameID does not fill in. I have tried placing the field name from the Names table into the layout for Phone. Then the drop list does not work.

      Etc, etc, etc.....

      Does anyone know of a simple tutorial to enlighten me?

        • 1. Re: Relationships For crying out loud
          bumper

          In the Manage Database panel on the Relationship tab, in between the two tables there will be a line from the NameID in Names to the NameID in Phones, and there is a box on the line. Double click it and you will see a text representation to the relationship. Again NameID to NameID, AND the little check box at the botton under Phones that reads "Allow creation of records in this table via this realtionship" must be checked.  

          You may be complicating the issue a bit, normally there is no reason to have the name in the phone table on a separate layout, instead put a portal from the phone number table on the Name layout and put the NameID and number fields in the top portal row while in layout mode, and IF you have clicked in the little box in the TOG as per above, when you enter data in the number field FM will go grab the NameID and put it in the NameID in Phones, making the relationship and creating a new record in the Numbers table.

          To keep my mind straight on which is what to whom, I label the keys fields (ID) to remind me of where the fields came from and what is their purpose. The NameID in the name table would be called the primary key, I would add either a prefix or suffix to that field name, NameID_pk, this is the field that is receiving the auto generated serial number when a name record is being created. in the other table, Numbers, I would call the NameID field the foreign key, and give it a suffix of NameID_fk, this would be a simple number field that FM will fill in for you (make sure type of the two fields from each table agree, number to number) and back to that little box, make sure the math sign between the two fields in an equal sign, =. This is called a equijoin, so if the primary key in Names is 111 and the foreign key in Numbers is also 111 and if it is set to be an equijoin, you should should see the phone number. HTH

          • 2. Re: Relationships For crying out loud
            kedikat

            Thank you for the reply.

            I have tried using portals. I tried again with your suggestions. However, it gives me unexpected results ( unexpected to me that is ).

            I have renamed tables and fields to reflect what I am really trying to do.

            Transmit Table is to record the reception of items for test and repair. I wish this to only have the fields TransID (Unique index), Serial (the item being received), Crew (the customer). One Crew can bring in many Serial.

            The layout is, Drop list of Crew ( a number from 0 to 9 ) It is prefilled with last record visited, as one Crew can bring many Serial. Serial is entered manualy. There is a InDate field automatically filled with a timestamp.

            So a Transmit record has TransID, Crew, Serial, InDate. I wish this record to remain unchanged. I do not want other fields in the record.

            Test Table is to record testing failures for a Serial. The record requires the data from Transmit Table fields, TransID and Crew. It also has a field Test which will contain a text value from a drop list of Test names. TestID unique index and a Timestamp field.

            A Serial can fail many tests. So in the Test table records only TestID and Timestamp fields will be unique.

            Using a portal on the Transmit table, I find records being created or altered in Transmit table, when I only want records created in Test table. The relationship links do work however. New Test table records do receive the related data.

            If I create the portal on the Test table layout, it seems I do not get the relationship function.

            I will try some more combinations.

            • 3. Re: Relationships For crying out loud
              kedikat

              Ahhh

              Got a portal working with respect to the proper record. Odd methodology, but works.

              Thanks for the help.