4 Replies Latest reply on Mar 14, 2012 10:44 AM by karendweaver

    Relationship and showing data problems

    TC

      I developed a database in Access, but now the customer wants to use the system on a MAC. I chose FileMaker after researching what product to do. Here is the background

       

      The system tracks customers and the filters needed to support them by month. I have three tables and have extracted the key fields below

       

      tblCustomer

      <CustomerID>

       

      tblCustomerFilterAmounts - one to many relationship with tblCustomer

      <CustomerFilterAmountsID>

      <CustomerID>

       

      tblCustomerMonths - one to many relationship with tblCustomer

      <MonthsID>

      <CustomerID>

       

      I created a form layout and data displays correctly as I move from my two test customer records with each record having two filters records and two month records. Views of each tbl also show correct data

       

      I want/need to do a rollup that shows what filters need for each customer by month so if I had one customer with two filter records and two month records, I want displayed in a list view (for now):

      Filter1 Data Month1

      Filter1 Data Month2

      Filter2 Data Month1

      Filter2 Data Month2

       

      I tried creating using the relationships above, but only got Filter1 Data Month1

      I creating another relationship using tblMonths2 with tblCustomerFilterAmounts, but that was a many to many relationship. I split the relationship into two one to many relationships by adding a tblGoBetween (for now) with the following

      tblGoBetween

      <goBtwnID>

      <CustomerFilterAmountsID> linked to <CustomerFilterAmounts> in tblCustomerFilterAmounts

      <MonthsID> linked to <MonthsID> in tblCustomerMonths

      Now I get Filter1 data to show only. I created a view for tblGoBetween and it shows no data

       

      Can anyone show me where I am going wrong? I would greatly appreciate it

       

      TC

        • 1. Re: Relationship and showing data problems
          comment

          I don't understand this part:

           

          tym@thecaddells.com wrote:

           

          I want/need to do a rollup that shows what filters need for each customer by month so if I had one customer with two filter records and two month records, I want displayed in a list view (for now):

          Filter1 Data Month1

          Filter1 Data Month2

          Filter2 Data Month1

          Filter2 Data Month2

           

          I am not sure what a "rollup" means. In your example, there is 1 record in the Customers table, 2 records in the CustomerFilterAmounts table, and 2 records in the CustomerMonths table, is that correct? There is no table that has 4 records, so no table can provide the display you describe. It's also not clear what "Data" means: you don't seem to have filter-by-month data anywhere.

          • 2. Re: Relationship and showing data problems
            TC

            Michael

            Bad choice of words I guess when I used want to do a rollup.  Is this clearer

            Filter data = Filter Type, Filter Size, Filter quantity.  My main form shows this data (1 to n records) in a portal where the user can add or delete rows/records as needed.  There is another portal that displays the months of service where the filters are needed.  I would like to show a report or layout that shows

            what months the different types of filters are needed as a total and then by customer so the rollup for all the filters across each customer may look like:

             

            Filter type, Filter size      Jan      Feb      Mar... Dec     Total

            Merv8.1     16x16x1          10     10          10 ....10        120 (summation of Merv 8.1 with 10 each month)

            Merv8.2     16x16x2          5                      5                   30 (summation of Merv 8.2 with 5 every other month

             

            Second table would be the same, but grouped by customer or customerID

             

            Does this make more sense

            TC

            • 3. Re: Relationship and showing data problems
              comment

              Does this make more sense

               

              Not sure about that. If you had a table of Orders(?) with fields for:

               

              OrderID

              CustomerID

              FilterID

              Date

              Quantity

               

              you could easily produce a report in the form of:

               

              Customer: Adam

              Filter ABC

              • Jan: 10
              • Feb: 12

              ...

              Filter XYZ

              • Jan 5
              • Feb: 4

               

              Customer: Betty

              Filter ABC

              • Jan: 25
              • Feb: 21

              ...

               

              or a similar report ordered by Filter then by Customer. Filemaker is not really good at producing cross-tabs, but that too is possible with some effort. 

              • 4. Re: Relationship and showing data problems
                karendweaver

                Hi TC

                 

                I recommend you research Bruce Robertson's Virtual List technique.  It's an extremely versatile way to pull data into a coherent report, allows for cross-tab and summary reports, it's fast (if you do it right), and just learning it will give you some mad FileMaker skills.

                 

                SeedCode has a great example file you can download at http://seedcodenext.wordpress.com/2011/11/05/virtual-list/

                 

                Hope that helps!!

                 

                warm regards,

                Karen