1 2 Previous Next 20 Replies Latest reply on Jan 26, 2011 8:16 AM by RossanoIennarelli

    primary key as a string

    RossanoIennarelli

      Title

      primary key as a string

      Post

      I want to use a 4/5 carachter string such primary key, this string go from AAAA to ZZZZ, this key must automatic increase

      for example:

      AAAA - AAAB - AAAC - ... - AAAZ - AABA - AABB - AABC - .... - ecc.

      is there a way to do this??

      thanks in advance

        • 1. Re: primary key as a string
          raybaudi

          ... and which will be the advantages of such primary key compared to the normal way to get a serial number ?
          ( considering that the primary key must be neither seen nor used by the user )

          • 2. Re: primary key as a string
            RossanoIennarelli

            yes I know this, but I'm making a db based on a pre-existent register, and the primary key in this register is that string.

            It represent a kind of serial referring to the work and there are no other possible primary key in that register

            I know that is'nt comparison with a simple serial number.

            • 3. Re: primary key as a string
              raybaudi

              Is it too difficult to add a real ( auto-enter serial number ) primary key to that DB ?

              Are there some relationships based on that string ?

              • 4. Re: primary key as a string
                RossanoIennarelli

                no no relationships, but I think I've found a solution :

                two table linked by the two IDs, in one the register, in the oder the string

                • 5. Re: primary key as a string
                  raybaudi

                  If there are not any relationships, you can simpler change the definition of that field from text to auto-enter serial, show all records, select that field in Browse Mode and do a substitution ( look at the Record Menu ).

                  • 6. Re: primary key as a string
                    Rajkumar

                    Hi dude,

                    u asked a very good question, keep asking such questions. your answer is here...

                    for ur primary key field's options->auto enter-> calculated value, and paste the code below :

                    if(get(recordnumber)=1;"AAAA";

                    if(code(right(GetNthRecord(   field name;       Get(RecordNumber) - 1   );1))<90;
                        Left(GetNthRecord(   field name;       Get(RecordNumber) - 1   );3) & char(code(right(GetNthRecord(   field name;       Get(RecordNumber) - 1   );1)) +1);
                        
                         if(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );3;1))<90;
                             Left(GetNthRecord(   field name;       Get(RecordNumber) - 1   );2)  &  char(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );3;1)) + 1 )         & "A";
                            
                              if(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );2;1))<90;
                                 Left(GetNthRecord(   field name;       Get(RecordNumber) - 1   );1)  &  char(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );2;1)) + 1)  & "AA";
                                
                                    if(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );1;1))<90;
                                          char(code(middle(GetNthRecord(   field name;       Get(RecordNumber) - 1   );1;1)) + 1)  & "AAA";
                                          "AAAA"                        
                                       )             
                                 )        
                            )
                        )
                    )

                    Note : In the above code, replace the field name by ur respective field name.

                    • 7. Re: primary key as a string
                      RossanoIennarelli

                      it works!! great!! thanks Raj kumar!! with this I've saved a lot of MB of memory and a lot of time!!

                      • 8. Re: primary key as a string
                        philmodjunk

                        Warning, the above method can in multi-user situations result in duplicate values should two users attempt to generate the next value at the same time. (That's one reason why raybaudi suggested that you don't use this field as your primary key.)

                        • 9. Re: primary key as a string
                          Abhaya

                          Hi,

                          you can add this script with the auto enter field with script trigger..

                          so just check

                          • Set Variable [ $x; Value:Get ( RecordNumber ) ]
                          • If [ $x=1 ]
                          • Set Variable [ $$i; Value:1 ]
                          • Set Variable [ $$j; Value:1 ]
                          • Set Variable [ $$k; Value:1 ]
                          • Set Variable [ $$l; Value:0 ]
                          • Set Variable [ $$str; Value:"AAA" ]
                          • End If
                          • Loop
                          • Loop
                          • Loop
                          • Exit Loop If [ $$l = 4 ]
                          • Set Field [ AccessLog::_kp_ID; Replace ( $$str ;Length ( "AAA" ) ;1 ; Char (  Code ( Middle ( "AAA";Length ( "AAA" ) ; 1 ) )+$$l ) ) ]
                          • Set Variable [ $$l; Value:$$l+1 ]
                          • Exit Script [ ]
                          • End Loop
                          • Exit Loop If [ $$k = 4 ]
                          • Set Variable [ $$str; Value:Replace ( $$str ;Length ("AAA")-1 ; 1 ; Char (  Code ( Middle ( "AAA" ;Length ( "AAA" ) ; 1 ) )+$$k ) ) // Middle ( "DCBA" ;  Length ("AAAA")-1 ; 1 ) ]
                          • Set Field [ AccessLog::_kp_ID; $$str ]
                          • Set Variable [ $$k; Value:$$k+1 ]
                          • Set Variable [ $$l; Value:1 ]
                          • Exit Script [ ]
                          • End Loop
                          • Exit Loop If [ $$j = 4 ]
                          • Set Variable [ $$str; Value:Replace ( "AAA";Length ("AAA")-2 ; 1 ; Char (  Code ( Middle ( "AAA" ;Length ( "AAA" ) ; 1 ) )+$$j ) ) // Middle ( "DCBA" ;  Length ("AAAA")-1 ; 1 ) ]
                          • Set Field [ AccessLog::_kp_ID; $$str ]
                          • Set Variable [ $$j; Value:$$j+1 ]
                          • Set Variable [ $$k; Value:1 ]
                          • Set Variable [ $$l; Value:1 ]
                          • Exit Script [ ]
                          • End Loop
                          • 10. Re: primary key as a string
                            RossanoIennarelli

                            I've already notice that Phil, but there aren't any problems because this application will be performed only on a computer one person per time, however I've set a id such primary key, but I need the same string field.

                            Raj can I make a ask to you? when I delete a record with the code AAAB and create a new record the code return AAAB

                            abhaya thanks for help but I think your script is almost the same as that of Raj

                            • 11. Re: primary key as a string
                              Abhaya

                              Hello Rossan,

                              No doubt your proble has been solved but this is a auto enter field so it should start with A

                              and another thing we couldn't create  the same or next value after delete a single value.(Means if i delete AAb then i can't create a new record with the deleted primary key)

                              so i did  ascript that start from A

                              then B,C.....................Z

                              then AA,AB...........infinite.


                              Set Variable [ $x; Value:Get ( RecordNumber ) ]
                              If [ $x=1 ]
                              Set Variable [ $$str; Value:"A" ]
                              Else
                              Set Variable [ $n; Value:Length ( $$str ) ]
                              Set Variable [ $i; Value:1 ]
                              Loop
                              Exit Loop If [ $i > $n ]
                              Set Variable [ $ch; Value:Middle ( $$str ; $n-$i+1 ; 1) ]
                              If [ $ch = "D" ]
                              If [ $i=$n ]
                              Set Variable [ $$str; Value:"" ]
                              Set Variable [ $j; Value:1 ]
                              Loop
                              Exit Loop If [ $j>$n+1 ]
                              Set Variable [ $$str; Value:$$str & "A" ]
                              Set Variable [ $j; Value:$j+1 ]
                              End Loop
                              Set Variable [ $i; Value:Length ( $$str )+1 ]
                              End If
                              Else
                              Set Variable [ $ch1; Value:Middle ( $$str ; $n-$i+1 ; 1) ]
                              Set Variable [ $ch1; Value:Char ( Code ( $ch1 )+1 ) ]
                              Set Variable [ $$str; Value:Middle ($$str ; 1 ; $n-$i ) & $ch1 ]
                              Set Variable [ $i; Value:Length ( $$str )+1 ]
                              Loop
                              Exit Loop If [ $i > $n ]
                              Set Variable [ $$str; Value:$$str & "A" ]
                              Set Variable [ $i; Value:$i+1 ]
                              End Loop
                              End If
                              Set Variable [ $i; Value:$i+1 ]
                              End Loop
                              End If
                              Set Field [ AccessLog::_kp_ID; $$str ]

                              Add it with script trigger.......

                              • 12. Re: primary key as a string
                                RossanoIennarelli

                                excuse but in what script trigger I must add this?

                                however It can't start from A because in the current register there are already records since AAAH, but this is simple to solve I think

                                • 13. Re: primary key as a string
                                  Abhaya

                                  Hi

                                  Actually it is for new data base which is ready to start means bef'r  1st record insertion.

                                  set field as on object enter trigger and this field is 1st in the layout.

                                  but it can't be start with a value like AAA,AAH ..........this .

                                  • 14. Re: primary key as a string
                                    RossanoIennarelli

                                    I tried this but it give me for the firts record A, but the others it give me 1,2,3,4,5,.....

                                    1 2 Previous Next