7 Replies Latest reply on Mar 19, 2009 1:20 PM by Orlando

    Creation of records in related table

    c1

      Title

      Creation of records in related table

      Post

      Hi,

      I have two tables A and B. Table A contains student names and particulars. Table B contains student names, test dates and scores.

       

      I created a relationship between these 2 tables and set the field Name = Name. In the "Edit Relationship" window, I checked the boxes under Table B "Allow create of records in this table via this relationship" and "Delete related records.......".

       

      When I created a new student record in A, I thought this new student will also appear as a new record in Table B. Mine doesn't work like this.

       

      Can anyone advise where did I go wrong? Thanks in advance.

        • 1. Re: Creation of records in related table
          Orlando
            

          Hi c1,

           

          No a related record will not be created in Table B when a record is created in Table A when you set Allow Creation to on, it will however place a blank row in a portal based on Table B that is on your layout for Table A that when you input a value into a field the record is automatically created in Table B. Does this make sense???

           

          If you did need the two created at the same time you could create a script that the user runs to create the student record that also creates a single record in Table B.

           

          However my main concern would be the field you are using to join your two tables, I would not advise using the students name as a key, since a name can change.

           

          I would suggest creating a UniqueID field in Table A, setting Auto-Enter Serial to on, and then having a StudentID field in Table B and using these fields as your link. This way records would not be lost if a student changed there name, or if two students with the same name were in the database then you would not see test scores for the other student.

          • 2. Re: Creation of records in related table
            c1
              

            Hi Orlando,

            Thanks for the explanation and I understand both parts of your answer now. My relationships are working as planned!

             

            Thanks for the tip about the Unique ID field, I just created a new field "StudentID" in both tables and linked them together. 

             

            I am quite used to viewing and entering table in the Table view compared to the form view. In the table view of Table A, it will only show the first related record from Table B. This is set by default and was supposed to be this way rite?

            • 3. Re: Creation of records in related table
              Orlando
                

              If viewing your records in Table View then yes you will only see the first related record in Table B as table view effectively discards the all aspects of the layout apart from the fields, so if you have fields in a portal it will act the same way as if you put the fields on your layout with no portal and only display the first row.

               

              So if you want to see al the related record you would need to go into a layout based on Form View, which you could do with a Go to Layout script step.

              • 4. Re: Creation of records in related table
                c1
                  

                What if I wish to make use of the "Send Mail" feature and I want to retrieve the score of a certain date? There is date field in table B. I tested a send mail and it will call out the first record score of that student iD. How can I write a fmp function that should work as getScore(Date)?

                • 5. Re: Creation of records in related table
                  Orlando
                    

                  This should be fairly easy to achieve, but will depend on how you want it to work really

                   

                  Do you want to select a score for an individual, from a portal row for example, and email that score?

                   

                  Or are you looking for a process that will send all the scores for a particular date, and send all of them to the recipient(s)?

                   

                  Or both? 

                  • 6. Re: Creation of records in related table
                    c1
                      

                    Hi Orlando,

                    For now maybe just select a score for an individual based on a date, then email that score.

                     

                    I will start with this first then I will try to modify it to work for the more advanced processes such as sending all the scores for a particular date. 

                    • 7. Re: Creation of records in related table
                      Orlando
                        

                      Hi c1,

                       

                      Apologies for the delay in getting back to you on this, have not been on the forum that much over the last couple of weeks. Are you still trying to send emails of a specific date?

                       

                      There are a few ways you can go about this, and it all really depends on how you select or input the date of the score you want to send.

                       

                      The easy option is to display all scores for an individual in a portal and select the required row to send the email for.

                       

                      Another option will involves having a global field that you input the date and hit send and a script goes to table B and performs a find for a matching date and then send an email based on the found record, this can then alert you if more than one record is found, or no records.

                       

                      Let me know which option would suite the way you want to use the system and I will put something together for you.