11 Replies Latest reply on Apr 27, 2010 8:45 AM by comment_1

    Maintain field traits when 'setting variable'

    k1ngf1sher

      Title

      Maintain field traits when 'setting variable'

      Post

      Hi, I'm having trouble with the 'Set Variable' function.  I'm using a script to copy a field's data (an auto-serial field) and enter it into another field in another table using the 'Set Field' function.  The problem is that the initial field's structure is a five-digit value (auto-serial, beginning with 00001), but when the field's value is copied to the other table, the value is only reported as non-zero numbers: e.g., 00001 is reported as 1, 00109 is reported as 109.  How can I set the properties of the script so that the entire value gets copied from one table's field to another table's field?

       

      Thanks for your help.

      K

        • 1. Re: Maintain field traits when 'setting variable'
          comment_1

          The best thing to do would be to change (at least) your target field's type to Text.

           

          You could also change the Set Field[] step to Set Field [ YourField ; GetAsText ($yourVariable) ] - but the truth is that "00001" is NOT a number.

          • 2. Re: Maintain field traits when 'setting variable'
            k1ngf1sher

            Thanks for the response.  I'll try implementing your solution and get back to you with additional questions.

             

            I'm not certain whether I understand why 00001 is not a number; should I have started my numbering with 10000 rather than 00001 to keep my record IDs simple?

            • 3. Re: Maintain field traits when 'setting variable'
              comment_1

               


              k1ngf1sher wrote:

              why 00001 is not a number


               

              Because GetAsNumber ("00001" ) returns 1. Leading zeros are meaningless in a number, and discarded when text is converted to a number.

               


              • 4. Re: Maintain field traits when 'setting variable'
                k1ngf1sher

                Ok, I follow the logic.  One follow-up question: can a text field, such as the one you're suggesting, but auto-incremented by values of 1, such as in an auto-enter serial number?

                 

                Thanks for your insight.

                K

                • 5. Re: Maintain field traits when 'setting variable'
                  comment_1

                  Auto-increment works just as well within a text field. For example, setting a text field to increment "ABC0001" by 1 will generate:

                  ABC0001

                  ABC0002

                  ABC0003

                  ...

                  • 6. Re: Maintain field traits when 'setting variable'
                    k1ngf1sher

                    Ahh, I initially was going to implement the increment that you suggested (ABC00001), but I read in several other places in this forum that I should avoid mixing alphanumerics in auto-enter serial values.

                     

                    How much trouble would I be in if I switched all my current auto-enter serial numbers from 'number' format to 'text' format?  I do not summarize by these fields (obviously), so I cannot see immediately how it might negatively influence my database.  Any input would be greatly appreciated.

                     

                    K

                    • 7. Re: Maintain field traits when 'setting variable'
                      comment_1

                       


                      k1ngf1sher wrote:

                      How much trouble would I be in if I switched all my current auto-enter serial numbers from 'number' format to 'text' format?


                       

                       

                      That's hard to say without knowing everything about your solution. For one thing, text fields sort differently. It is also not obvious to me why you would not summarize by these fields (or why that would make a difference).

                      My own personal preference is to use pure numbers as the unique ID's of a table (and as matchfields for relationships). If a formatted display is required, you can use a calculation field (with a Text result) =

                      SerialIncrement ( "YourPrefix" ; Serial ID )


                      But it is only a preference and there are exceptions: for instance, if data is coming in from various sources, a prefixed serial number can be useful.




                      • 8. Re: Maintain field traits when 'setting variable'
                        k1ngf1sher

                        I use the auto-enter serial numbers as unique IDs in each table (n=20) in my database.  I cannot see why I would summarize by these IDs, other than a total number of records in a search.  Can you tell me how else I might use them (without knowing everything about my database, as you said in your last reply)?

                         

                        I'm curious, how would a text field with values such as 00001, etc. be differently sorted than a number field with values 00001, etc.?  I know Windows Explorer doesn't how to sort files whose names include numbers, but does FM have similar problems?

                        • 9. Re: Maintain field traits when 'setting variable'
                          comment_1

                          If your primary keys are of type Number, then so should be your foreign keys (and vice versa). I often use a foreign key as the breakfield in a summary - but again, I don't see why the type should make a difference here.

                           

                          A text field with values "00001",  "00002", etc., will be sorted in the same order as a number field with values 1, 2, etc. - up to "99999". After that there will be a difference, since text is sorted alphabetically ( "12" < "9" ).

                          • 10. Re: Maintain field traits when 'setting variable'
                            k1ngf1sher

                            Righto!

                             

                            I use the my "ID" fields as my primary keys, and at this point, all are in number format.  I'll probably leave them in this format because the sorting problem associated with numbers when identified as text will not work for my database solution.

                             

                            I wanted to return to my initial issue now that we've largely coverted the 'why' part of the format problem.  My issue was that I cannot consistently have my values (e.g., 00001, etc.) returned in the same 'format' between different tables.  That is, on one table, the value will display as 00001, and when copied from one table into another (e.g., Table1::'Set Field' -> Table 2::'Set Variable'), the format changes (00001 -> 1).  Should I include the functions you initially suggested in an earlier comment?  If you have any insights into why this might be happening, I would love to hear it!

                             

                            Thanks for all your help,

                            K

                            • 11. Re: Maintain field traits when 'setting variable'
                              comment_1

                              I am afraid you may still be missing the main point here:

                               

                              Storing a TEXT value such as "ABC123" or "00001" in a Number field is asking for trouble. Filemaker is pretty tolerant when it comes to data types, so it will not object or modify the stored value. However, it WILL treat the entry as a number - usually, when you expect it the least.

                               

                              A few examples:

                              • The entry "ABC123" will match "XY123" and "00123" in a relationship;

                              • Max ( Related::Field ) will return 11, when the entries are "A011" and "Z009";

                              • SetField [ Target ; Source ] will set Target (Number) to 123, when Source (also a Number) contains "ABC123".