5 Replies Latest reply on Apr 8, 2011 11:45 AM by philmodjunk

    Creating report database



      Creating report database


      I'm having difficutly getting some concepts down.  I'm fairly new to FM, the application, not necessarily to DBs. However, I feel like I am making this way harder than it needs to be.

      I've got a templated contact management DB with multiple tables and files.  I want to create some custom reports, keeping a copy of the data.  Also, given the expected size of the original files, I need to put the reports in their own file.

      The problem that I am having is getting the data out of the main file and into the reports file.

      I've got a find script in the main file, based on a layout, giving me the correct fields and the correct finds.

      I've got the script going between scripts and windows.

      I can't seem to pass the data between the scripts that access different files.

      Does anybody have a good tutorial or starting place to see how this works?


        • 1. Re: Creating report database

          I would keep the report layouts in the same file. Then there is no need to pass the data from one file to another. Adding 4, 5 , 50 more layouts to your file will not greatly increase the size of the file and this increase in size really shouldn't be an issue.

          To pass data from one script to another you have several different "buckets" you can put the data in to make it available from the other script.

          You can pass data in the script parameter and there are several ways to combine multiple items in the same parameter string. The other script then uses Get ( ScriptParameter ) to access this data.

          You can use Set Variable to put the value in a global variable. The other script can then refer to the global variables. Global variables start with $$ instead of $. Global variables created in on file cannot be accessed by a script running in another file, however.

          You can put the data in global fields. Data in global fields can be accessed from any layout in the same file and by any script. (Just like a global variable. If you use an external data reference to create a table occurrence to the global field's table, the contents of a global field from a different file can be accessed. (This is how we did all this before we had variables and script parameters.)

          • 2. Re: Creating report database

            Thanks Phil.  However, I'm looking to create a DB of past reports, not just the ability to run a report.  For export reasons, I don't want to dump this out to PDF's.  Because of wanting to keep the static data snapshots, it will increase the DB size, and could do so rather quickly.

            So, if I read this correctly, I will need to utilize temporary global fields/variables to accomplish this.  Correct?


            • 3. Re: Creating report database

              In that case, I'd use import data to import the data from your current file into the reports file. Then your reports can run directly from the imported data. Trying to pass all of that data using the methods I described will prove very difficult for most reports you might create--sorta liking trying to drain the ocean by sipping thorugh a soda straw...

              it will increase the DB size, and could do so rather quickly.

              But why are you so concerned about the file size? Some of my DB's are several Gigabytes and size and continue to grow in size each day as new records are added...

              I don't want to dump this out to PDF's.  Because of wanting to keep the static data snapshots,

              I'm not sure I follow your logic here. The PDF's are static snapshots of your data so what is the issue here with PDF's? (And your PDF's once generated can be loaded back into container fields to make them easy to find and open for view...)

              Keeping "Static data snapshots" may prove to be a challenge here as one report's data set might overlap the data set of another report that you also want to save.

              • 4. Re: Creating report database

                So, you are suggesting that I can somehow import only specified fields in a found data set from a different file?  Or are you suggesting a script in the data file that exports the specified fields from a found set, and then a second script in the report file that imports that data into the report DB?

                I'm concerned about the data size for a number of reasons.  For one, this will be a mass accessed file via network, and often from external sources over the internet.  Another reason is , this won't be the only DB on the server.  A smaller file will place less overhead on the server.  These reports, while accessed often and regularly, won't be a constant part of the workflow.  Also, I'm already looking at thousands of contacts in the main file, before the growth and importing of leads, notes, etc. 

                As for the dumping out to PDF's, I'm afraid I inadvertantly deleted part of my sentence.  I'm wanting to have the static historical field values available for export in the future.

                • 5. Re: Creating report database

                  A script in the report file can import data from the original file. When you import records, you have complete control over which fields are imported from the source file and which fields in the target file will receive that data. You can run a script on your contacts file that finds the records you want to export/import and then it can use Perform Script to run a script in the report file that imports the found set of records.

                  There are many options for Import records so you'll need to read up on this in FileMaker help and do some tests with your data and files.