12 Replies Latest reply on Nov 30, 2016 3:05 PM by philmodjunk

    Go To Next Field

    gdaquino

      Hello there,

       

      I have a script that need to find all empty fields in a layout and save statistics in another layout.

       

      I decided to go for a script that loops inside each field of the layout and search empty records, then save the count and the activefieldname in another stats layout.

       

      The loop would work correctly except for the fact that when I perform the find it resets the active field to the first....

       

      Any idea?

       

       

       

        • 1. Re: Go To Next Field
          beverly

          are you trying to get the values for MANY records on "the layout"? remember that layout means one record in form or list view.

           

          for example:

          ValueCount ( FieldNames ( Get ( FileName) ; Get ( LayoutName ) ) )

          returns the number of fields on the current layout in the current file

          that would be the same number of fields for each record.

           

          beverly

          • 2. Re: Go To Next Field
            David Moyer

            Hi,

            just to add a bit more - Go to Next Field depends on your tab order.  So if there are fields left out the tab order, they won't be evaluated.

            • 3. Re: Go To Next Field
              gdaquino

              Well,

               

              I need to find EMPTY RECORDS for each FIELD.

               

              There are 2 things that are my script going wrong. If I enter in find mode the active field is reset.

               

              And inside the layout there are some tabs :/

               

              Script_Workspace__cognitivecf_database_.jpg

              • 4. Re: Go To Next Field
                David Moyer

                after testing a good bit - I came across a problem when Go to Next Field landed on a pop-up menu - even in table view.  Is that a possibility?

                • 5. Re: Go To Next Field
                  gdaquino

                  Ok I created a new layout and I go with go to object naming all field first

                  • 6. Re: Go To Next Field
                    David Moyer

                    please don't go the route of naming all of your objects.  Check into the function (mentioned above) FieldNames.  I think you can use that list, along with the Evaluate function to determine which fields are empty.

                    1 of 1 people found this helpful
                    • 7. Re: Go To Next Field
                      gdaquino

                      I don't have to find every field empty I have to count all of the records

                      with that field empty

                       

                      Il giovedì 17 novembre 2016, David Moyer <noreply@filemaker.com> ha scritto:

                       

                      Go To Next Field

                       

                      risposta da David Moyer

                      <https://community.filemaker.com/people/moyerdave?et=watches.email.thread>

                      su Discussions - Visualizza la discussione completa

                      <https://community.filemaker.com/message/619919?et=watches.email.thread#619919>

                       

                      • 8. Re: Go To Next Field
                        erolst

                        Consider using ExecuteSQL; it features some hidden goodies that would allow you to get a list of all fields of a certain type. (This page http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/ has all the details, courtesy of Andrew Duncan at Databuzz.) Saves you the check on field type inside the loop.

                         

                        Loop through that list and again use ExecuteSQL to get a count of records where the current field is NULL (in SQL parlance).

                         

                        Store each field name where that count is equal to the total record count in a variable.

                         

                        When that is done, switch to your log table and process the variable to create log records.

                         

                        This should be much more robust and, with all the checks performed in memory and only one layout change, faster than your existing script.

                        • 9. Re: Go To Next Field
                          gdaquino

                          Using executesql is for me adding extra complexity to the simplicity of filemaker speaking of layout and object control.

                           

                          I used the fieldnames value list instead of it.

                          • 10. Re: Go To Next Field
                            philmodjunk

                            Consider the following Non SQL approach:

                             

                            Use set variable and the FieldNames function to get a return separated list of fields. Use Get (LayoutTableName ) to get the name of the table occurrence that's the basis for your layout.

                             

                            Use a loop to loop through the list of field names in your variable.

                             

                            In the loop, use Set field by Name with the following steps to put find criteria into a different field from your list each time:

                            Enter Find Mode [ ] --> clear the pause check box

                            SetFieldByName ( Get ( LayoutTableName ) & "::" & GetValue ( $FieldList ; $K ) ; "=" ) ]

                            Perform Find []

                             

                            $K is a variable that you increment by 1 each time through your loop. $K > ValueCount ( $FieldList ) will tell you when to exit that loop.

                             

                            This avoids any reliance on which field has the focus, they simply have to be present on the current layout, while performing a find on each field in turn to find out how many records have that field empty.

                            • 11. Re: Go To Next Field
                              gdaquino

                              Hi there,

                               

                              I believe that the 'simple' approach can save time and money, so for sure sql is powerful, but for this scope is 'too much' and I've gone with the technique using the FieldNames and the Evaluate, instead of SQL:

                               

                              Enter Find Mode [ ] --> clear the pause check box

                              SetFieldByName ( Get ( LayoutTableName ) & "::" & GetValue ( $FieldName ; $K ) ; "=" ) ]

                               

                              SQL can do better with reports!

                               

                               

                              • 12. Re: Go To Next Field
                                philmodjunk

                                Actually, ExecuteSQL might be used in place of the fieldNames function to get a list of all fields in a table whether they are on the current layout or not. That would make this process more flexible as it could automatically adjust as fields might get added/removed from the table in the future.

                                1 of 1 people found this helpful