11 Replies Latest reply on Dec 31, 2009 5:53 AM by trouty

    Problems with relationships

    trouty

      Title

      Problems with relationships

      Post

      Hi,  hopefully, someone has the answer to my problem.

       

      Within 1 database, I have linked 3 tables with relationships as follows:

       

       table1::id*    =    table2::index          (table 2 is included in a table1 form as a portal)

       

       table2::policy no*    =    table3::policy no            (table3 is a single form)

       

      I want my colleagues to enter data into a form holding solely table3 data, where they will be required to enter a policy number (which is a link field).

      I will then go into the table2 portal (that's within the form containing table 1 data) and also enter the policy number at a later date, thus associating the data in table2 and table3.

      My problem is that when data is entered into the form containing table3 data, this is creating records also in table2 and table1.  I've tried all combinations of "Allow creation of records in this table" in the "Edit Relationships" dialogue box to no avail.  I've recently moved over from Lotus Approach database, and this combination worked, so I'm baffled with Filemaker.

       

      Any help would be much appreciated - this is the final piece of converting my database from Lotus Approach (thank god!) so any help very much appreciated.

       

      Trouty 

       

      (*id - is auto enter incrementing serial number field)

      (the policy no's in both table2 and table3 don't have any unique or self-generating serial numbers) 

        • 1. Re: Problems with relationships
          littledog
            

          "Allow create record" must be disabled in relationship.

          But I don't clearly understand tour database - can you provide some sample? 

          • 2. Re: Problems with relationships
            trouty
               Hi Peter, when you say provide sample of data, how do I do this?
            • 3. Re: Problems with relationships
              davidanders
                

              You  can capture screen snipits with pdfcreator

              http://sourceforge.net/projects/pdfcreator/

              You can save captures to Google

              http://www.google.com/search?q=file+save+free 

              • 4. Re: Problems with relationships
                philmodjunk
                   You can also simply type in a few lines of text to illustrate what you want in many cases.
                • 5. Re: Problems with relationships
                  trouty
                    

                  Hi,  sorry if this is causing headaches, it's difficult to explain:

                   

                   

                  Within 1 database, I have linked 3 tables with relationships simplified as follows:

                   

                   table1::id*    =    table2::id    =   table3::id    

                   

                   

                  (for table1  the id* field is self incrementing index)

                   

                  I want a colleague to access a form containing solely table3 fields.  They will enter data into this form (using IWP) and the id field within table3 will contain a unique document number that they also enter.  I will then access table2 through a portal in table1 (at a later date), and will enter data and put in the unique document number in the id field in table2 (.  This will then allow the record entered in table3 to be linked to the record in table2.   The problem I have is that I've run a trial of this and upon entering data into table3, a record is being created in table2 and table1.  I don't want this to happen and haven't had any problems with IBM Lotus Approach in the past with this configuration.

                   

                  Does this make any sense? 

                  • 6. Re: Problems with relationships
                    philmodjunk
                      

                    That shouldn't happen.

                     

                    Are table1, table2, table3 different table occurrences with the same data source table perhaps?

                     

                    If you open up Manage | Database | Relationships and double click the boxes for table1, table2 and table3, do you see the same source table or different tables?

                    • 7. Re: Problems with relationships
                      trouty
                        

                      Hi Philmodjunk,  here is gif of the relationship

                       

                      image of relationship graph 

                      id is indexed, auto enter serial no.    index is a number field.    policy_no in both tables are text fields.

                       

                      All 3 table are different.  Does this help with my query? 

                      • 8. Re: Problems with relationships
                        philmodjunk
                          

                        Well that rules out an easy explanation of this. Now to dig a little deeper...

                         

                        What "Allow creation of records via this relationship" options have you selected for each of these?

                         

                        "The problem I have is that I've run a trial of this and upon entering data into table3, a record is being created in table2 and table1."

                        I'm reading your last post as Table 3 = new business..., table 2 = commission s... and table 1 = commission detail....

                        Please describe step by step how you are doing this. Layout details are important here. Please indicate what table occurrence your layout is referencing in layout setup... and if you are using a portal, please identify the table occurrence specified in portal setup.

                         

                         

                        • 9. Re: Problems with relationships
                          trouty
                            

                          Hi PhilModJunk,

                           

                          have tried all permutations of "Allow creation of records via this relationship".  I would assume that when entering data into table3 (on on far right), it would have to have Allow creation ticked only.  But I've also tried Allow in middle database, allow in middle and right and allow in all three, and allow in just middle table.  God knows what's happening?

                           

                          Trouty 

                          • 10. Re: Problems with relationships
                            philmodjunk
                              

                            To repeat:

                             

                            Please describe step by step how you are doing this. Layout details are important here. Please indicate what table occurrence your layout is referencing in layout setup... and if you are using a portal, please identify the table occurrence specified in portal setup.

                             

                            I need to know the last settings you had when you created a record in the last table and found matching records were created automatically in the other two. I'm looking for the specific detail that caused this to happen as this is not the expected behavior. The Allow creation option won't create a related record unless one of two things take place:

                             

                            You enter data in a portal's bottom blank row.

                            You enter data into a field from a related table where no related record yet exists.

                            • 11. Re: Problems with relationships
                              trouty
                                

                              Hi PhilModJunk,

                               

                              Have sorted it, hadn't set the correct database for the 3rd table.

                               

                              Many thanks and Happy New Year