7 Replies Latest reply on Aug 29, 2011 3:08 PM by philmodjunk

    IF matching field THEN add increment (A,B,C etc)

    JantzSelk

      Title

      IF matching field THEN add increment (A,B,C etc)

      Post

      Hey everyone, 

      I've run into some problems with an auto-enter calculation field. The field, "AuditLotNum", acts as a unique identifier for each record. Don't worry it is not used as a primary key, it is strictly to help integrate the database into an existing indexing system. 

      Anyways, my problem is that i need a way to search through the existing "audit" records and if there is a duplicate "AuditLotNum" field i want it to apply this custom function http://www.briandunning.com/cf/718

      This is what i've come up with so far:

      Set Variable [ $AuditLot; Value:Audit Form::AuditLotNumber ]

      Enter Find Mode [ ]

      Set Field [ Audit Form::AuditLotNumber; $AuditLot ]

      Set Error Capture [ On ]

      Perform Find [ ]

      Set Variable [ $Error; Value:Get ( LastError ) ]

      If [ $Error = 401 ]

      Exit Script [ ]

      Else If [$Error = 0 ]........... <------ I think the CF needs to go here but i can't seem to get it to work 

      End If

      Any help is appreciated!

      EDIT: I should mention i tried the "replace field contents" script step after the Else If [$Error = 0 ], but the option to replace with calculation result was greyed out (probably because the "AuditLotNum" is defined as an auto-enter calculation)

       

      Cheers

        • 1. Re: IF matching field THEN add increment (A,B,C etc)
          philmodjunk

          I have more questions than answers.

          The find script as written will never produce an errror. Since you copy an existing value from the same field in the same table, there's always at least one record with this value already in existance. If the field is empty, you might get 400 for missing criteria, but not 401 for no records match.

          You'll need to first modify your find to distinguish between cases where it finds itself and cases where it finds other records with the same serial number.

          You might test with get ( FoundCount ) and you can sort on this field (It should be a text field) to find the latest value in the same series.

          Even then, you'll have trouble with a find for 10 pulling up records with 1001 unless you use leading zeroes to make all these the same number of digits (not counting an appended letter).

          Your find should also strip off any existing letter. If you have these values in your table: 1001A, 1001B, 1001C, you'd want to make sure that a value of 1001B produces 1001D, not a duplicate value of 1001C.

          • 2. Re: IF matching field THEN add increment (A,B,C etc)
            JantzSelk

            PhilModJunk,

            Thanks for your reply, i'm still new to using scripts and obviously got caught up in trying to deal with the found set so much i didn't quite realize that my find was off in the first place! 

            The "AuditLotNum" field format combines an abbreviation of the formula name along with the date prepared, for example hydroxylated toluene solution prepared today would look like "HTS082911". The AuditLotNum should always be 9 characters so if i understand correctly this format should eliminate the problem:  

            "Even then, you'll have trouble with a find for 10 pulling up records with 1001 unless you use leading zeroes to make all these the same number of digits (not counting an appended letter)."

             

            The purpose of this script is to eliminate the possibility of creating a record with a duplicate lot number. When the auto-enter calculation creates a LotNum, i was thinking that an "OnLayout Enter" script trigger could be used search for a LotNum that matches the current records LotNum. If it finds an existing record with a matching LotNum i want the CF to apply to the current record.

            Would Get (FoundCount) work something like this?

            Set Variable [ $AuditLot; Value:Audit Form::AuditLotNumber ]

            Enter Find Mode [ ]

            Set Field [ Audit Form::AuditLotNumber; $AuditLot ]

            Set Error Capture [ On ]

            Perform Find [ ]

            If [ Get (FoundCount) = 1]

            Exit Script []
             
            Else If [ Get (FoundCount) > 1]
             
            ....i don't know what to do here if there existing LotNum's like HTS082911A, HTS082911B etc. What steps would i need to take these LotNum's into account and so the next one created will be HTS082911C? You mentioned that the find should remove any existing letters, which i think could be done using something like:
             
            Let(Text ; Length(Text) - (Right(Text ; 1) =("ABC....."))
             
            But wouldn't this reset the CF back to creating HTS082911A instead of HTS082911C?
             
            This script is a lot of work for how little we acually run into this situation, but any help is appreciated!
             
             
            Cheers
            • 3. Re: IF matching field THEN add increment (A,B,C etc)
              philmodjunk

              There's a number of issues still to resolve. OnLayoutEnter doesn't seem like the right tirgger here as entering a layout has nothing to do with wheter a new record is created.

              Since the audit code combines an abbreviated chemical name and a date, this initial code can be auto-entered, but you can't have your script check for duplicates and increment the code until you've specified the chemical used in the abbreviation.

              You might want to select the chemical (or it's abbreviation) in a field with global storage, then click a button to kick off a script that creates the new record and then increments if the audit code is a duplicate. (The date can be auto-entered from the computer's clock or it can be also specified in a global field.)

              Now you have an audit code without any appended letters you can use as criteria to check for duplicates.

              I'd put the audit code and the incremented letter in separate fields, AuditCode, AuditLetter.

              Then the self join relationship: AuditForm::GlobalAuditCode = AuditFormSameCode::AuditCode

              can be used to check for duplicates. Define a sort order on this relationship that sorts by AuditLetter in descending order.

              Now your script would be:

              If [AuditFormSameCode::__pk_NumberField]
                 Set Variable [ $Letter ; Right ( Length ( CustomFunction ( AuditForm::GlobalAuditCode & AuditFormSameCode::AuditLetter ) - 9 ]
                 New Record/Request
                 Set Field [AuditForm::AuditCode ; AuditForm::GlobalAuditCode ]
                 Set Field [AuditForm::AuditLetter ; $Letter ]
              Else
                 New Record/Request
                 Set Field [AuditForm::AuditCode ; AuditForm::GlobalAuditCode ]
              End IF

              Note 1: I'm leaving it to you to decide how to put the correct combination of letters and digits in the global field :GlobalAuditCode before this scritp is run.

              Note 2: With some testing, I could probably get this to work as an auto-entered calculation.

              • 4. Re: IF matching field THEN add increment (A,B,C etc)
                LaRetta_1

                This script would need to fire upon new record creation (so turn off options to create records other than your script).  I also think you should open a new window to perform this find so you do not disturb your existing found set or sort order.  Try this:

                New Record/Request [ ]
                Commit Record
                Set Variable [ $AuditLot; Value: Left ( Audit Form::AuditLotNumber ; 9 ]
                New Window [ ]
                Enter Find Mode [ ]
                Set Field [ Audit Form::AuditLotNumber; $AuditLot ]
                Set Error Capture [ On ]
                Perform Find [ ]

                If [ Get (FoundCount) = 1]
                Close Window []
                Else
                UNSORT [ ] ... this is so we know the last record is the newly created record
                Go To Record/Request/Page [ Last ] ... go to this new record
                Set Field [ Audit Form::AuditLotNumber ;  this calc in blue ]
                  $lot & Let ( [
                  prior = GetNthRecord (  Projects::lots  ;  Get ( RecordNumber ) - 1 ) ;
                  letter = Right ( prior ; 1 ) ;
                  string = "abcdefghijklmnopqrstuvwxyz";
                  position = Position ( string  ; letter  ; 1 ; 1 )
                  ];
                  Middle ( string  ; position + 2 ; 1 )
                  )
                Close Window
                End If

                I can quickly put together a demo file if you wish.  I can't help but wonder how, if you have an auto-enter field for this number (and it increments) how you could ever get dups?  Anyway, no custom function required here.

                • 5. Re: IF matching field THEN add increment (A,B,C etc)
                  LaRetta_1

                  Actually here is a demo.  I was assuming the first original Lot would have an A.  If it does not have an A on the end then change the calc string from Middle ( string  ; position + 2 ; 1 ) to Middle ( string  ; position + 1 ; 1 )

                  I set the auto-enter serial back so it can create duplicates so just create new records and see what happens.  You will need to set the serial (in auto-enter serial) back to see the subsequent 'duplicate' records.

                  Demo available: http://www.4shared.com/file/qSr5QxAV/AssignAlpha.html

                  • 6. Re: IF matching field THEN add increment (A,B,C etc)
                    JantzSelk

                    PhilModJunk,

                    I have a button that goes to the audit form layout and creates a new record, unfortunately for me the OnLayout Enter is still not the correct way to go about doing this due to the fact that the script has to first go to the auditform layout THEN create a new record.

                    The calculation used to generate the "AuditLotNum" from the formula name is:

                    Let([

                    threeWords=LeftWords ( FormulaName ; 3 );

                    firstWord=LeftWords( threeWords ; 1);

                    secondWord=LeftWords ( RightWords (threeWords;2);1);

                    thirdWord=RightWords(threeWords;1);

                    letter1=Left(firstWord;1);

                    letter2=Left(secondWord;1);

                    letter3=Left(thirdWord;1);

                    abr=letter1&letter2&letter3

                    ];

                    Upper(abr& Substitute (( Right ( "0"  & Month ( DatePrepared ) ; 2 ) & Right ( "0" &  Day ( DatePrepared ) ; 2 ) & Right ( Year ( DatePrepared ) ; 2 )

                    ) ; "-" ; ""))

                    )

                    So the formula name would be Hydroxylated Toluene Solution, and the calculation abbreviates it into "HTS". 

                    "You might want to select the chemical (or it's abbreviation) in a field with global storage, then click a button to kick off a script that creates the new record and then increments if the audit code is a duplicate. (The date can be auto-entered from the computer's clock or it can be also specified in a global field.)"


                    Does the above calculation change the need for this? I don't want to create a new record if it finds a duplicate i just want it to add A,B,C etc to the current records "AuditLotNum" if there does happen to be a duplicate. Like is said before it is very rare that it would find a duplicate because the same exact formula would have to be made on the same day.


                    LaRetta,

                    Thanks for your post, unfortunately there is a little confusion. It's true that this "AuditLotNum" acts some what like a serial number (used for indexing in another system), however it is NOT an auto-enter serial number it is an auto-enter calculation. The reason for this is because we make a lot more compounds than just hydroxylated toluene solution so the formula name can change. Instead of HTS we could make methylated toluene solution (MTS), essentially there is no limit to the compounds we can make so the 3 letter abbreviation is always changing. 

                     

                    LaRetta & PhilModJunk,

                    Maybe a little more background will help shed some light on the process of creating audit forms. I work in a university chem lab, currently we have hundreds of compounds stored in binders. This is the first reason for the database, to store all of the compounds in a table called "compound formula". Each time we make one of these compounds we have to make an audit form, which is pretty much a duplicate of the compound formula but is individualized for each "batch" we make. So there is a button on the compound formula layout which goes to the audit form layout, creates a new record, and "copies" over all of the relevant information from the compound formula. So instructions, ingredients and amounts used are copied over to the audit form through "set variable/Set field" script steps. In addition an "AuditlotNum" is created for each new record. I don't know if this is helpful at all, but sometimes the big picture helps organize ones thoughts. 


                    • 7. Re: IF matching field THEN add increment (A,B,C etc)
                      philmodjunk

                      So there is a button on the compound formula layout which goes to the audit form layout, creates a new record, and "copies" over all of the relevant information from the compound formula. So instructions, ingredients and amounts used are copied over to the audit form through "set variable/Set field" script steps. In addition an "AuditlotNum" is created for each new record. I don't know if this is helpful at all, but sometimes the big picture helps organize ones thoughts.

                      This would have helped from the start. As I have said previously, OnLayoutEnter is not a trigger you want to use.

                      So there is a button on the compound formula layout which goes to the audit form layout, creates a new record, and

                      This is the button that should run the above script. The script would first use your formula to build the audit code and then use the method described to determine if this new record has the correct code or if it should also have a letter appended to it to make it unique from another batch of the same chemical created on the same date.

                      and "copies" over all of the relevant information from the compound formula. So instructions, ingredients and amounts used are copied over to the audit form through "set variable/Set field" script steps.

                      You should need to do this. This is much more complext than needed. The only data that should be copied over is any data that might be modified in some way from the original. Otherwise you should use a relational link to the formula and other data so that this data is stored in a single location not copied with each new record. In cases where you do need to copy over the data, just updating a key field with the correct ID code is all you'd need to do. Looked up value or auto-enter calculation field options would then copy the data for you without needing to add additional set field steps to your script.