3 Replies Latest reply on Feb 8, 2012 3:27 PM by philmodjunk

    Copying info on a one off basis from a related table based on related ID

    MikeBradshaw

      Title

      Copying info on a one off basis from a related table based on related ID

      Post

      Apologies if this has been posted before and I have not found it. I am not clear which script function to use to make this an easy task. Here is what I am trying to achieve:

      I have a Table that stores customer address info at the generic account level - Company name, shipping address and billing address. I also have a contacts table which has the contact name, Account (looked up from the Account Table to make sure spelling is correct) and here I want to have the option to pull the address from the Account table or enter myself.

      A script runs which performs the lookup of the Account Name, and then sets a field within the Contacts table called "account_ID".

      I want to put a button next to the contact address fields which would then perform a script that would copy the fields from the related record in the Account Table to the Contacts table based upon the Account_ID.

      As I said, I am not sure on the best script function to use to perform this - is it a case of using temporary variables on the script that get the record content from the account table and then commit this to the Contact fields. If this is the way to go (which is how I started), I need to make sure that the correct account info is used as this may not be the last account showing.

      Any guidance on the way of doing this would be appreciated.

       

      thanks

      Mike

        • 1. Re: Copying info on a one off basis from a related table based on related ID
          philmodjunk

          The simplest method does not require a script. (Note, the simplest method is not always the best method.)

          Format your account_ID field in contacts with a drop down list or pop up menu with a 2 field value list. In field 1, specify the Account_ID, in Field 2 specify the account name. When you select an account name from this value list, the account id is entered linking that contact record to the selected account record.

          Then, for each data field, such as address fields where you want to copy the address but still have be edited to differ from the data in the account record, use the looked up value auto-enter setting to copy data from the corresponding field in the account table.

          • 2. Re: Copying info on a one off basis from a related table based on related ID
            MikeBradshaw

            Thanks Phil,

            The Account_ID was already a link between tables so this was already in place. I have used the lookup and it works fine. The only thing is it now enters the address by default rather than by choice. This is not a major issue as I will just put a button to clear the address contents if they are not required.

            Appreciate the help (again) :-)

            Mike

            • 3. Re: Copying info on a one off basis from a related table based on related ID
              philmodjunk

              You could make it by choice rather than default. (Didn't understand that was what you want.)

              Establish a new relationship based on a hidden field in your table. (Hidden=not on the layout.) Match it to the account ID field in the other table.

              Specify that your looked up value setting use this relationship in place of the original.

              Now a button can use set field to copy the account id value into the hidden field to trigger the lookup of related data "on demand".

              Note: to create more than one relationship between the same two tables like this, create a duplicate table occurrence of the related table and refer to it in the looked up value settings.