8 Replies Latest reply on Jan 9, 2012 12:55 PM by shashikanthkuntla

    How to filter records before displaying on Layout.

    shashikanthkuntla

      Title

      How to filter records before displaying on Layout.

      Post

      I have two tables X and Y.

      I have columns x1,x2,x3,x4 in table X.

      Columns x1,y1,y2,y3,y4 in table Y. (Field y1 can take either "yes" or "no").

      Note: x1 is common field for both the tables.

      Now the layout i have is A which takes the column values from table X i.e, x1,x3 and x4

      Problem: I need to filter the layout A before it displays. Filter is, i need to check in table Y whether for x1 the value of y1 in table Y is set to "yes" or "no", if it is set to "yes" only then i need to display it in the layout A.

      Someone please suggest me how to do this?

      Please let me know in case you require any additional information.

        • 1. Re: How to filter records before displaying on Layout.
          philmodjunk

          "Alphabet Soup" like this is much harder to follow than using the actual field, layout and table names of your database.

          You should define this relationship if you have not already done so:

          TableX::x1 = TableY::x1

          Then you can perform a find for all records in TableX that have a related record in TableY where y1 = "yes".

          Enter find mode on the TableX based layout. Put "yes" in the TableY::y1 field (With this relationship, you can add fields from table Y on your table x layout.)

          Perform the find.

          This can also be done with a script:

          Go To Layout [tableX]
          Enter Find Mode[] //clear pause checkbox
          Set Field [TableY::Y1 ; "Yes"]
          Set Error Capture [on]
          Perform Find []

          • 2. Re: How to filter records before displaying on Layout.
            shashikanthkuntla

            I thought it would be easy for you to understand rather than giving the original field names which are more confusing.

            Thank you very much for the response.. This works with using using find command..

            My intention is to display the layout with only the records which is the output of find(Given above in your post). Like when i run the above set of scriptsteps i will be shown the records which i want and an option "show all" will be activated on the toolbar by which user can see all the records again.

            I want to see only the filtered records, Hope you understood. Please let me know in case you require any additional information.

            • 3. Re: How to filter records before displaying on Layout.
              philmodjunk

              And where at this point are you having trouble doing that?

              Performing the find will give you want you need. To return to a data set of all records, you simply select Show All Records in the records menu or press the keyboard shortcut for the same. I believe that you can also customize your tool bar to include a button for this if you wish, or you can place a button on your layout that does a show all records when clicked.

              • 4. Re: How to filter records before displaying on Layout.
                shashikanthkuntla

                Thanks for responding.

                 There is no trouble in doing that, but that is not what i want. My requirement is to show the records after they are being filtered. Its like i need to join two tables and show the output in the layout. Your support will be helpful.

                • 5. Re: How to filter records before displaying on Layout.
                  philmodjunk

                  What I have described can do exactly that.

                  What kind of relationship do you have. One to one or one to many?

                  With a one to one relationship, you can list fields from both tables on the same layout.

                  With a one to many relationship, you can base your layout (and your find) on the table from the "many" side of the relationship and include fields from the "one" side on it to get your list of records matching your criteria.

                  • 6. Re: How to filter records before displaying on Layout.
                    shashikanthkuntla

                     I have made a script and triggered it when Layout loads(Please see figure 2 in the attachment).. Script contains the entering into find mode and performing find based on what i want.

                    Once i load a layout, it is displaying only the records what i want but i do not want to show the functionality button "Show All" once the records are displayed. (Please see figure 2)

                    • 7. Re: How to filter records before displaying on Layout.
                      philmodjunk

                      Then you should not have this button visible at all. You can hide the status area ( and lock it) to do that. Or, if you have Filemaker advanced, you can install a custom menu that does not have this option.

                      If the purpose is to keep users from accessing records to which they are not entitled, you can use manage | security and record level access control to limit their access to only those records to which they are permitted.

                      • 8. Re: How to filter records before displaying on Layout.
                        shashikanthkuntla

                         Ok got it thank you very much for patiently answering most of my questions.. :) Have a great day//