13 Replies Latest reply on Feb 26, 2012 6:08 AM by 4justme2

    Help with simple relationship



      Help with simple relationship


      I'm not making any headway so I thought I'd go back to the very beginning with just two tables to see if I can do something very simple. My tables are Contacts and Notes.

      CONTACTS::_pkCONID is linked to NOTES::_fkCONID. NOTES has a unique serial number __pkNotesID.

      I linked CONTACTS as the parent to NOTES as the child. I have a contol tab on the CONTACTS layoul with a portal to NOTES.

      There's only one field on the portal called Text.

      The goal is to have the user enter notes that combine a timestamp and user stamp with the text from the note.

      I can get everything to work but it doesn’t hold onto my GO TO Related Record.

      My script is: Go to Layout ["Notes_Utility - Table View" (Notes_Contact_Detail)]

      Go to Related Record [Show only related records; from table: "Notes_ContactDetail"; Using layout; <Current Layout>]

      New Record/Request

      Go to Field [Notes_ContactDetail::Text]

      But invariable my relationship fails to link the primary key from CONID to the foreign key in the Notes table.

      Any idea what I might be doing wrong?

        • 1. Re: Help with simple relationship

          I believe the problem is that you are going to the  Notes_Utility - Table View layout before asking Goto related Records to take you there. You need to drop the goto layout step and just have

          Go to Related Record [Show only related records; from table: "Notes_Contact_Detail"; Using layout; Notes_Utility - Table View

          You can't goto related records in <Current Layout> because you are not going anywhere :)


          After a further look I just realized the problem isn't about GTRR. It's about how you are trying to create a related record.

          1. You can create a related record by enabling allow creation of records through the relationship and entering data directly in the portal. The portal creates the related record by automatically populating the foriegn ID key field in the new child record.

          2. To create a related record by script you first have to grab the primary key (ID Field) of the current record using set variable. Then goto the child table layout. create the new record and use set field to drop the ID of the parent into the foriegn key field of the new record, thus establishing the relationship to the parent.

          • 2. Re: Help with simple relationship

             Hi Ron,


            I see what you mean.  Also, I don't even have a related record in the Notes file... yet (so I can see how I'm creating confusion) I want to create a new record and have the related ID autopopulate from the other table.  That being the case, am I using the wrong script all together?

            • 3. Re: Help with simple relationship

               I just editted appearantly at the same time you were posting, to adress the way you are trying to create a new related record :)

              • 4. Re: Help with simple relationship

                Here's a quick and simple demo of a notes technique. It demonstrates how to create related notes and also includes a technique for viewing and editing the notes on the same layout.

                Clicking a portal row sets a field with the note id to establish a relationship to a different TO (table occorance) and display the note in the fields on the layout. Note that the portal is viewing record from the notes TO while the note fields outside the portal are viewing records through a different relationship to the the Notes View TO.

                The portal rows are formated as a button that sets the viewnotesid field with the note id. There is also conditional formatting to highlight the portal row you are viewing and editting.

                The delete button demostrates how to goto a related record. Also note the use of the if statement to make sure the related record exists, GTRR can be very dangerous. If there are no related records to goto it will stay where it is and continue the script. In this case it would stay on the contact layout and delete the current contact frecord.



                • 5. Re: Help with simple relationship

                   Hi Ron,


                  Thanks for sharing the file.  When I click on a portal row or the new or delete button I get a popup that says the action is not modifiable.  I also can't get into the script. This happened to me with several other things I downloaded tonight.  Is it some setting I have in Filemaker that's causing this?

                  • 6. Re: Help with simple relationship

                     First, I realized I made a couple errors when I threw the file together. I just editted the discription and editted and reuploaded the file. So be sure and reread the post above and redownload the file.

                    The problems you are having with downloaded files is probably because you are trying to open and use them while they are still zipped. Make sure you unzip the files first :)


                    I had made reference to using a global for the viewnotesid field when it should actually just be a text field. Ooops :)

                    • 7. Re: Help with simple relationship

                       Keep in mind that the demo is very basic. Things you might want to add would be an error capture in the delete script that would popup a messages if there are no related records found that would say something like "Please select a note to be deleted". Of corse you could always just put a delete button on the portal row specified to delete portal row, and set the relationship to allow deletion of records through the relationship.


                      • 8. Re: Help with simple relationship

                         Doh!! It's late and I just found another small issue. I forgot to group the fields in the portal row to make the whole row a button, so only the notes field was clickable. I readjusted and reuploaded again.

                        • 9. Re: Help with simple relationship

                           This is extremely helpful, Ron.  Thank you so much for your patience with me.  I do appreciate!

                          • 10. Re: Help with simple relationship

                             Happy to help :)

                            • 11. Re: Help with simple relationship

                              Using this same example.  What if I want the enduser to be able to delete notes while I also retain a audit records of all notes that were ever rewritten for this record.  Would it be as simple as recreating another TO where deletions were not permitted?  Or is there a different way I would go about retaining that history?

                              • 12. Re: Help with simple relationship

                                Hi Ron,


                                When I view the relationship on the relationship graph between notes and contacts as well as between notes view and contacts it doesn't indicate that new records can be created as a result of this relationship... yet new records are being created in notes.  Is this because we're using global fiels?

                                • 13. Re: Help with simple relationship

                                   Hi Ron,

                                  What are the possible ramifications with my entering data directly into Notes from the portal, instead of going through the NotesView TO?  I ask because it seems to work if I do not add the script on the portal row... and delete my relationship with the notes view TO.  Do you see any issues with my doing it that way?