3 Replies Latest reply on May 20, 2014 2:19 PM by philmodjunk

    Modify Field Value Based on Duplicate

    ShaunHamid

      Title

      Modify Field Value Based on Duplicate

      Post

           Hi,

            

           I have a questions.  I currently have a field that concatenates a few fields to create a name field for file exports.  Is there a way to have it modify itself if the value in the field is already used?  Let me illustrate.  Say the main name of the record is "INT. Eastern Side of Grand Ballroom" for a show named Grand Manor.  The file field concatenates that to a shorter file friendly name "GM_EasternSideBallroom_v01" where v01 is the version number.  If somehow I had a value that duplicated that, would there be a way to flag that duplicate and possibly update the name to add a number or character to the name (for example "GM_EasternSideBallroom01_v01" so that there are no duplicate values?

           Thanks, and let me know if you need any clarifications,

           Shaun

        • 1. Re: Modify Field Value Based on Duplicate
          philmodjunk

               Define a self join that matches records on your current field.

               Yourtable::FilingName = YourTable 2::FilingName

               Define a Seq as a number field with this auto-enter calculation:

               Max ( YourTable 2::Seq ) + 1

               Clear both the "do not evaluate if all referenced fields are empty" and the "do not replace existing value..." check boxes

               Set up a new text field that combines a version sequence with the name produced by your calculation:

               FilingName & "v" & Right ( "0" & Seq ; 2 )

               Select a unique values validation field option for this new text field. (if two different users were to try to create a new record with this same name at the same time, duplicates could still be created.)

          • 2. Re: Modify Field Value Based on Duplicate
            ShaunHamid

                 Thanks Phil as always.  One quick question, say there are a string of ones that duplicate a value, would there be a way to have the self modifying name add the sequence number to the highest value.  Say there was FilingNamev02 (Using the above calc) and a FilingNamev03, the next one would then be FilingNamev04.

            • 3. Re: Modify Field Value Based on Duplicate
              philmodjunk

                   I'm not sure that I understand the question.

                   You appear to be asking for each matching value to be numbered sequentially and that's exactly what will happen with the Max function based auto-enter calculation.