2 Replies Latest reply on Aug 10, 2009 8:44 AM by afubeca

    New to relational databases

    afubeca

      Title

      New to relational databases

      Post

       

      I have raw data from a relational database that I have loaded into FileMaker and indexed. The data is drawn from annual reports submitted for approximately 13,500 locations. Each report represents about 20 pages of text. I have report data for the last 12 years, so all together there are 162,000 reports.

       

      The data is organized in 4 tables.

       

      Table 1 - Report Table - 162,000 Records (one for each of the reports) - With a unique KeyField for each report (one record per report) along with select data for each record.

       

      F1 KeyField

      F2 Data1 - Location ID

      F3 Data2 (Report Beginning Date)

      F4 Data3 (Report End Date)

      F5 Data4

       

       

      Table 2 - Alpha Table - In this table the KeyField repeats itself about 100 times for each alpha data field in the report, so there are about 16,200,000 records in this table.
      • F1 KeyField
      • F2 Contains the page the data is on
      • F3 Contains the row the data is in
      • F4 Contains the column the data is found. In.
      • F5 contains the data itself.

       

      Table 3 - Numeric Table -Set up in the same fashion as the Alpha table, however in this table the KeyField repeats itself over 600 times for each numeric data field so there are over 100,000,000 records in this table.

       

      Table 4 - Rollup Table - I don't understand the logic of this table. This table has additional numeric data that it appears could have just been added to the numeric table. This table only has three columns. This table has about 20,000,000 records.

      • F1 - Key Field (repeated as in Tables 2 and 3)
      • F2 - A code which delineates the page, row and column of the number all in the same field.
      • F3 - The numeric data.

       

      I would like to accomplish 3 objectives with this data:

       

      1 - Use Table 2 (Alpha table) to isolate certain unique KeyFields (reports)

      2 - Display a list of the unique KeyFields (reports) and their corresponding information in Table 1 (Report Table) so that one or more or the reports can be manually selected based on information in the Report Table.

      3 - Create two different reports

      i - After isolating the KeyField (report) merge all the data for a single report back into an excel file or word document template. Essentially recreating the report electronically from the raw data.

      ii - After selecting multiple reports use them to create a simple trend report comparing select data from the selected locations over the course of the 12 years.

       

      Initially I will perform the finds in FileMaker myself, but eventually I would like to make a web based version.

       

      I am told there are a number of ways to do this, but here is one example using my logic...

       

      I typically want to isolate all the reports for a certain county. I accomplished this manually by doing a find with two requests. One that has that has the page, row and column (in F2, F3 and F4) where the state is inputted on the report with the state I am looking for in F5; and a second request with the page, row and column that the county is located in with the county I am looking for in F5. I then sort the records by the KeyField and extract the KeyField numbers that are repeated (I can't just search for the county because county names are repeated in different states). I take the numbers that are repeated and perform a search in the Report table and then use the information on the report table to constrain the KeyField further. I have not gotten to the step of manually creating the reports.

       

      I am looking for some guidance on how I should set this up initially to make the process most efficient, quick and easy. Keeping in mind that eventually I want to make the process web based. I also need help in the next step of actually creating the reports.

       

      Thanks in advance for any help you can provide.

       

      Thanks.


       

       

        • 1. Re: New to relational databases
          TSGal

          afubeca:

           

          Thank you for your detailed post.

           

          Set up a relationship between Report Table and Alpha Table where Report::F1 = Alpha::F1.  Then, you can provide a portal in either table to display information in the other table.  That is, on your Report layout, place a portal into the Alpha table, and in the Alpha layout, place a portal into the Report layout.

           

          In Alpha, you will then see all Reports associated with each Alpha record, and in Report, you will see all Alpha records associated with each Report.

           

          In the Report table, create a calculation field that returns a Count of the number of Alpha records.  When the value is 1, then you know you have a unique entry.

           

          I don't have enough information regarding what data you want to merge, but the above information should give you a good starting point.

           

          Let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: New to relational databases
            afubeca
              

            TSGal, Thanks for the advice, I will play around with this for a bit.