13 Replies Latest reply on Nov 3, 2016 8:33 AM by beverly

    Changing keys in relationships

    Phil_Mac

      I want to add a new(different) field for my (one to many) relationship. I can add serial numbers to the primary (one) but how do I add those related (data "the many") to the foreign field?

      This is a existing db 277 records(parent side )  4043 records (child side)

       

      Thanks

       

      Phillip

        • 1. Re: Changing keys in relationships
          jimahopkins

          Question:  Does the data field that you want to add to the primary also appear in the foreign table?  And what is the hope here. . .  to narrow down the related records by the second field?  I"m just trying to get a better, more detailed picture.  There are several ways to approach this and they can boarder on the difficult to the easy.  It depends on the numbers of records to be related to your key fields.

           

          Great question though.

          • 2. Re: Changing keys in relationships
            beverly

            In the relationship graph, you can double-click on the "lines" between two TOs and the dialog to change the relationship will appear. You can add the other field from that dialog.

             

            beverly

            1 of 1 people found this helpful
            • 3. Re: Changing keys in relationships
              Phil_Mac

              Hey Jim thanks for the reply

              I have messed up and used a field for the key that I wished I hadn't used.

               

              primary is BatchID    foreign is BatchID  both currently exist is the db, I want to add another field for the key like

               

              primary _pkBatchID  foreign _fkBatchID

              • 4. Re: Changing keys in relationships
                erolst

                jimahopkins already speculated re your motivation for trying to do this.

                 

                If you want to simply display a serial number from the parent (“one”) record on a layout of the “many“, then simply display it as a related field. If you want to use it in a calculation or script from the child context, likewise refer to the related field.

                 

                Adding it to the relationship doesn't add anything, except complexity.

                • 5. Re: Changing keys in relationships
                  jimahopkins

                  Phil, if you're wanting to change the relationship completely from the current related fields to the two new fields that you mentioned, you can do that in the relationship graph as beverly suggested.  But remember, the records that you may have seen in your relationship will totally disappear if the related fields do not hold the information that you're drawing from.  In other words, both fields, foreign and primary have to have the data that keys together in order to use the data from one table to another.

                   

                  Good luck.

                  • 6. Re: Changing keys in relationships
                    coherentkris

                    youll have to write a scripted process that goes thru each child record and sets the new foreign key to the correct value

                    • 7. Re: Changing keys in relationships
                      Phil_Mac

                      Hey Beverly

                        I know that I can add another related field in the TO. the only data that will be in the 'new' child will be when i add a new record.

                      I need to "create/add/copy" the related data from one to the many side as is not there yet, then I can remove my old foreign key field and only have the _fkBatchID in that many side.

                       

                      The reason for this is if I change my current 'one' key it will not change my 'many' key. I only get the 'many' key(data) when a new record is created.

                       

                      Maybe I not be clear enough here.

                      • 8. Re: Changing keys in relationships
                        Phil_Mac

                        Hey Jim

                          Thats what I mean, I lose that connection or the relationship if I do that, thats why I want to convert over to a new relationship (with the correct data in place as not to lose that).

                        • 9. Re: Changing keys in relationships
                          erolst

                          coherentkris wrote:

                          youll have to write a scripted process that goes thru each child record

                          Replace Field Contents utilizing the (still) existing relationship should work just fine.

                          • 10. Re: Changing keys in relationships
                            hbrendel

                            If I understand your question, you have two tables who are related by some fields and you want this relation changed to dedicated keyfields, which you can protect and hide from the enduser.

                             

                            1. Create the new primary keyfield (_pkBatchID) in your primary table. Type number. Populate this with a serial number. The possibility given in the field definitions is OK. Don't change the realtionship yet.

                             

                            2. Now create a foreign keyfield (_fkBatchID) in your related table. Also type number.

                             

                            3. Go to a layout based on the related table, put the _fkBatchID field in there and choose Records > Replace Field Contents from the menu bar. Choose the bottom option 'Replace with calculated result'.

                             

                            4. In the calculation area type "<mainTableName>::_pkBatchID". Of course use the name of the main table.

                             

                            5. Click 'Replace'.

                             

                            6. Check if the numbers are calculated as expected.

                             

                            7. Now go to the Relationship Graph, locate the relation and change it to the new fields.

                            1 of 1 people found this helpful
                            • 12. Re: Changing keys in relationships
                              Phil_Mac

                              hbrendel,

                               

                              That's it thanks so much,

                               

                               

                              Phillip

                              • 13. Re: Changing keys in relationships
                                beverly

                                I do this for such temporary keys. Pull through the existing relationship or use ExecuteSQL() in a script to "set" what you need.

                                 

                                Sent from miPhone