AnsweredAssumed Answered

generate alphanumeric serial number and letter code in a specific order

Question asked by Beowulf on Apr 11, 2013
Latest reply on Jul 24, 2013 by Beowulf


generate alphanumeric serial number and letter code in a specific order


     Hi everyone,

     I'm very new to FM 12, so please be patient with me. :)

     I'm a biologist and setting up a database for birds. Each bird gets a metal ring with a ring number consisting of one letter (which never changes) and 6 digits and a colour ring combination with the metal ring and 3 different colours (i.e. 4 letters).

     I have checked the forum and the knowledge database but haven't found this specific topic. What I would need is a script, which generates the ring number as a serial number and then assigns a colour code, which was not used before. Unfortunately there are some circumstances which complicate things a bit.

     Ring number:

     Ok, first for the ring numbers. For example they all start with the letter N and the 3 first digits specify a series, which runs from 001 to 999 and then a fresh series starts. The thing is that a series is divided into hundred packages, i.e. 10 parts. Let me give an example:

     Series N234 consist of packages N234001-100, N234101-200, N234201-300 and so forth until N234901-999 but when I order rings I might not need the whole series or someone else took already part of the series and therefore I might end up with N234001-100 and N234301-400. So I don't know how flexible a script of FM Pro is and if I could adapt it after it already generated some serial numbers. So for example I have first a script for 001-100 and then need to change it to 301-400.

     I also thought about a value list with a drop-down list for the series. But then it gets annoying to always click into the field to add the 3 remaining digits. Maybe there is a way to let the cursor stay in the field after I chose my series from the drop-down list. (Or a script, which enters the 3 remaining numbers?) If I would do it that way I also would need a script for error avoidance, which would tell me if I entered a ring number already in use.

     Colour code:

     There are 8 colours available plus the metal ring. All colour codes start with a metal ring and 3 colours. The colours are abbreviated with one letter, e.g. W=white. Therefore a colour code could look like that: MRYV, where M stands for metal. The metal ring always stays in the first position. The order is a bit tricky, it starts at the second and third position with one colour and the fourth going through all the colours according to a sequence, then the colour at the second position stays again and the colour at the third position changes to the next colour in the sequence and the fourth is going again through all the colours and so on. The sequence of the colour is W, Y, O, R, G, B, V, N.

     Example: MWWW, MWWY, MWWO, MWWR, MWWG, MWWB, MWWV, MWWN; the sequence is finished and starts over again with the next colour at third position

     MWYW, MWYY, MWYO, MWYR, MWYG, MWYB, MWYV, MWYN; sequence finished and starts over again

     MWOW, MWOY etc.

     The colour in the second position changes, if you have gone through all the colours at the third and fourth position, i.e. MWNN and then it starts fresh with MYWW.

     Maybe it’s too complicated to write a script for that. So the other solution I was thinking is a table with all the colour code combinations and kind of a tick field or Yes/No (the code being used or not). So if I enter a new ring number it should automatically select a colour code which is not ticked or set to Yes and when it takes this colour code it should also automatically set it to “Yes” or tick it.

     Hope, I explained it well. If you have any further questions or if something is not clear, please tell me and I will try again. :) Thanks a lot in advance for anyone trying to help me.