5 Replies Latest reply on Feb 8, 2012 12:49 PM by philmodjunk

    How to get data field in Layout A to also show in Layout B

    Willz

      Title

      How to get data field in Layout A to also show in Layout B

      Post

      I’m working with FM Pro 11 db file with a Layout called “Layout A” - I made a Table in the db from an external FM file called “Table B” – And I made a Layout in the db from that new Table “Layout B”  -  PERFECT!   I see all the data in" Layout B" for each client.  Problem?  Have been unable to make relationship so data fields in the new Table B Layout can also be seen in the Table A layout.  I’m using primary and foreign keys but no data connection happening.  Please advise. Thank You

      Screen_shot_2012-02-07_at_8.14.50_PM.png

        • 1. Re: How to get data field in Layout A to also show in Layout B
          philmodjunk

          In order for data from a record in Table B to be visible on a layout based on Table A, there needs to be a record in table B where the value in _fk_contactID equals the value in _pk_ContactID. Since your relationships and layouts appear to be correct, I'd check the values in these fields. Usually, the pk field is defined as an auto-entered serial number field so that each new record automatically gets a unique value. The fk field must then receive a matching value--somthing that can be done via a drop down list on the fk field, a script, or automatically via "Allow creation of records via this relationship" when you first enter data into a field to Table B that was placed on the Table A layout.

          You may also want to look into portals if there will be multiple records in Table B linked to the same record in Table A.

          • 2. Re: How to get data field in Layout A to also show in Layout B
            Willz

            Thank you for your quick response PhilMonJunk.  Yes, I understand your instructions and all is setup accordingly.  However, there are no values in the _fk_contactID field of Table B when generating a record in its corresponding db Layout.  Table B generates auto-enter serial number in field _pk_mostID when creating new record…but the  fk_contactID field is empty.  Parent is FMdb “Contacts” Table A.  Child is FMdb “MOST” Table B.

             

            Here is what I have…Script button in Table A (FMdb “Contacts”) copies cFullName of current record being viewed in Table A… then the script goes to external FMdb “MOST” and creates a record in there using cFullName.  Items in this “MOST”db are selected creating a score for that individual client cFullName.  All these records in “MOST”db show up in the corresponding Table B Layout in the Parent db perfectly.  Cannot make a single field from Table B work in Table A.  Relationship connection not figured out.  Do I need another table with two foreigh key fields?  Not sure.  Thank you!

            • 3. Re: How to get data field in Layout A to also show in Layout B
              philmodjunk

              However, there are no values in the _fk_contactID field of Table B

              Which is why you don't see any data in the fields from Table B. Without a value in this field that matches to the __pk field on the other side of the relationship, there is no way for FileMaker to connect a record in A to a record in B.

              Here's a scripted method:

              Set Variable [$ID ; Table A::__pk_ContactID]
              Go To layout ["Table B" (Table B) ]
              New Record/Request
              Set Field [Table B::_Fk_ContactID ; $ID]

              This establishes the needed link between the new record in B with the current record in A

              If you will do this multiple times for the same Record in A, you'll need to do more than just put fields from layout B on Layout A. Either you use a portal to B so you can list all the related records from B, or you turn your layout design around and base the layout on B with records added from A.

              • 4. Re: How to get data field in Layout A to also show in Layout B
                Willz

                The solution was so simple – Your suggestions made me think the logic through.  I just changed the match fields hook from ( _pk /_fk) to (cFullName).  Using (cFullName) as the match field solved the problem perfectly. 

                Then, I did as you suggested and made a portal to Table B.  Excellent!  Thank you for your ideas.

                I still don't understand why _fk fields are empty or how to use them for this kind of scenario.  FM training is not clear about how to format the foreign key field.  I know that in the relationship diagram dragging the hook makes _pk  EQUAL  _fk but the primary key serial number doesn't copy into the fk field, It's just indexed I think.  In my solution cFullName in Table A EQUALED cFullName in Table B.

                Thank You!

                 

                • 5. Re: How to get data field in Layout A to also show in Layout B
                  philmodjunk

                  Matching by names is not a good idea. Names are not unique, people change their names and correcting data entry errors when you have related records linked by the incorrectly entered name are also an issue. A serial number primary key is a much better option all around as it avoids all of those issues.

                  There is no "formatting" needed for foreign key fields. They are simple data fields (often of type number like we have here), linked to a primary key field in another table. Most often, the primary key field in FileMaker is an auto-entered serial number as this is the simplest way to give each record a unique value that never need change.

                  "fk" and "pk", by the way, are just characters that are part of the field's name. They confer no special properties on the field. They just make it easier for you, the developer to know the purpose of these fields and the underscore characters ensure that alphabetic sorting of the field names will list them first--a convenient feature for many of the dialogs where you might need to select a field when working with FileMaker.

                  The thing to keep in mind is that foreign key fields do not automatically receive a value to link them to a record in another table. You have to set up a system that enters a matching value from the correct record in the parent table. (The table with the primary key.) There are a number of different ways to do this. Some are simple and others are complex, the best method to use depends on the structure of your data (Do you match one record in table A to one Record in Table B or do you match one record in Table A to many records in Table B...) and the design of your layout--and the design of your layout is driven by the real world requirements for fast and accurrate date entry.

                  The script I posted earlier is a simple scripted method for creating a related record. Here's a method that does not require any script.

                  You have this relationship:

                  TableA::__pk_ContactID = TableB::_fk_contactID

                  Double click the relationship line and select "Allow creation of records via this relationship for TableB".

                  Go to the layout for TableA and use the portal tool to draw a rectangle on the layout. Portal Setup... pops up.

                  Select TableB in the "Show related records from" drop down. Click OK.

                  Select fields from TableB to put in your portal. For education purposes, include the _fk_ContactID field, but you don't need it to be in the portal for this to work.

                  Now, try entering data in any of the fields in the portal row except the fk field. What you should see is that you can enter data into the fields and the fk field will automatically get the same value as the current record's pk field from TableA. This created one new related record in TableB that is linked to TableA. A new blank row appears in your portal and you can now enter data into this blank row to add a second related record in TableB that will also be linked to the same TableA record.