5 Replies Latest reply on Mar 6, 2012 11:46 AM by philmodjunk

    Attempting to add a hyphen to an accession number

    BrendanHuggins

      Title

      Attempting to add a hyphen to an accession number

      Post

      Hi I am Currently trying to add a hyphen to my accession number script. The current script does not include a hyphen and I have hit a brick wall attempting to figure this out. I hope someone can help.

      The script currently reads:

      Set Field [new inventory::next accession number; GetasText(GetAsNumber(nex inventory::accesion number)+1)]

      I'm just not certain where or what command will achieve my desired result and I have tried a few.

      I am using FileMAker Pro 11. 

      Thanks for your help.

        • 1. Re: Attempting to add a hyphen to an accession number
          philmodjunk

          Your current script increments the value of next accession number by 1. Don't see any reason to use GetAsText or Getasnumber here, let alone nesting one inside the other. I think you'll get the same result if you remove them.

          How is that added hyphen supposed to work?

          Do you want to get values such as:

          100-1
          100-2
          100-3

          or something else?

          • 2. Re: Attempting to add a hyphen to an accession number
            BrendanHuggins

             Thanks Phil,

            Sorry for not making that clear the hyphen should work so that it wouls read 2012-0001, so that I have the year and what number plant it was, and the following should read 2012-0002, etc...

            Thanks for your advice with the Get as text and GetasNumber advice as well I will try that as well.

            • 3. Re: Attempting to add a hyphen to an accession number
              philmodjunk

              This can be scripted, but an added complication will occur if you are hosting the database over a network such that two users might be creating new records with these numbers at the same time. That can easily produce two records with the same accession number.

              I'd use a an auto entered serial number field to number each new record 1, 2, 3 and use a script to rest the next serial value at the start of each new year.

              Then, assuming you have  date field, this calculation will return your properly formatted accession number:

              Year ( datefield ) & "-" & Right ( "000" & SerialNumberField ; 4 )

              • 4. Re: Attempting to add a hyphen to an accession number
                BrendanHuggins

                 Phil-

                Thank-you for your help. I do have a date field but it is in the format of 3/6/2012, and all I want is for the year to be used. Is it possible for me to only use the year from this colum some how? I hope these questions do not seem overly ignorant, I'm a horticulturalist who was given this task rather than have a more computer literate person from outside the organization work on this. I've been using the FileMaker Training Series to get a basic understanding of the programing techniques, but I am realizing that this is a bit more complicated than that manual covers. Thanks again though.

                 Only one computer will be able to edit data in this database at a time. though so that should not be a problem.

                • 5. Re: Attempting to add a hyphen to an accession number
                  philmodjunk

                  Is it possible for me to only use the year from this colum some how?

                  Year ( yourdatefield )

                  does exactly that. It extracts just the year from the date stored in the field, but the field must be of type date--not text.