7 Replies Latest reply on Dec 16, 2009 8:34 AM by philmodjunk

    How to automatically leave some fields blank.



      How to automatically leave some fields blank.


      Using Windows XP SP3 & FMP 10


      The database and application being built is NOT fully relational. Due to the small size of all data any possible reduction in speed as an overhead will not be noticed.


      As a result we have all policy records held in one policy table.  As such, if it is a car some fields must be completed e.g Breakdown Cover must be Yes or No, but if it is a dental policy then those fields can be left blank. 


      I can see that at a field level on the table it can be stipulated/set up that the field is a required field, but then how do I allow these to be blank if the policy type does not require these to be filled.  I don't want the User to have to click on the dialog box to confirm that the field can be left blank.


      Or, do I have to set a default value e.g. NO?

        • 1. Re: How to automatically leave some fields blank.

          Just because you store different policy types in the same table and thus leave a few fields blank in each record doesn't mean your database isn't fully relational. :smileywink:


          You should be able to set up validation messages that only pop-up if a field required for a specific policy type is left blank.


          Example: Let's say you have a field for a car's VIN number. That's a required field only if the Policy field = "Automobile".


          In your validation by calculation, use this expression:


          Not IsEmpty(self) And Policy = "Automobile"


          Clear the "evalute only if field is modified" option. Now you can enter a validation message: "A VIN number is required for all automobile policies."


          If you enter automobile, leave VIN blank and commit the record, you'll get the above validation message. Make sense?


          Sometimes validation settings just get too clunky. Another option is to write your own error checking code and set it to be triggered when the record is committed.

          • 2. Re: How to automatically leave some fields blank.
               I'll give your code a bash in a few minutes. I prefer to have the fields validated as the User enters the enter per field. Leaving it until the record is committed seems to messy because if several fields are invalid teh user will be looped around message displayed, returning to relevant field, recommiting record, so checking again and around we go. If that makes sense.
            • 3. Re: How to automatically leave some fields blank.
                 With a script set to perform when the record commits, you can exit the script on the first error after placing the cursor in the problem field. That way the user only deals with one error at a tiem. You can also use custom formatting to draw attention to empty required fields to help the user avoid leaving the field empty in the first place.
              • 4. Re: How to automatically leave some fields blank.

                That works fine, thanks. 


                Have set Cover_type field up as a dropdown value list so values can be selected, but how do I force User input to be in caps? i.e. if a value on the value list is "TP" or "TPF" but User keys "tp" of "tpf" I want the validation to convert all to caps

                • 5. Re: How to automatically leave some fields blank.

                  You can't use the validation, but you can use an auto-entered calculation. Even simpler, just format the field to be Upper case. Select the field in layout mode and use Format text...


                  If you want to actually convert the data to all caps, use this auto-entered calculation:




                  Clear the "Do not replace existing value..." check box also.

                  • 6. Re: How to automatically leave some fields blank.

                    I believe you could also use a script triggered OnObjectExit to make sure errors are reported one at a time?



                    • 7. Re: How to automatically leave some fields blank.

                      That works also, though it's behavior will now be very similar to a validation setting so I would normally use this method only if I need do something that a validation rule with custom validation message can't handle. (The literal text validation message sometimes just doesn't cut it.)


                      The only minor drawbacks to a separate trigger on each such field is that now you have more layout details to manage and your error checking is split up into multiple scripts which is sometimes a bit more trouble to manage than putting all the error checking code into a single script.