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)
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.
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!
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.
Additional Field 1
Additional Field 2
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.
Campaign.fmp12.zip 18.6 K
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!
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.