9 Replies Latest reply on May 7, 2013 11:13 AM by StephenWonfor

    Variables in List()?




      I have a homemade client database that has a table with 20,000 or so records and 200+ fields. I am trying to find out how many of the fields do not contain data.


      1. create a layout with all non-global and calculated fields on it.

      2. go from field to field and do an "InsertFromIndex" on each.

      3. Note fields where no data is present.

      4. Remove same.


      This would, I expect to take "x" hours.


      But, as a developer, I concluded there must be an more elegant and easier way. I thought I could use the FieldNames design function to create an abstracted bit of code that would loop through all the fields and use List() (via a cartesian join) to get the field contents, noting those where contents were empty.


      Needless to say I am at "3x" hours to date...


      Go to Layout [ “ComponetsViewer” (ComponentsFocal) ]

      Show All Records
      Set Variable [ $AllFields; Value:FieldNames ( Get ( FileName ); Get ( LayoutName )) ]

      Set Variable [ $AllFieldsCount; Value:ValueCount($Allfields) ]
      Set Variable [ $$Results; Value:"" ]
      Set Variable [ $Count; Value:$Count + 1 ]

      Set Variable [ $Field; Value:"componentsfocal|x|" & "::" & GetValue($AllFields; $Count) ] //error lies here???

      Set Variable [ $Contents; Value:List ( $Field) ] // the issue is here. hardcode works, variable fails
      Set Variable [ $ContentsCount; Value:ValueCount($Contents) ]

      Set Variable [ $$Results; Value;

      Let( [

      x = $ContentsCount;
      y = Case ( IsEmpty(x) or x = 0;0;x) ];

      Case( not IsEmpty($$Results); $$Results & "¶") & $Field & " - " & y

      Set Variable [ $Count; Value:$Count + 1 ]

      Exit Loop If [ $Count > $AllFieldsCount ]

      End Loop


      Does List() not like variables or is this a flaw ( #2,245 in a series) in my code. I find I get the same issue using CF's like FoundList(...Eilert Sundt) and UniqueValues(Jeroen Aarts then tweaked by Erik Wegweiser)






      "When you are courting a nice girl an hour seems like a second. When you sit on a red-hot cinder a second seems like an hour. That's relativity." ---- Albert Einstein

        • 1. Re: Variables in List()?

          Have you tried wrapping it in Evaluate() ?


          IE Evaluate ( "List(" & $field & ")" )


          Not sure if that would get it to work, my other suggestion would be to do a find, set field by calculation for "=", and see if it matches your total number of records, dropping that inside an iterated loop.



          show all

          set var $total = get(foundcount)

          set var $iteration = 1

          error capture on


             enter find mode

             set field by calculation based on iteration -> "="

             perform find

            if get(lasterrormessage) = 401

               do nothing here

            Else if get(foundcount) = $total

                Add to blank list

             End If

             set variable $iteration = $iteration + 1

             Exit loop if $iteration > number of fields in field list

          End Loop

          • 2. Re: Variables in List()?

            Seems like it would be much easier to use ExecuteSQL for this as it does not rely on the fields being on the layout or any graph clutter to make it work.


            Use the FileMaker_Fields metatable to get a list of all the fields, then do a SQL SELECT on each of the fields in a loop and when the result comes back empty you know the field is empty in all records

            • 3. Re: Variables in List()?

              I agree with Mike, with some variations


              Create a new field with all the fields that you want sitting on it.

              Create a looping script that uses Go to Next Field to control the interaction

              Set a temp variable to active field name.

              in each loop, enter find mode, set the search value to "*"  and "omit"

              If found count is zero (or test error status) append the temp variable to a global variable.


              result is a list of field names with no data.



              • 4. Re: Variables in List()?



                Another approach you could use is to set up a field ~c1 which is a calculation with the value of 1.  In the relationship graph create a relationship between two table occurences for the table you want to count and link them by the new field.  Simple self join that should show all records in the secondary table.


                Create a layout based on the first TO showing all of the fields you want to get counts on from the second TO.  This is probably the largest peice of work with 200 fields in your table.  Still a simple layout should be fairly quick to create.


                Then set up a script along the following lines:


                Set Variable [$fnamelst; Value:FieldName(Get(FileName); Get(LayoutName))] // gets the list of field names.

                Set Variable $lpend; ValueCount($fnamelst)


                  Exit Loop if [Let $Lnct = $Lcnt + 1; $Lcnt > $lpend)]

                  Set Variable [ $fldn; Value:Getvalue($fnamelst;$Lcnt)]  // gets the field name we want to evaluate

                  Set Variable [ $fcnt; Value:Evaluate("Count(" & $fldn & ")" )]  // use Evaluate to get the actual count

                  if [$fcnt = 0]

                    Set Variable [$$counts; Value: List($$counts; $fldn & "|" & $fcnt )]

                end if

                End Loop


                When the script ends you will have a global variable with a list of all fields that are empty.  If you want take the if out and you will have a list of all fields with the current count of values. 



                • 5. Re: Variables in List()?

                  Thanks to all who posted replies.  This is in FMP 11.  I guess the question I was trying to ask is  "Can I use variables in the LIST() function.?"

                  • 6. Re: Variables in List()?

                    Evaluate is a function in FM11, so what I posted should help you, have you tried it?


                    Evaluate ( "List(" & $field & ")" )

                    • 7. Re: Variables in List()?

                      In short yes.  If you want to append to the current list then include the variable name in the list. So if the variable name is $lstname then the list function script step could look like this:


                      Set Variable [ $lstnames; Value:List($lstnames;$some; $thing;$otherstuff )]

                      You can intermix fields,  other functions like count()  and put in text. 

                      By the way the script example I gave you will work in both  FileMaker 11 and 12.



                      • 8. Re: Variables in List()?

                        Hi Stephen,


                        I've missed seeing those random quote signatures of yours.  Nice to see you here again.


                        Here are a couple of thoughts:


                        1) Given that the context is v11, Mike's suggestion of using Evaluate seems very apt.


                        2) Also note that it is possible to use the List function on a variable, where the variable is used as a reference to a field name (as opposed to an individual value).


                           In order to do this, you need to first wrap the the variable with the GetField function, as so:


                              List( GetField( $myVariable ) )


                        A simple example should be attached to this post.


                        Very best,



                        • 9. Re: Variables in List()?



                          GetField was indeed the issue.  Thanks for that.  And thanks to all other posters as well.


                          Jamie Zawinski has said "Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."  In my case substitute($String;"regular expressions";"SQL")...


                          This code now works and is almost modular...


                          #Cheat 1: need a layout with fields requiring analysis
                          #Cheat 2: need a cartesian self join using |x| notation.
                          Go to Layout [ “ComponentsViewer” (ComponentsFocal) ]
                          Show All Records
                          Set Variable
                          [ $AllFields; Value:FieldNames ( Get ( FileName ); Get ( LayoutName )) ]

                          Set Variable [ $AllFieldsCount; Value:ValueCount($Allfields) ]
                          Set Variable [ $Table; Value:Get ( LayoutTableName ) ]
                          Set Variable [ $$Results; Value:"" ]
                          Set Variable [ $Count; Value:$Count + 1 ]

                               Set Variable [ $Field; Value:$Table & "|x|::" & GetValue($AllFields; $Count) ]

                               Set Variable [ $Contents; Value:List ( GetField($Field)) ]
                          Set Variable [ $ContentsCount; Value:ValueCount($Contents) ]

                               Set Variable [ $$Results; Value:Let( [ x = $ContentsCount;y = Case ( IsEmpty(x) or x = 0;0;x) ]; Case( not IsEmpty($$Results); $$Results & "¶") & $Field & " - " & y )]

                               Set Variable [ $Count; Value:$Count + 1 ]

                               Exit Loop If [ $Count > $AllFieldsCount ]

                          End Loop


                          ...now that produces this...now I can adjust to only report on the empties...


                          componentsfocal|x|::ComponentType - 26284

                          componentsfocal|x|::DisplayName - 20954

                          componentsfocal|x|::District - 976

                          componentsfocal|x|::Entity - 26669

                          componentsfocal|x|::Entity_EUBCode - 3349

                          componentsfocal|x|::EUBLineLineNo - 7613

                          componentsfocal|x|::FacilityAcreageOnt - 220

                          componentsfocal|x|::FacilityKMPostOnt - 220

                          componentsfocal|x|::FacilityLegalOnt - 440

                          componentsfocal|x|::FromFac - 6361

                          componentsfocal|x|::FromLegal - 7743

                          componentsfocal|x|::FromLSD - 6361

                          componentsfocal|x|::FromMer - 6361

                          componentsfocal|x|::FromRange - 6361

                          componentsfocal|x|::FromSection - 6361

                          componentsfocal|x|::FromTwp - 6361

                          componentsfocal|x|::id_component - 26684

                          componentsfocal|x|::LegalDocRefLicenseNo - 3047

                          componentsfocal|x|::License - 6407

                          componentsfocal|x|::ManDepAllowance - 130

                          componentsfocal|x|::ManLooplineAllowance - 130

                          componentsfocal|x|::ManPropertyClass - 130

                          componentsfocal|x|::Map_ID1 - 0

                          componentsfocal|x|::Map_ID2 - 0

                          componentsfocal|x|::Map_ID3 - 0





                          "In politics stupidity is not a handicap." --- Napoleon Bonaparte