1 2 Previous Next 17 Replies Latest reply on Nov 1, 2012 9:38 PM by LaRetta

    Data Field Level Validation; lengths, codes etc.

    johnenyedy

      I am rather new to FM, coming from FoxPro and there are several data level things that I am trying to understand. I am working on an HR system aimed at PC and Mac with a few hundred fields.

      There are a number of these fields that are used for codes that currently are limited to a few characters and quite a few that are T or F.

       

      I am concerned about performance and unforseen consequences.

      1. When should I limit the length of a text field? [ I know how to do this via Database field Options Validation --> Left( Trim( <fieldname> ); number ) ]
      2. When should I limit the length of a numeric field?
      3. Is there any reason NOT to limit a code field to a just few characters when it's value comes from a Drop-down List? [ again using Validation, forcing a List Value ]
      4. For T-F fields I am currently using a Yes-No List as the apparently easiest solution. Is there a better way? I know I could store it as a number 1 or 0 but then I need to deal with all the displays of Yes-No and I am not sure how to best do that.
      5. Are there any other field level things I should be concerened with.

       

      I have read a book, watched hours of videos and searched forums. I have learned "How To" do the above but not the "Why" or "Why Not". It seems like the "Whys" have illuded me.

       

      Thanks in advance for your thoughts.

        • 1. Re: Data Field Level Validation; lengths, codes etc.
          comment

          To questions 1 to 3: the question is not when. but why. If it's important for your data integrity that values be restricted, validate - otherwise don't bug your users unnecessarily.

           

          To question 4: It is indeed best to use a Number field. Select the field in Layout mode and format it to display as Boolean (using the Inspector). Another option is to format the field as checkbox using a custom value list of "1".

           

          Message was edited by: Michael Horak

          1 of 1 people found this helpful
          • 2. Re: Data Field Level Validation; lengths, codes etc.

            John Enyedy wrote:

             

            I am concerned about performance and unforseen consequences.

            I agree and restricting also requires resources and can slow it down.  I would only add that large text fields such as notes and comments are best placed in a secondary table which relates 1:1 (related to your main table's primary key).  ALL fields of a record must be pulled down from Server and those fields can really slow it down.  If related, they will not be served to User unless displayed.  In fact, any auxiliary fields can be placed in another table to lighten the load.  If a field is needed for lookups, relationships, filtering, reporting or if displayed frequently then best to stay in main table.

            1 of 1 people found this helpful
            • 3. Re: Data Field Level Validation; lengths, codes etc.
              johnenyedy

              Thank you for your quick response. Sounds like I do not need to go thru all the trouble and time to size my fields.

              Your suggestion for the boolean data is a great idea. I have messed around with it and it works for flags, but is there a way to provide a Yes-No dropdown for the user or do I need to use a check box? Can seem to find a way to do that.

              • 4. Re: Data Field Level Validation; lengths, codes etc.
                johnenyedy

                Thanks for the suggestion about creating 1:1 tables since we were planning to have it be a Multi-user application with the data on a server. So if I understand you correctly then what I should do after separating the notes into a related 1:1 table is put the Note display on a separate layout form or will a separate tab of the same form also work? I am asking with reguards to pulling the data from the server, not displaying the data.

                • 5. Re: Data Field Level Validation; lengths, codes etc.
                  comment

                  John Enyedy wrote:

                   

                  is there a way to provide a Yes-No dropdown for the user

                   

                  The way I usually do this is to make the field a button that does:

                   

                  Set Field [ SomeTable::StatusField ; not SomeTable::StatusField ]

                   

                  so that clicking it toggles it. The checkbox is also a good option, IMHO.

                  • 6. Re: Data Field Level Validation; lengths, codes etc.

                    When you want to display a YES, the question remains ... what is the User saying yes TO?  So you still have to provide a label or something which makes it clear.

                     

                    So if you label a value list Select with only a '1' then you can use it in multiple ways by attaching this same checkbox to any boolean field you need.  Then you can simply create a label next to the single checkbox, such as "Printed" or Shipped or Billed.

                    • 7. Re: Data Field Level Validation; lengths, codes etc.

                      John Enyedy wrote:

                       

                      So if I understand you correctly then what I should do after separating the notes into a related 1:1 table is put the Note display on a separate layout form or will a separate tab of the same form also work?

                      I've heard conflicting reports about what is downloaded from server on tabs.  I originally heard that if hidden behind tab that data did NOT download but it seems when 11 came out that (at DevCon) it was mentioned that it DOES download that data.  So I cannot say for sure and hopefully others can provide firm answer on it. 

                      • 8. Re: Data Field Level Validation; lengths, codes etc.
                        johnenyedy

                        Thanks again. The set field works very nicely.

                        • 9. Re: Data Field Level Validation; lengths, codes etc.
                          johnenyedy

                          Thanks Again. Another good way to go.

                          I have so much to learn.

                          • 10. Re: Data Field Level Validation; lengths, codes etc.
                            Malcolm

                            When you want to display a YES, the question remains ... what is the User saying yes TO?  So you still have to provide a label or something which makes it clear.

                             

                             

                            So if you label a value list Select with only a '1' then you can use it in multiple ways by attaching this same checkbox to any boolean field you need.  Then you can simply create a label next to the single checkbox, such as "Printed" or Shipped or Billed.

                             

                            An issue with boolean values and the checkbox method that LaRetta is suggesting is that unchecking the field leaves it empty. If you want to use boolean formatting the false value must be zero. You can handle this by adding an auto-enter calc to the field,  getasboolean( Self )

                             

                            Malcolm

                            • 11. Re: Data Field Level Validation; lengths, codes etc.
                              beverly

                              The formula that I have used (forever!) as a "toggle":

                               

                              ABS(myfield -1)
                              

                               

                              Empty (to start) toggles to 1

                              1 toggles to 0

                              0 toggles to 1

                               

                              Validate to be always be one of these values (can't accidently insert anything but a Boolean or empty).

                               

                              While "empty" isn't strictly Boolean, I might at times need a third "status", so don't always want auto-entered 1or 2.

                               

                               

                              -- sent from my iPhone4 --

                              Beverly Voth

                              --

                              • 12. Re: Data Field Level Validation; lengths, codes etc.
                                beverly

                                I mean don't always enter 1 or 0...

                                • 13. Re: Data Field Level Validation; lengths, codes etc.
                                  comment

                                  Beverly Voth wrote:

                                   

                                  The formula that I have used (forever!) as a "toggle":

                                   

                                  
                                  ABS(myfield -1)
                                  

                                   

                                   

                                  The formula  =

                                   

                                  not myfield

                                   

                                  will produce the same result with less operations and - IMHO - a lot more sense. After all, False is the negation of True and vice versa.

                                   

                                   

                                  I agree about 0 being non-essential as a rule. However, Malcolm is correct about forcing a zero in case you want to format the field to display as "No".

                                  • 14. Re: Data Field Level Validation; lengths, codes etc.
                                    beverly

                                    don't you just love that we can do so many things, so many different ways?!

                                    1 2 Previous Next