7 Replies Latest reply on Apr 22, 2011 9:46 AM by JonathanRoss

    Reports from multiple databases? Please help.

    JonathanRoss

      Title

      Reports from multiple databases? Please help.

      Post

      Hello!

      I currently am working with Filemaker Pro and I have a question that I cannot seem to figure out.

      I have multiple databases that contain contact and work information from different marketing efforts.

      I would like to be able to search across all of these databases in one fell swoop, or create some kind of report that pulls data from all databases (for example, I'd like to search everyone that lives in New York City across all databases).

      Is this possible in Filemaker? Any advice would be greatly appreciated.

      Thanks,

      Jonathan

        • 1. Re: Reports from multiple databases? Please help.
          philmodjunk

          Possible? Maybe, but you really haven't given us any idea what you are really trying to do here. There are many possible interpretations here.

          The most common solution, is not to have separate databases (or tables) for the same data, but to keep all the data in a single database (table) in the first place.

          Can you describe these "separate databases" in more detail and explain why they are separate?

          Can you describe what kind of report you want to see once you have searched them all for "New York"?

          • 2. Re: Reports from multiple databases? Please help.
            JonathanRoss

            Hey PhilMod, thanks for such a fast response!

            I'll do my best to describe the separate databases and what kind of report we're looking for.

            We have one database that contains everyone who has reviewed our products for our company. The database shows their name, address, phone, email, and what product they reviewed.

            We have a second database that contains everyone that we have done marketing efforts to over the past year. This database shows their name, address, phone, email, and what marketing efforts we made, whether it be donations, flyers, announcements etc.

            The kind of report that I believe we are looking for is something along the lines of "we want to find every contact that lives in the New York City area, and then see what efforts we have made with each one."

            Does this make any sense? I'm starting to think keeping all of these "databases" in just separate tables in Filemaker is the solution here.

            Thanks,

            Jonathan

            • 3. Re: Reports from multiple databases? Please help.
              philmodjunk

              And that's a classic case where you probably do not need separate tables. If you put the contacts for "everyone who has reviewed our products for our company" and "everyone that we have done marketing efforts to over the past year" into the same table, you no longer have the problem of trying to search two different databases with the same criteria and then combine the results.

              Just to describe one out of many possible ways that you might do this, you could have a combined "Contacts" table with all the name, address, fields and then also a "contact type" field that can hold values that distinguish between the two types of contacts. Scripts, finds, relaitonships, etc. can then use this contact type field to keep the data properly segregated when you only want to work with one type of contact or another.

              Working for now with your two separate tables (databases), You'd need to perform exactly the same find on both tables. This can be done with a script. What you do with the results depends on your needs and the structure of these two tables. Are they exactly the same with exactly the same fields? Once you have pulled up a found set in both tables, you can use Import Recods to import the records into a third table to display the results in a single report. You can also use a script to save a PDF of the report from the first table and then save a PDF with the append file option to attach the PDF from the second table's layout to the end of the first PDF'd report.

              All complications that can be readily avoided if you merge the two tables to begin with.

              • 4. Re: Reports from multiple databases? Please help.
                JonathanRoss

                Thank you for the great advice Phil.

                I will merge these tables and make everything accessible from one file, even though my disillusioned boss cannot wrap his head around it.

                I really appreciate the fast reply and great information. Thank you again!

                Jonathan

                • 5. Re: Reports from multiple databases? Please help.
                  JonathanRoss

                  Hi again Phil,

                  Quick question:

                  "Scripts, finds, relaitonships, etc. can then use this contact type field  to keep the data properly segregated when you only want to work with  one type of contact or another."

                  How would I go about doing this?

                  I know how to sort and find etc., but how exactly would I keep these very different groups properly segregated?

                  Thanks again,

                  Jonathan Ross

                  • 6. Re: Reports from multiple databases? Please help.
                    philmodjunk

                    Much depends on the structure of your data, your layout designs and how you work with each group of records. Just from what you've posted so far, it seems likely that the same contact could be a member of both groups. If so, you can add a text field to this table and format it with checkboxes so that you can assign a given record either or both groups. You may also want to consider treating this as a "many to many" relationship where you use a join table to link each contact record to different "group records" in yet another table. I can't tell you one or the other approach is better, you'll have to figure that out for yourself. For simplicity here, I'll stick with just using a checkbox field for group membership.

                    With FileMaker 10 and 11, you can use script triggers that automatically constrain current found sets to a specific group. Thus an OnLayoutLoad script trigger, can use this script to limit all records displayed on that layout to contacts that are members of the "Review" group:

                    Enter Find Mode [] //clear pause check box
                    Set Field [YourTable::GroupField ; "Review"]
                    Set Error Capture [on] //keeps dialog box from interrupting script if no records in current found set are part of "review" group
                    Constrain Found Set []

                    If you also use the OnModeExit script trigger to perform this script whenever you exit find mode so that when a user does a find for "John Smith". It only displays records for "John Smith" that are also labeled as "review". in the group field.

                    It's also possible to use settings in Manage Security to limit access to records on a record by record basis. While I haven't tested this idea, there is a Get function that returns the name of the current layout, so it should be possible to use manage security to limit visible records for a specific layout or group of layouts to just one group of records.

                    See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set  up record by record access controls.

                    That only leaves two basic ways a user might still bring up a record from the Wrong group: Show All Records and Show Omitted Only. If you use security settings, records from the wrong group remain unviewable, but you see a grey screen with something like <<access denied>> on the screen. To avoid this, you can use FileMaker Advanced to create a custom menu that either removes these options from the Records Menu or replaces the standard FileMaker operation with a script that you create which uses the above Constrain Found set trick to eliminate records from the wrong group from the found set after doing the Show All or Show Omitted action.

                    • 7. Re: Reports from multiple databases? Please help.
                      JonathanRoss

                      Thanks again Phil, I really appreciate the insight here.

                      I will work with what you wrote and see what I can put together here in my database with the scripts and access privileges.

                      Jonathan