8 Replies Latest reply on Jan 7, 2010 1:35 PM by bestknownmethod

    Script to Serialize a Field

    bestknownmethod

      Title

      Script to Serialize a Field

      Post

      FM Experts, I have a database where initially I have a group of individuals who will create a new record from a form which will set the record ID to "New Item".

       

      From there the next step is for the Asset Managers to verify the data is correct and valid in the database.  If all is well, I want them to execute a script to serialize the record (Basically change the field called "New Item" within the record to the Next Serial Number) and make it a valid unique entry within the database. 

       

      Ex: ASSET-00000001 is the first record, so if they seralized the next "New Item", set it to ASSET-00000002

       

      I wanted to see what would be the best way to setup a script.  Been difficult finding the correct commands.

       

      Thanks!

        • 1. Re: Script to Serialize a Field
          ninja
            

          Howdy,

           

          I figure the best way to do this is to autoenter a serial# and not let humans play with your recordID's.  Have the fields as you describe them, but they should not be your keys for relationships.

           

          For what you're trying to do, assuming they won't be your keys, you could:

          set up a self-join with the [x] operator in the relationship (all records related to all records)

          Create a "Minimum" field, calculation of Min(jointable::recordID)

          and have your script simply be:

          If [recordID = "New Item"]

             Setfield [recordID ; Minimum - 1]

          Endif

           

          Note: I'm using Minimum rather than Maximum since you have a dash "-" in your text which will be treated as a negative.

          Note: I'm inherently uncomfortable doing math on text...but it works.  You could simply have this be a number field instead if you're willing to do without the word "Asset" and leave the field blank for a new item.

          • 2. Re: Script to Serialize a Field
            bestknownmethod
              

            I understand to not mess with the initial ID number, but it's a requirement by the customer to provide checks and balances.

             

            I have run where if you click the APPROVE button it will REPLACE FIELD CONTENTS of the record but if there are more than 1 "New Item" records, it will serialize them for all which is not what I need.  I need to serialize that particular record and ensure it is unique so it is not duplicated.  I think I have most of it down, but I think I have an idea to fix this instead of generating a "New Item" as the ID, I can just set a different field called "Status" to "New Item" so the Asset Manager's can sort records in that context and once approved, then it will serialize the record.  Does that sound more efficient?

            • 3. Re: Script to Serialize a Field
              ninja
                

              It sounds like you got my point.  And how you proceed will of course be dictated (to a greater or lesser degree) by the customer with the cash.

               

              You can have an autoenter ID that isn't shown on the layout, and in addition have the fields you're talking about that are shown.  That gives you both worlds.

               

              I used SetField rather than Replace FieldContents...since you're on a single record it really makes no difference...as long as you're only doing it to one record.  SetField will only affect the current record no matter how many records have "New Item" as a value.

               

              As I think about it, you can do exactly what I understand that you want with the script above...just use the Autoentered ID for your link behind the scenes.  The customer gets the field(s) they want, and can serialize it as they wish for their checks and balances...but it won't affect any related records or relationships by fussing with the key fields (which is very good). 

               

              I think your question was answered...did I drop anything?

              • 4. Re: Script to Serialize a Field
                bestknownmethod
                  

                I almost have it.  Thanks a ton for your help so far.

                 

                When I use the 'Set Field' command, it does not change it for some reason or add the serialized text as you stated.  I wonder what I'm missing.  here is an example of the script:

                 

                Set Error Capture[On]

                Show Custom Dialog["Approve"; "Are you sure that the information below is correct?  Click 'Approve' to continue or click 'Cancel' if you made a mistake"]

                #1 = Cancel, 2 = Approve

                If [Get(LastMessageChoice)=2]

                     Set Field[AssetTable::AssetID[SerialIncrement("ASSET-00000001";1)]]

                     Set Field[AssetTable::Status[TextColor ("VALID"; RGB(0;0;255))]]

                Else

                     Reverse Record/Request[No Dialog]

                End If

                 

                It won't change the contents for some reason.

                • 5. Re: Script to Serialize a Field
                  bestknownmethod
                     You know I could just do the AUTO-ID like you said and change another field, but I need that "Status" field changed.   It's being a bum :)
                  • 6. Re: Script to Serialize a Field
                    ninja
                      

                    bestknownmethod wrote:

                       Set Field[AssetTable::AssetID ; [SerialIncrement("ASSET-00000001";1)]]  (note syntax change)

                      

                    It won't change the contents for some reason.


                    Well, that's not what I suggested....did you try what i posted up above?

                    Your approach actually seems cleaner than mine though...I'll mock it up tomorrow and give it a go.  I'm not used to SerialIncrement...let me learn it.

                     


                    bestknownmethod wrote:

                    If [.....]

                         #####

                    Else

                         Reverse Record/Request[No Dialog]

                    End If


                     

                    How about:

                    If [.....]

                       #####

                    Endif

                    Exit Script

                     

                    Probably just preference on this one ;) .....



                    • 7. Re: Script to Serialize a Field
                      bestknownmethod
                        

                      Ah, I messed up on the "Target" field...it works like a charm now.

                       

                      Thanks for your help.

                      • 8. Re: Script to Serialize a Field
                        bestknownmethod
                          

                        Yea I haven't gotten the SerialIncrement command to work right so I did what you said, auto-serialize the record and not have it changed and just use the "Status" as a reference for NEW ITEMS and VALID records.  That was MUCH easier to do.  That works great.

                         

                        If you find something else that may work even better, please let me know.  Thanks again.