8 Replies Latest reply on Jul 6, 2017 8:51 AM by julianavivacqua

    Doubt about exporting a file to excel

    julianavivacqua

      Hi People! I’m a beginner user at this software and I’d like to know if someone could help me  J I get this doc at filemaker and I’d like to make an excel file in which I could filter the results if there were more than one ticked per person. The words don’t come separated by any character that I can separate by columns at Excel, so I don’t know if I can resolve this at Filemaker. Does anyone know that?

        • 1. Re: Doubt about exporting a file to excel
          TomHays

          It looks like you have a field in FileMaker that has a control style of Checkbox.

          When you check more than one box, the values are put on more than one line in FileMaker.

          [ ] Estudante

          [ ] Profissional

          [ ] Arquitetura

          [x] Design de Interiores

          [x] Paisagismo

          [x] Engenharia

           

          is stored as:

          Design de Interiores

          Paisagismo

          Engenharia

           

          inside  FileMaker with each checkbox value on a separate line.

           

          When you export a field that has more than one line such as this checkbox field, it looks like it goes into Excel with the line breaks removed:

          Design de InterioresPaisagismoEngenharia

           

           

          "I’d like to make an excel file in which I could filter the results if there were more than one ticked per person."

           

          This task can be done easily within FileMaker.  Excel is not needed.

           

          Add a new Calculation field to the table in FileMaker with the following definition:

          ValueCount(Atividade)

           

          Have it return a calculation result of Number.

           

          Put this new field onto a layout.

           

          This new field will tell you how many checkboxes were selected.

           

          You can do a Find on this new field ">1" to locate records where more than one box was checked.

          Or you can export this field to Excel along with the other fields and use that for your filter.

           

           

          -Tom

          • 2. Re: Doubt about exporting a file to excel
            TomHays

            julianavivacqua wrote:

             

            The words don’t come separated by any character that I can separate by columns at Excel, so I don’t know if I can resolve this at Filemaker.

             

            Alternative to counting the checkboxes in FileMaker is to calculate a copy of the checkbox field in FileMaker that does have a more convenient separator between values.

             

            Create a new Calculation field that returns a result of Text with the following definition:

             

            Substitute(Atividade; "¶"; "|")

             

            This will turn

             

            Design de Interiores

            Paisagismo

            Engenharia

             

            into

             

            Design de Interiores|Paisagismo|Engenharia

             

            You could export this field without having to deal with the line break characters.

             

            -Tom

            • 3. Re: Doubt about exporting a file to excel
              julianavivacqua

              I understood your suggestion, but I'd like to identify how many people are students and how many are designers, for example. So, I don't need to know how many checkboxes are selected, but what are selected. If I could do that, I wouldn't need to export any data anymore.

              • 4. Re: Doubt about exporting a file to excel
                beverly

                Are you wanting a separate column for each "check" or something else in Excel?

                As Tom explained so well, the checkbox is just return-delimited.

                 

                Beverly

                • 5. Re: Doubt about exporting a file to excel
                  TomHays

                  It sounds like this can be done with a Find in FileMaker.

                   

                  To find records with the "Arquitetura" checkbox checked:

                  Find button.

                  Click the "Arquitetura" checkbox.

                  Click Perform Find.

                   

                  Look at how many records are found.

                   

                   

                  To find records with that match either "Estudante" or "Design de Interiores":

                   

                  Find button

                  Click the "Estudante" checkbox.

                  New Request button.

                  Click the "Design de Interiores" checkbox.

                  Click Perform Find.

                   

                  Now the Found Set will have records that show any combination of "Estudante" and "Design de Interiores".

                   

                  Each "New Request" in a Find will provide a different reason for the record to appear in the Found Set of results.

                   

                  You can add as many New Requests as you need to assemble your search.

                   

                  The only trouble with using this technique of searching on checkboxes comes when you have checkboxes defined with a common word within a phrase.

                  [ ] Admiral

                  [ ] Vice Admiral

                  [ ] Rear Admiral

                  [ ] Commodore

                   

                  Searching for

                  [x] Admiral

                   

                  will also bring up records with

                  [x] Vice Admiral

                  or

                  [x] Rear Admiral

                   

                  since "Admiral" appears in those too.

                   

                  -Tom

                  • 6. Re: Doubt about exporting a file to excel
                    julianavivacqua

                    I want to separate in Excel because I do not know how to count each type of "check" in FileMaker. It's to generate statistical data.

                    • 7. Re: Doubt about exporting a file to excel
                      TomHays

                      If you want to create new Calculated fields in FileMaker that analyze the checkbox field, you can define them like:

                       

                       

                      Case(

                      IsEmpty(FilterValues(Atividade; "Estudante¶Design de Interiores"); 0;

                      1

                      )

                       

                      This will return 1 when Altividade has "Estudante" checked or "Design de Interiores" checked.

                       

                      To modify this for your choice of values, put them all inside the second quoted argument and separate each one with ¶.  You can put in as many values inside the quotes as you want for your analysis.

                       

                      -Tom

                      • 8. Re: Doubt about exporting a file to excel
                        julianavivacqua

                        I like that idea, I think it cab be solve my question imediatly. But do you know how can I do this count on a script or a calculated field? On my mind, the idea is have that information always uptated.