10 Replies Latest reply on Aug 11, 2014 3:01 PM by philmodjunk

    Filtered Report to include fields from various tables

    DouglasCourter

      Title

      Filtered Report to include fields from various tables

      Post

           I am trying to create a report that will only list children who have been sponsored by a particular church. I have a Church ID field in the main child information table. I set up a global field for Church ID. I navigate to a layout where I pick a church from a dropdown which sets the value in the global field. From there a button runs the report/layout in which a script is run to perform the correct find/filter. I've attached several snippets. I have searched throughout the forums, but can't find a solution. Here are my problems: I want to be able to display the church's name, city and state on the report in the header. I then want to list all the children (various fields) who are sponsored by that church. I've tried portals. I've tried using various tables as the basis of the layout, but I can't get this to work. Any help would be appreciated.

      Relationships.PNG

        • 1. Re: Filtered Report to include fields from various tables
          DouglasCourter
          /files/10b97f59ad/Sponsored_Children_by_Church_list.PNG 346x111
          • 2. Re: Filtered Report to include fields from various tables
            DouglasCourter
            /files/fa6256c5d0/Select_Church_Layout.PNG 303x114
            • 3. Re: Filtered Report to include fields from various tables
              DouglasCourter
              /files/987247c05d/Report_Script.PNG 497x90
              • 4. Re: Filtered Report to include fields from various tables
                philmodjunk

                     This appears to be the key part of your relationships, which all appear to be correctly set up:

                     Sponsorship Database (children)>-------Church Contacts>--------Sponsoring Churches    (>---- means "many to one")

                     This appears to be exactly the relationships that you need for what you want in your report.

                     Set up a list view layout based on Sponsorship Database. You can use your global field with Go to Related records or a scripted find to pull up a found set of these records. Because many records in this layout's table link to one church contact which then links to one sponsoring church, you should only need to add the fields that you need from Sponsoring Churches directly to your layout--whether in the header, the footer or other parts of your layout.

                     But your global field won't enable you to pull up the correct records in SponsorShip Database unless you are also correctly entering the right value into the Sponsoring Church ID field in every record of this table. This is a weak point in your design and a field that you can actually remove from your database. You can, instead, use your global field to find a record in Sponsoring churches and use the relationships I have reproduced at the top of my post to pull up the records in Sponsorship Database that are linked (via contacts) to that church.

                • 5. Re: Filtered Report to include fields from various tables
                  DouglasCourter

                       I made some changes, but it is still not working correctly. I added the Go to Related Records to the script. I changed the source fields of the report to the table of children. Now when I run the report I don't get the church's name/city/state at all (it's on the layout twice because I tried it from both the Sponsoring Church and Sponsoring Church 2 tables). The report also lists all the children every time, so the filter is not working. Also, for a point of information, a church might have more than one church contact. I want the list of children sponsored by the church no matter who the church contact is. Thanks again. I feel like I should be getting a better handle on this, but none of it seems intuitive to me.

                        

                  • 6. Re: Filtered Report to include fields from various tables
                    DouglasCourter

                         Here is the layout of the report.

                    • 7. Re: Filtered Report to include fields from various tables
                      philmodjunk

                           Your script is not set up correctly.

                           What you would do would be as follows:

                           Go to layout ["Sponsoring Churches" ( Sponsoring Churches ) ]
                           Enter FInd Mode []
                           Set Field [Sponsoring Churches::Sponsoring Church ID ; Globals::Global Sponsoring Church ID ]
                           Set Error Capture [On ]
                           Perform Find []
                           If [ Get ( FoundCount ) = 1 //church record found ]
                              Go To Related Record [Show only related records; From table: Sponsorship Database; Using layout: "Report - Sponsored Children By Church" (Sponsorship Database) ]
                           Else
                              Show Custom Dialog ["An error occurred. A single church record was not found"]
                           End IF

                           The layout design looks correct provided that you see: "Sponsoring Churches::" as the first part of the text in "Display data from" in the inspector when you select either of the two fields in the header.

                           If you are pulling up the correct records in Sponsorship Database on this layout but are not seeing data in these two fields, this suggests that the records are not correctly linked to a church contact or the contact is not linked to a church.

                            

                      • 8. Re: Filtered Report to include fields from various tables
                        DouglasCourter

                             When I changed the script (which I run when the Sponsored Children by Church layout is entered) the first line takes me to a different layout/report than I want. It takes me to the Sponsoring Churches layout. So I changed both instances which call for the Sponsoring Churches table back to the Sponsorship table. Now, everything works perfectly (picks and displays the correct children) except I can't get the name of the church to show up in the Header. I also thought about one additional feature I would like. A church might have more than one contact, so is there a way to group the children by their contact (sorted by name) within one church? Thanks so much for your help!

                        • 9. Re: Filtered Report to include fields from various tables
                          DouglasCourter

                               I have fixed the church name/city/state issue. The layout actually was set up correctly. The problem was my misunderstanding of how Filemaker's calculated fields work. I have used MS Access for years and their fields recalculate every time the record is displayed. I had created the calculated field to display church name/city/state after I had created the records. Evidently FM did not calculate the values for the calculated field until I made a change in one of the fields used in the calculation. Is there a way to have calculated fields recalculate every time a record is displayed?

                               Also, currently when I run the report and get the correct results the Show All button is still available. If it is pressed accidentally then it displays all the children from all churches even though there is only one church name at the top. Is there a way to disable that button?

                               It looks like the only other issue is the one about setting up a grouping level on Church Contacts within the same church as I asked in the post above. Again, thanks for all your help. This database will help greatly coordinating the sponsorship of children in Haiti.

                          • 10. Re: Filtered Report to include fields from various tables
                            philmodjunk
                                 

                                      When I changed the script (which I run when the Sponsored Children by Church layout is entered) the first line takes me to a different layout/report than I want. It takes me to the Sponsoring Churches layout.

                                 But the script doesn't STAY on the Sponsoring Churches Layout. It's crucial that it make this layout change as the script needs to find the correct sponsoring church record before using Go To Related records to find the related Sponsored Children records. So while it changes to the Sponsoring church layout to start, when finished it should be on the layout that you want. If it does not end up on that layout, then there aren't any sponsored children records linked, (via the link to contacts) to the sponsoring church record.

                                 From what you report, I suspect that this is why you aren't seeing any data in the header fields.

                                 

                                      Evidently FM did not calculate the values for the calculated field until I made a change in one of the fields used in the calculation. Is there a way to have calculated fields recalculate every time a record is displayed?

                                 It sounds like you took a field of type text and added an auto-enter calculation. Auto-enter calculations that refer to fields from a related table will not automatically update when data in the related record is modified. But if you define that field as a field of type calculation instead, it will update whever data in a referenced field--whether from a related record or not,  is modified. But from what I see here, you really shouldn't need or use such a calculation just to pull up data from a related table and if this field is used as a match field, it may not work as an unstored calculation field.

                                 

                                      Is there a way to disable that button?

                                 If you have FileMaker Advanced, you can set up this layout with a custom menu set that does not have this menu option to select.

                                 

                                      It looks like the only other issue is the one about setting up a grouping level on Church Contacts within the same church

                                 Once you have successfully pulled up these records and they are correctly linked to records in contacts, you can add a sub summary layout part and specify Church Contact ID as the "when sorted by" field. You can then put fields from the Church Contacts table occurrence inside this sub summary layout part to set it up as a kind of "sub header" in your list of sponsored children records.

                                 For more on Table Occurrences, see: Tutorial: What are Table Occurrences?

                                 For more on summary reports and sub summary layout parts, see: Creating Filemaker Pro summary reports--Tutorial