14 Replies Latest reply on Jun 10, 2010 7:05 PM by eibcga

    Validation Field Options



      Validation Field Options


      I'm new FMP11 user on Mac OS X 10.6.3.


      There's a check box for Maximum number of characters in Field Options… Validation.  What if I want to set the Minimum number of characters?  I have a Date field as a number type rather than as date type, and I enter the date as YYYYMMDD.  I set the field option, Validation, to Maximum number of characters to eight (8), and would also like to set the Minimum number or characters to eight (8), but the option is not there.  Must I use an IF formula using the check box "Validate by calculation" and click the specify button, then create the appropriate formula, in this case?  Any ideas?



        • 1. Re: Validation Field Options


          eibcga wrote:

          Must I use an IF formula using the check box "Validate by calculation" and click the specify button, then create the appropriate formula, in this case? 

          Well, that's one way. In this case you could also validate by range, e.g. 10000101 .. 40001231 (assuming you have no dates earlier than Jan 1,1000).


          However, not all combinations of 8 digits are valid YYYYMMDD dates, so this is hardly a satisfactory solution no matter how it's implemented.


          • 2. Re: Validation Field Options

            What's the purpose in entering a date as a number in this format? (We might be able to suggest an alternative that enables better/easier validation if we knew that.)

            • 3. Re: Validation Field Options

              This question was also asked (and answered) in this thread: 



              validate by calculation


              Length ( Self ) = 8

              • 4. Re: Validation Field Options

                This question was also asked (and answered)


                Yes and no. If simply restricting input to a specified number of characters resulted in a valid entry everytime, then yes. As Comment has pointed out, however, it's possible to enter an 8 digit number that doesn't represent a valid date. 20100231   (Feb, 31, 2010) is one example of such an invalid input.


                THere are ways to get the specified format after first entering an actual date in a date field. If you need the actual data in this format, a calculation such as


                Year (date) and Right ( "0" & Month ( date ) ; 2 ) & Right ("0" & Day ( date ) ; 2 )


                will do the job or if you just need it for display purposes, you can simply format a date field with a custom format to display as YYYYMMDD.


                Either way, you have the advantage of being able to rely on filemaker's built in date validation and a pop-up calendar control (if desired) to ensure that only valid dates are entered.

                • 5. Re: Validation Field Options

                  eibcga wrote:


                  Must I use an IF formula using the check box "Validate by calculation" and click the specify button, then create the appropriate formula, in this case?

                  This validation checks for the 8 digits and for a correct date in US format:


                  Length ( Self ) = 8 and IsValid ( GetAsDate ( Middle ( Self ; 5 ; 2 ) & "/" & Middle ( Self ; 7 ; 2 ) & "/" & Middle ( Self ; 1 ; 4 ) ) )


                  • 6. Re: Validation Field Options

                    Thanks very much.  When I originally exported my transactions from accounting software to a spreadsheet, then imported the spreadsheet into FMP, it messed up the date format during import (treated days as months, months as days, etc.).  At the time, I didn't know how to fix this, so I decided to make the dates into numbers as YYYYMMDD so it would sort, and I could also query easily.


                    It would be ideal to replace this Date field and use proper dates in the date format desired (I'm in Canada, which uses DD/MM/YYYY instead of the U.S. version of MM/DD/YYY).


                    I know how to create a new Field as date type, then make a formula to convert the YYYYMMDD date-number into a date format, but then I would want to remove the original date field since it would no longer have any reason for being.  The problem with that is, I don't know how to do that without messing up the formula that made the date.  Is there a way to convert the result of a formula so that it leaves the formula's result in the field, but removes the formula (like Copy, Paste Values, in Excel).




                    Yellowknife, Canada



                    • 7. Re: Validation Field Options

                      Try adding a Calculation field (with result type Date) first. Check the results, then change the type of field to Date.


                      • 8. Re: Validation Field Options

                        YAY!  Thank you, Comment, it works!

                        • 9. Re: Validation Field Options

                          Common, I'm trying to do a similar solution (that is, a kind of Copy, Paste Values situation, to another problem I have).  When I originally created my database, I didn't import/do things correctly by using ID numbers for unique records.  Instead, I used the names of the ID numbers instead (so if I wanted to change the name later, it would not change all names that appear in the DB).  I have since added ID numbers for all unique records in all the tables.  But now I want to change all fields with names in them to their corresponding ID number (then delete the field with names in them, and add a field that shows the name as a related field).


                          I have a table that contains two fields: ACCT_NAME and ACCT_NO for Account Name, and Account Number, respectively.  I have been entering/importing data by entering the ACCT_NAME rather than the ACCT_NO.  The ACCT_NO field is accessing data from a related table (ACCOUNTS), based on the relationship and key fields I established.  I would like to convert the results of the ACCT_NO field so it stays there.


                          Here's what I did to try and do this:


                          I added a new Calculation, Number field called AC_NO (Account Number).  The formula is GetAsNumber(ACCOUNTS::ACC_NO), Calculation result is Number.  The AC_NO field is now showing the same data as is in the ACCT_NO field—good.  I then want to keep the results in AC_NO by changing the field type from Calculation to Number (kind of like Copy, Paste Values in Excel or Numbers).  But, when I did this, it changed all the results in AC_NO to blank—not good!  I tried variants of this by using lookups.  But lookup did not work either since it only seems to work when I add new records, not lookup for existing records.


                          I know I'm going a round-about way of fixing my DB, but I know I can fix it, but not sure how.  I'd rather do this way than having to export all the records, then re-import them the correct way.


                          Thank you!





                          • 10. Re: Validation Field Options

                            This method only works with stored calculations. A calculation that references a related field must be unstored.


                            You could do this: Show all records in the table, click in the new field and select Records > Replace Field Contents… > Replace with calculated result, then enter your formula.


                            Make sure you have a backup, because there's no undo on this.

                            • 11. Re: Validation Field Options

                              Perfect!  Thanks very much.

                              • 12. Re: Validation Field Options

                                Going one step further...


                                When I use my database, I only use Table View which has the following fields.  This is a partial list:





                                Acct_Name (related field)


                                The Acct_ID field Validation is set to require Member of value list, of which the source is From Field (use values from first field = Acct_ID, and also display values from second field = Acct_Name, showing values only from second field).


                                In Form View, I know there is a trick so that one can have the Acct_ID not appear by changing the fonts of this field to white (and on a white background), then dragging the Acct_Name field over the Acct_ID field.  This gives the illusion that, while only the values from the first field (Acct_ID) will be stored or applied, it displays the contents of the Acct_Name field.  Can one also do the same effect for Table View?  If so, then I will not need to have both the Acct_ID and Acct_Name fields appearing in Table View, which is what I'm trying to accomplish.


                                Any ideas?


                                Thanks again for all your help.



                                • 13. Re: Validation Field Options

                                  You cannot layer fields in Table view. However, you could format the Acct_ID field as a pop-up menu, and set the value list to "Show values only from second field"  (with Acct_Name being the second field).


                                  Note that this requires Acct_Name to be unique in the Accounts table.

                                  • 14. Re: Validation Field Options

                                    Thank you!  That changing the control style of this field in Layout Mode from drop down list to pop-up menu did the trick.