3 Replies Latest reply on Mar 16, 2011 8:42 AM by Frinholp

    Maintain Foreign key

    Abhaya

      Title

      Maintain Foreign key

      Post

      Hello guys

      Actually i'hv a simple doubt.

      There are 2 tables X  AND Y

      X has fields like pk_ID ,name,add'r

      Y has fields like pk_ID ,color.age and fk_ID(for X pk_ID)

      I want to  automatically  maintain the fk_ID in Y table whenever there is a record created in X table .

      No doubt in relationship i  checked the checkbox (allow creation.... for Y)

      Both pk_ID and fk_ID are  same type.

      It can be done manually  or by script that is copy from pk_ID from X and paste IN Y's fk_ID..

      Pls help i am a newbie...

      Thanks

      Abhaya

        • 1. Re: Maintain Foreign key
          Frinholp

          It's not too clear what is causing you the issue looking at your post bu I hope this helps

          Are you are creating your pk id's by script?

          If so, you should be using an auto-enter serial number instead. This will be assigned on record creation. You can set this for your pk in both tables within the field options. You should also tick the do not allow user modification checkbox for both your fk and pk fields to prevent the key accindently altered.

          It seems you have your relationships set correctly.

          Are you using fields from both tables on one layout? If so, and assuming the layout is based on the X table, when you modify a field that belongs to table Y using the relationship you defined earlier, a record will be created in table Y with the pk from table X copied into the fk field in table Y.

          Have you checked the allow creation of records on the correct side of the relationship?

          The side where the fk is highlighted should have the allow creation checkbox ticked.

          If you don't have the fields from both tables on one layout, you will then have to create the new record in table Y by copying the pk from table X into fk on table Y.

          Set Field [ Y::fk , X::pk ]

          Lee

          • 2. Re: Maintain Foreign key
            philmodjunk

            "Allow creation of records via this relationship" is primarily useful when you have a portal to the Y table on your X based layout. Then, typing data into the bottom blank portal row creates a new record in Y and copies the primary key from the current record in X into the foreign key field in this new record.

            If you use fields from Y on a layout based on X, you can only access the first such related record. When you first create a new record in X, there will be no related records (in most cases) in Y. The fields from Y will thus appear empty. When you enter data into any one of them, the same process kicks in and you get a new record in Y with the Primary Key value automatically entered into the Foreign Key field of the new Y record.

            In all other situations, you'll need either a script or an auto-enter calculation to automate entering the desired value. Sometimes you can load the current Primary Key into a global field or global variable and then define an auto-enter calculation to copy this value into any new records created.

            • 3. Re: Maintain Foreign key
              Frinholp

              Thanks Phil for elaborating.

              Sometimes you can load the current Primary Key into a global field or  global variable and then define an auto-enter calculation to copy this  value into any new records created.

              A technique I have not thought of; most certainly will be used in the future.

              Lee