6 Replies Latest reply on Jun 27, 2013 11:23 AM by philmodjunk

    Find all non empty fields?

    JohnWK

      Title

      Find all non empty fields?

      Post

           Hi-

            

           Context: I'm pretty new to filemaker pro.  I've got about 2000 fields which will be either blank or contain a comment by the end of the year.  I'll need to regularly print out reports that contain all the comments made in those fields for each person in my database.  

           So my main problem is: how do I get filemaker to create a report that contains the name and contents of all the nonempty fields in a table.

            Multiple searches about selecting all non empty fields have turned up nothing, so I figured I could make a script do the following:

           select first field in a table.

           if it's not empty, print the field name in the report, then print the text from the field into the report.  

           if It's empty, ignore it.  

           Move on the to the next field. 

           Repeat for every field in the table.  

            

           I can do the select first field, then select next field, and I can figure out how to loop it i think, but I can't seem to find a way to check if the selected field is not empty, and if it's not, then print its name and its contents in a report.   Is that possible?  

            

        • 1. Re: Find all non empty fields?
          philmodjunk
               

                     I've got about 2000 fields

               Whoa! 2000 fields in ONE table?!!!!! surprise Sounds very likely that you have a need for a much different data model that doesn't try to pack so many fields into a single table. Each "comment" should be in a different record instead of a different table of the same table.

          • 2. Re: Find all non empty fields?
            JohnWK

                 The fields are all divided into different tables, so I've got between 200-300 in each table.   I've been trying to work out a different data model, but it really makes the most sense for each comment to be in an individual field, because it will be so much easier to input and then track later on.   '

                  

                 So is "print all non empty fields" not an option?  

                  

                  

            • 3. Re: Find all non empty fields?
              philmodjunk

                   Sorry, but I disagree, but note that is based solely on what little you have described here. If I knew more about what you are trying to accomplish, my opinion might be different. Each comment should be a separate record and that should not raise any major data entry issues. With a separate record for each comment, there wil be NO empty comment fields to find in the first place.

                   Producing a list of empty fields--though your description of that is still very vague here, IS possible. I just don't see the need for it and seems like helping you further into a mess best avoided.

              • 4. Re: Find all non empty fields?
                JohnWK

                     Maybe I'm confused about terminology here.  I thought records were for things like people.  I have ~30 records, each one is a profile of a person.  Then for each of those people, I was thinking I needed around 2000 fields for comments that pile up over the year.  The reason I wanted them all separate fields is because then they'd be labeled according to the specific thing the comment refers to - they're generally short comments, like 2-3 sentences, so having to label each comment as it's made increases the time it takes to make the comments substantially.  

                      

                     This way, I can create the fields once, and then create a script that pulls the non empty fields, sorts them by whatever criteria I decide, and prints them into reports to give to each person.  When the comments are made, the commenter just has to select the field- which will be grouped in the order the work is submitted, and the comment will be tied to the work it reviews.   

                      

                     I don't understand how I would use records for comments.  

                • 5. Re: Find all non empty fields?
                  JohnWK

                       Let me be a bit more specific:

                        

                       I'm managing a database for an academic course with roughly 30 students on it.  Each week, the students submit about 60 pieces of work.  Each one has a specific Area, chapter, section, and presentation name.  The work is commented on each week by the staff and each student generally has to make usually between 0 - 20 corrections.  These corrections are the comments I've been talking about- and each one needs to be linked to the presentation it refers to.

                       Ideally, that work is resubmitted and checked immediately, but because of the volume of work, often times people get behind and don't turn their corrections punctually.  So we need to have a complete record, for each student, of every correction that was required-  so we can review their whole body of work at the end of the year and ensure the corrections have been made.  

                       I was hoping to integrate these comments into our database, so I could do nice things like- easily create a weekly corrections report,  search for comments by area, chapter, section, or presentation - print out reports for what corrections have been made or are outstanding.  And what I laid out in the first post is how I imagined it working in filemaker pro.  

                  • 6. Re: Find all non empty fields?
                    philmodjunk

                         No you should not use "2000 fields for the comments that may pile up over the year"

                         What you describe would have many different tables, each divided into records. One for students where each record represents a given student, one for student works--where each record represents a different student work and another for submissions where each record is for one time that the student submitted a specific work for comment. Your comments may be part of submissions or it may be yet another table linked to submissions if there may be multiple comments that need to be recorded for a given submission.

                         Students-----<Submissions>-----StudentWorks
                                                       |
                                                       ^
                                                  Comments

                         Students::__pkStudentID = Submissions::_fkStudentID
                         SudentWorks::__StudentWorkID = Submissions::_fkStudentWorkID
                         Submissions::__pkSubmissionID = Comments::_fkSubmissionID

                         For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained