8 Replies Latest reply on Jul 24, 2013 8:39 AM by Beowulf

    generate alphanumeric serial number and letter code in a specific order



      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.

        • 1. Re: generate alphanumeric serial number and letter code in a specific order

               Didn't you post this question once before?

               I recall seeing this question some time ago and posted an answer to it that should do the trick.

          • 2. Re: generate alphanumeric serial number and letter code in a specific order

                 Nope, was not me. This is my first post. Do you remember the title of this post? Might do the trick for me too.

                 Edit: I found the post but there the colour codes are assigned randomly, I need them in a specific order.

            unique id-code generator

            • 3. Re: generate alphanumeric serial number and letter code in a specific order

                   The method should still work. The randomization is produced by sorting on a random numbef in a field in the table. Just don't do that part and you get codes generated in sequence.

              • 4. Re: generate alphanumeric serial number and letter code in a specific order

                     Hi PhilModJunk

                     It's been a while I know, smiley but I didn't have time until now to work on the database. As I said I don't have any experience with FM or programming. In this post unique id-code generator you wrote that one has to generate a table of all possible color combinations and assigning a code from the next record in the table. But I don't know where to begin, sorry.

                     Where do I generate this table you are talking about? And this calculation would go with this table or in a new script? For example I would have a script for a new male/female and it would go like that:

                     Go to Layout[…]

                     Insert Text […]

                     Insert Calculated Result […]

                     then you would specify it:

                     Let ( [ N = SerialNumberField ;

                               D1 = Mod ( N ; 8 ) ;

                               D2 = Mod ( Div ( N ; 8 ) ; 8 ) ;

                               D3 = Mod ( Div ( Div ( N ; 8 ) ; 8 ) ; 8 ) ];

                               Choose ( D3 ; "White" ; "Yellow" ; "Orange" ; "Red" ; "Green" ; "Violet" ; "Noir" ) & "/" &

                               Choose ( D2 ; "White" ; "Yellow" ; "Orange" ; "Red" ; "Green" ; "Violet" ; "Noir") & "/" &

                               Choose ( D1 ; "White" ; "Yellow" ; "Orange" ; "Red" ; "Green" ; "Violet" ; "Noir" ))

                     And this SerialNumberField would be the table one generated?

                     Probably a stupid question, sorry, but I have really no experience with this. sad

                     Would you also have a suggestion for my Ring-number question?

                     Thanks a lot for your help anyway!, you where the only one that answered.

                • 5. Re: generate alphanumeric serial number and letter code in a specific order


                       I understand the numeration more or less, but I don't quite get how you're using it, i.e. when you need that number to be generated? Could you please explain the situation a bit more?

                       Here's how I understand it: you're ordering metal rings that come in batches of 100 pieces. Each has a number like N234001, N234002, N234003, etc. You give each bird a ring plus three more color rings which serve as an additional highly visible mark. (Or, maybe this is different: you have multiple metal rings with N234001 and give each bird three more color rings as an additional subnumber? This sounds less likely, so I assume the fist description is correct.)

                       You don't know which ring numbers you get; you just order then and receive one or more series of 100 rings. Maybe you enter the numbers of series into your database. Now you need to label birds. You add a new record in your birds table and -- what happens next? Do rings come in correct order, i.e. N234001, N234002 or you just pick a random ring and enter its number? Do you need the color code to follow the number (i.e. N234001 would get WWW) or you want the first ring you pick to be WWW, the next WWY, and so on?

                       By the way, note that with 3 rings of 8 colors you'll only get 512 possible color combinations, so you won't be able to give each of 1000 metal rings a unique color code. If you label 1000 birds, there will be like two copies of each combination.

                  • 6. Re: generate alphanumeric serial number and letter code in a specific order

                         Hi Mikhail Edoshin

                         Your first assumption is correct, every bird receives a metal ring and three colour rings.

                         It goes like this:

                         When I catch a bird which is unringed he receives a metal ring and the 3 colour rings. So I would like to have a database which gives me the new colour combination in order, e.g. the button "new male" and "new female" with a script for the new colour combination. Yes, the metal rings come in order, but as the metal ring is just abbreviated as M, the Nr. of the ring doesn't matter for the script. Best would be a script, which automatically adds the M to the colour code because the M never changes, e.g MVRN with VRN the next colour code in order.

                         Yes, I would like the colour code to follow the number but actually that doesn't matter. Because I have two separate fields for the Ringnr. and the colour code. First I need a script to assign me the next colour code and then I add the Ringnr. to it on the next field manually with a drop down list for the first three numbers, i.e. I only have to add the last three digits since the first three never change. I just thought that I would enter the series I get in a drop down list for the letter and the first three nr., e.g. N234. Seem to be the easiest way for me. What would also be good if there could be some kind of system to reuse a code again as birds might die. I don't know if that would be possible to do, kind of mark a bird as dead and therefore give the colour combination free. So that's what I wrote at the beginning, if that would also be possible. To have a list with all the colour combination and if you enter a colour code, it kind of get's ticked off (Yes/No, "Yes" for used code). Then the scripts just looks for the next code with "No" in order.

                         Since I already did fieldwork without the database I have to enter all the data. Is it possible to enter manually the colour combination and the system recognizes the codes and incorporates that in the list, so that these codes can't be used again?

                         I'm working only with a small amount of birds, so 500 combinations are enough. :)

                         Just ask again if something is not clear and thanks for any suggestion/help. laugh

                    • 7. Re: generate alphanumeric serial number and letter code in a specific order

                           I still don't fully get how this works, but anyway: if you simply need to assign color combinations in order, then you need to use numbers and simply encode them as color combinations. It's the same as if "WYORGBVN"  were digits "01234567". You need to start with 0; 0 will be WWW (000), 2 will be WWY (001), 8 will be WYW (010) and so on. The formula is similar to the above, but I'll rewrite it slightly to highlight the pattern:

                           Choose( Div( Mod( Serial; 8^3 ); 8^2 ); "W"; "Y"; "O"; "R"; "G"; "B"; "V"; "N" ) &
                           Choose( Div( Mod( Serial; 8^2 ); 8^1 ); "W"; "Y"; "O"; "R"; "G"; "B"; "V"; "N" ) &
                           Choose( Div( Mod( Serial; 8^1 ); 8^0 ); "W"; "Y"; "O"; "R"; "G"; "B"; "V"; "N" )

                           Do not work with codes themselves, work with numbers, these are much simpler to understand; once you get them right, you can encode them as color codes.

                           If you need to reuse color combinations, you need to treat them separately from birds. A bird record gets a color combination and keeps it until the record is active (the bird is alive). Once you deactivate the record, the combination becomes available. Make a separate table Color Combination with a single field: Code. Make it a numeric field and set it to enter serials starting with 0. In your Bird table add a numeric field Color Combination Code and link these two tables by these fields. Write a script to add a new bird record like that:

                           - Go to the color combinations table
                           - Enter find mode and set Bird::Status to "Inactive". Perform find.
                           - If you found some records, then you have inactive color combinations you can use.
                           - If you haven't found any records, all combinations are in use and you need to add a new one, so add a new record in this table. FileMaker will set the Code to the next number and when you'll encode it later you'll get a correct color code.
                           - Remember the code of the current record in a varaible (it will be either a new code or a code you're going to reuse).
                           - Go to the bird table, add a new record and set its code to the remembered value.

                           This schema assumes you never delete records in the Bird table; if you do, you'll need to modify the script that searches for available codes to find either inactive codes or codes that don't have related Bird record.

                      • 8. Re: generate alphanumeric serial number and letter code in a specific order

                             The generating of the colour combination worked so thanks a lot for the help!! It just took a while because I also wanted to understand what the script is actually doing. But I have two other questions regarding this topic now:

                             If I go beyond the 512 color combinations, (so the next newly created record gives me 512 for the SerialNumberField) it begins anew. But I would like to stop, so that there are no more records created accidentally. I tried with ranging the SerialNumberField from 0 to 511, but when I press the new record field it still generates a new record. How can I prevent that?

                             A friend of mine suggested a find function when actually creating a new record for a bird, so to make sure, that the code is not used twice. Therefore taking the next code in line from the ColourCombination table and see if it already exists in the Birds table. But the script doesn't work yet so there must be a mistake somewhere. Maybe you have an idea what is wrong?  Would it also be possible to somehow combine it with your'e perform find for the inactive status? So first the script looks for any inactive colour combinations and if none then it takes the next color combination in line in the Colourcombination table and also checks if it's already existing in the Birds table.

                             Here is a print screen of the script