8 Replies Latest reply on Jun 5, 2012 6:18 AM by philmodjunk

    How to filter from a drop down list in a saved report?

    Jenny_1

      Title

      How to filter from a drop down list in a saved report?

      Post

      Good Afternoon All

      I have creted a "student" database in FP11 using the Student Record template (using Mac), I have also created some reports for easy access to information. 

      In one report I have used a field with a drop down list showing "current, recent & past" students as the "sub-summary sort", I would like to filter out the "past" student files for this report& I assume using a edit to the script?

      However as a beginner I am unsure of the best approach, can you help?

      Many thanks for your assistance

      Jenny.

        • 1. Re: How to filter from a drop down list in a saved report?
          philmodjunk

          Do you want all records except those with "past" in this field?

          If so, use this script:

          Go to layout [your report layout]
          Enter Find Mode [] // clear the pause check box
          Set field [Yourtable::YourDropDownField ; "past"]
          Omit record
          Set error capture [on]
          Perform Find[]
          Sort Records [restore ; no dialog ] //sort by your dropdownField so your sub summary parts will be visible

          Note, when in find mode, the Omit Record step produces an "omit request" just like clicking the omit button in the status area when you perform a manual find on your database.

          • 2. Re: How to filter from a drop down list in a saved report?
            Jenny_1

            Hi Phil

            Thank you for your quick response.

            Sorry, I am doing something wrong here!

            I have entered your script but can not get it to work?  I entered it via editing the existing script, deleting previous & adding your script.  In a saved copy of the database I edited the existing script by inserting yours as an extention of the existing.  Neither actions had an impact of the report view.

            When I entered the "Set field" instruction "past" appeared in brackets so instead of your [Yourtable::YourDropDownField ; "past"] I have [Yourtable::YourDropDownField ; ["past"]] - not sure if this is an issue?

            Also when I entered Sort Records to make the sub summary parts visible, do you mean when in Edit Script then Sort Records, the Specify sort order instruction selection is "Status" & then select Custom Order based on value list is selected?  If so this is what I have done.

            When I go into Find mode, the record options to search (Include or Omit) do not include the 'Status' values list selection (Current, Recent, Past), I can search via the other fields as the are visible & able to be written into but not the Status (sub summary) field. I do have the Sub summary field in the report, "Status" selected for Quick Find.

            I hope you can clear this up for me?

            Thanks again

            Jenny

            • 3. Re: How to filter from a drop down list in a saved report?
              philmodjunk

              When I entered the "Set field" instruction "past" appeared in brackets so instead of your [Yourtable::YourDropDownField ; "past"] I have [Yourtable::YourDropDownField ; ["past"]] - not sure if this is an issue?

              That's the problem.

              When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: How to filter from a drop down list in a saved report?
                Jenny_1

                Thanks Phil

                I now have the Set Field correct but is is still not taking out "Past" student entries, in the Report?

                When I entered Sort Records to make the sub summary parts visible, then Edit Script then Sort Records, the Perform without dialog is selected, then the Specify sort order instruction selection as the field "Status" & I then select the Custom Order based on value list but I can not see the value list items (Current, Recent & Past).  Is this correct?

                Sorry I am not sure where I am going wrong, can you help again?

                Jenny.

                • 5. Re: How to filter from a drop down list in a saved report?
                  Sorbsbuster

                  "...Custom Order based on value list but I can not see the value list items" - that is correct.  You just see the list of Value Lists to choose from.  You would choose 'Status'.

                  To de-bug: run the script and then from the Records menu choose 'Modify Last Find' and check to see if you have a single Omit Request with the work 'Past' in the status field.  If not, you haven't set the Set Field correctly.

                  • 6. Re: How to filter from a drop down list in a saved report?
                    philmodjunk

                    You can also temporarily remove the set error capture step to see if running this script pops up an error message such as "no records found" or "no criteria specified".

                    • 7. Re: How to filter from a drop down list in a saved report?
                      Jenny_1

                      Hi again

                      Thank you very much I have fixed the error I made & teh report is now correct.

                      Can I ask you in regard to a question in my May 31 post above, regarding "Finds" in this report - When I go into Find mode, the record options to search (Include or Omit) do not include the 'Status' values list selection (Current, Recent, Past), I can search via the other fields as the are visible & able to be written into but not the Status (sub summary) field. I do have the Sub summary field in the report, "Status" selected for Quick Find.

                      I have noticed in other Sub-summary reports I have created that the Sub-summary field does not allow for selection in a find (ie the field does not allow you to type in a request) but reports I have created which do not have a sub-summary criteria allow entry into all the reports fields, can you explain this to me please?

                      Thanks again for all your assistance.

                      Jenny

                      • 8. Re: How to filter from a drop down list in a saved report?
                        philmodjunk

                        I usually use a layout dedicated for entering search criteria into global fields. The layout is popped up in a small floating window, the users enters/selects criteria and clicks a button that performs a script that does the find and sort needed to bring up the report as controlled by the criteria entered by the user.

                        I suspect that you'd need to put a copy of this field in a different layout part than the sub summary part. It could be given the "hide when printing" option in the Sliding and visibility section in the inspector so that this copy of the field does not appear when you print/preview/pdf.