5 Replies Latest reply on Oct 10, 2013 6:20 AM by philmodjunk

    Finding Any Empty Field

    MatthewMackay

      Title

      Finding Any Empty Field

      Post

           Hi,

           I've been looking for an answer to this question but can't really find a definitive one. No field in my database can be left blank, I have 2 tables but quite a lot of fields in each table. Ideally I want a script to search every field in the database for an empty value, if it finds any, a custom dialogue box appears and directs the user to the blank field.

           Is there an easier way than than writing a long if statement containing every field in my database?

           Thanks

           Matthew

        • 1. Re: Finding Any Empty Field
          philmodjunk

               Open Manage | Database | Fields

               Double click each such field and set a validation field option to require that this field cannot be empty. This can be set up to prevent fields from being left empty in the first place which is much simpler than trying to search your entire table for every record where at least one field is empty.

               You can also set up a layout with conditional formats that fill empty fields with a contrasting color to draw attention to them.

               If you must search your database for an empty field--any field in the table, you'd use a script similar to this but with your names used instead of those in the script:

               Enter Find Mode []
               Set Field [YourTable::Field1 ; "=" ]
               New Record/Request
               Set Field [YourTable::Field2 ; "=" ]
               #Repeat this pattern for every field in your table that can't be allowed to be empty.

               Set error capture [on]
               Perform Find []
               If [ Get ( FoundCount ) // records with blank fields were found ]
                  Show Custom Dialog ["These records have at least one empty field that cannot be left empty. They are highlighted in Red." ]
               End IF

          • 2. Re: Finding Any Empty Field
            MatthewMackay

                 Thanks for the reply,

                 Is there a way to trigger the validation of fields? Like after exiting the last field on a layout?

                 The users of my solution are more likely to miss a field than enter the field and not enter any data.

                  

                 I've started working with the script you mentioned, it's almost working however the find appears to be searching all records, I need the script to only search the current record, is that possible?

            • 3. Re: Finding Any Empty Field
              philmodjunk

                   It does search all records. That's what you requested:

                   

                        Ideally I want a script to search every field in the database for an empty value,

                   A script that checks every field in the current record would be different. It is also may not be necessary.

                   

                        Is there a way to trigger the validation of fields? Like after exiting the last field on a layout?

                   OnObjectExit could be used to perform such a script, If you have set a validation option that requires that the field be not empty, this script can consist of a single script step: Commit Records/requests []

                   That will trigger the field validations for fields that can't be left empty.

              • 4. Re: Finding Any Empty Field
                MatthewMackay

                     Apologies, I shouldn't have said 'all fields in the database', I should have said all fields in the current record.

                     The script is currently triggered when the user tries to create a report from the current record, if all fields are filled in the report will be created, if not the script is stopped and all empty fields are highlighted in red and the user prompted to fill these.

                     If however the user creates a new record, but then goes back to a previous record to edit something and create the report again, the empty fields from the newest record are empty and therefore the script stops

                • 5. Re: Finding Any Empty Field
                  philmodjunk

                       "going back to the previous record" should trigger the validation options that, in turn should prevent the user from changing records as this should commit the new record.

                       Or is the user editing data in a portal?