5 Replies Latest reply on Oct 14, 2012 6:58 PM by ianlenehan

    Create Summary Data

    ianlenehan

      Hello

       

      I hope someone here can help me with my query…

       

      I have a set of data that gets imported and regularly updated via Excel import. The original data has multiple lines of which I am looking to summarise and then add more data to.

       

      Essentially there are fields that will be in these data that are repeated through each row in excel, however I want Filemaker to summarise the data.

       

      Two of the fields that are repeated are barcode, install co, and the unique data in each rows would be panel (though sometimes this is blank). There is also a quantity field which is generally 1, but sometimes can be higher than 1.

       

      Capture.PNG

       

      I don’t want to see panel ID in this view in Filemaker, what I want to be able to see is one record that shows barcode, install company, and a sum of the quantity for the combination of this barcode and install company. (Note – barcode may appear ten times, for example, 3 times for 1 install company, 3 times for another and 4 times for a third). Essentially what a pivot table would do in Excel. I also then want to add additional fields that will be manual entry data or linked data that I can add to the summary data. Below is an example of what I want Filemaker to be able to do with the Excel data pasted in above.

       

      Capture.PNG

      I've been struggling with how to work this out. I'm pretty sure I need to create a second table that summarises that data and then allows me to add new data to these summaries, but I can't really figure it out.

       

      Apologies if this is confusing, happy to clarify anything...any suggestions would be much appreciated!

       

      Cheers

      Ian

      Filemaker Pro 12

        • 1. Re: Create Summary Data
          martinsorich

          Hello Ian,

           

          I'm going to make a few assumptions. Your database has a table named Campaign with a unique ID field (primary key) and also a field named Campaign Name. The data you're importing above from the Excel spreadsheet, for example's sake, is stored in a table named Campaign Line. You have a relationship between Campaign and Campaign Line using the unique ID field described above.

           

          In your Campaign Line table, create a new field called sum_qty, which is a Summary field totalling the Qty field.

           

          In your Campaign table, create the Additional Field 1 and Additional Field 2 fields. 

           

          Create a new layout named Campaign Report based on the Campaign Line table occurrence used in the relationship above. Add a Subsummary part to this layout, that will be sorted by Campaign::Campaign Name.

           

          In the subsummary part, of the layout based on the Campaign Line table occurrence add these fields:

           

          Campaign::Campaign Name (related field)

          Install Co

          Barcode

          sum_qty

          Campaign::Additional Field 1 (related field)

          Campaign::Additional Field 2 (related field)

           

          Next, sort the records in your new Campaign Report layout by the related field Campaign::Campaign Name. You should see the result of your second attached pic and be able to enter values into the Additional Field 1 and Additional Field 2.

          • 2. Re: Create Summary Data
            ianlenehan

            Thanks a lot for your reply m_sorich. I've certainly made progress with your suggestion, however I am not there yet.

             

            If I have followed your instructions correctly, this method only summarises the total for Campaign Name. Within each campaign, there could be different Install Companies and also different barcodes. So essentially what I need is a summary showing the total number of posting orders for each barcode, for each campaign, for each posting company. It needs to be based off the unique combination of these three identifiers.

             

            As you can see in the second Excel screengrab I posted, the example shows that the campaign can appear multiple times, as can the install company within that campaign. It's the barcode within those two sets that is ultimately unique.

             

            Hopefully this makes sense!

            • 3. Re: Create Summary Data
              martinsorich

              Ian,

               

              Thanks for clarifying your problem further.

               

              To be thorough, an ideal solution would have 5 tables Campaign, Barcode, Campaign_Name, Install_Company and Campaign_Line.

               

              Campaign

              campaignId

              campaignNameId

              installCompanyId

              Additional Field 1

              Additional Field 2

               

              Barcode

              barcodeId

              barcode

               

              Campaign_Name

              campaignNameId

              campaign_name

               

              Install_Company

              insallCompanyId

              install_co

               

              Campaign_Line

              campaignLineId

              campaignId

              qty

              sum_qty

               

              This configuration would accommodate normalization of the data most optimally. I won't go into the details, but the report would still be based on the Campaign_Line table occurrence used in the relationship with Campaign.

               

              I have provided an example file. I think the bigger issue you will have is how to parse the data from the import file.

               

              Martin


              • 4. Re: Create Summary Data
                ianlenehan

                Hi Martin

                 

                Thank you so much for you help. I have worked it out. Whilst I haven't used your solution I have used elements from what you have suggested to make it work.

                 

                In my original table with all the data imported from the Excel sheet, I created a unique ID with a calculation that is basically a concatenation of campaign name, barcode and install company. In the layout, I then put all relevant fields into the sub summary and sorted it by this concatenation.

                 

                I also have a second table to allow me to input the additional fields. I added this to the sub summary line using a portal linked the portal by the unique ID (the concatenation), and it seems to be working well. I am also using the sum_qty field that you suggested to give me the running total.

                 

                Thanks again for all your help!

                 

                Cheers

                Ian

                • 5. Re: Create Summary Data
                  ianlenehan

                  Just to add, in case anyone else reading has had the same problem as me. I soon realised that I could not search this layout as all my data was in the sub summary part, and apparently you can't search a sub summary. To get around this I simply put a copy of the field I wanted to search for in the footer and it worked for me.

                   

                  Cheers

                  Ian