5 Replies Latest reply on Oct 23, 2014 6:53 PM by philmodjunk

    Help with a Report

    jakes

      Title

      Help with a Report

      Post

      I have 2 tables.

      1)Table 1. There is a field in this table called "status"

      2)Table 2. This is a table used for reporting. I have  a global field where I want to list the different statuses that appear in table 1, field status.

      The field status in table 1 has about 10 different statuses.

      I want a user to be able to go to the reports table and select a report based on multiple statuses. 

      If I have a drop down list with the different statuses user can only select one status.

      I assume the best option would be to use a checkbox where user can select multiple statuses and then run the report based on this.

      How would I do a search in Table 1, based on all the values user selected in the checkbox field? The values get saves as a list with a return character separating the values.

      Or is there a better way of doing this?

      Thanks 

        • 1. Re: Help with a Report
          SteveMartino

          You have 10 statuses (statii?)  Does each record only have 1 status or can each record have multiple statuses?

          For example, would a report be something like, "show me all records that have Status 1, OR Status 2, OR Status 4", or "Show me all the records that have Status 1 AND Status 2 AND Status 3"? 

          • 2. Re: Help with a Report
            jakes

            Hi Steve,

            Thanks for your response. Each record can only have one statuses at a time.

            I think its AND.

            For example, if user selects statuses pending, active and cancelled.

            The report must find everything that is pending and everything that is active and everything that is cancelled.

            Manually it would be a add new find requests, I just don't know how to take the users selection from the checkboxes and convert it into different find requests.

            Thanks for your help

             

            • 3. Re: Help with a Report
              philmodjunk

              The global, check box formatted field could be a match field in a relationship to an occurrence of the other table. Then Go To Related Records could be used to pull up a found set of all the records with any of the selected status values.

              OR

              There's a looping script example in the following link that shows how to set up a script that loops through the values in the check box field in order to set up a find that will find any of the selected values in the global check box field:

              Scripted Find Examples

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Help with a Report
                jakes

                Thanks Phil,

                This seems to work to just find the checkboxes, I would also like to do the search based on additional find criterias such as date, so statuses selected plus the date.

                Based on this example

                Set Variable [$FruitList ; value: Globals::gFruitList ]
                Enter Find Mode []
                Loop
                   Set Variable [$K ; Value: $K + 1 ]
                   Set Field [YourTable::Fruit ; GetValue ( $FruitList ; $K) ]
                   Exit Loop If [ $K > valueCount ( $FruitList ) ]
                   New Record/Request
                End Loop
                Set Error Capture [on]
                Perform Find[]

                is the correct place to put additional set field for the date field after the Exit Loop If?

                 

                • 5. Re: Help with a Report
                  philmodjunk

                  You would put those set field steps inside the loop so that each find request is identical except for the specified check box value.