4 Replies Latest reply on Feb 26, 2013 11:26 AM by willrollo

    importing field contents on a calculated field

      Title

      importing field contents on a calculated field

      Post

            I have a calculated field on my customers table. This field, Account_number is created when a customer places an order. It is linked to an accounts table that creates the account pk number and recieves the customer fk when a account record is created.

           The account_number field in my customers table provides a unique account number that comprises the last 3 digits of the customer id number and the first three letters of the surname. 

           However, I am importing some old customer records which already have their account numbers calculated - but the account number on these imported records is calculated in a different way. 
           I have been able to import these records and into my new customers table by turning off the calculated field to just an edit field. But when I turn it back on to calculated, for new customers, it will alter all the other imported account number fields, How do I get around this.??

            

        • 1. Re: importing field contents on a calculated field
          philmodjunk

               Perhaps this should be a text field with an auto-entered calculation. That will allow you to import data into the field and yet new records will compute the expected value.

               But

               

                    account_number field in my customers table provides a unique account number that comprises the last 3 digits of the customer id number and the first three letters of the surname

               is not guaranteed to be unique and I personnally would not use such a field as a match field in relationships.

               A straight serial number is much simpler and far safer.

          • 2. Re: importing field contents on a calculated field

                 I do have unique number only serials ineachtable - this account number format is purely because of the imported data having a similar format...

                 I did try auto enter calc, but it doesnt seem to create the account number when I run the script...

            • 3. Re: importing field contents on a calculated field
              philmodjunk

                   Your scritp may have to set the field instead of relying on the auto-enter calc. Auto-enter calcualtions do not automatically update when you modify a field that they reference that comes from a different table or record.

                   You may also want to not import the data into this field. Import it into a separate text field and use a calculation in a script or Replace Field Contents to load the fields that the calcluation uses to produce this value.

              • 4. Re: importing field contents on a calculated field

                     Ah I see......I had thought of your second suggestion, but wasnt sure if there was another way...

                     thank you