Need Schema Setup for Export

[Rich S]

Greets, All:

I'm racking my brain trying to figure out how to set up the schema for exporting records for a report where I need to count how many records (in the Attendee table) have the same school district name per parent (Activity_Log) parent record. 

Please refer to the attached. The shown Activity Log (parent) record has 23 children records stored in the Attendee table, some of which are visible in the portal at the far right. Some of the children records have the same school district name (or more accurately, UUID number in the _kflt_District_NameID field.) When I export weekly records (using the fields at right in the Attendee field list) to an Excel spreadsheet, the recipient only wants one record--with a Count field--per district name that tallys all the records with the same district name, e.g. for this parent record there are four children records with the district name, Boone Central Schools and five children records with the district name, Schuyler Community Schools; the exported report should have only one record from Boone Central Schools--that has 4 in its Count field--and one record from Schuyler Community Schools with 5 in its count field. This way, it'll reduce the number of records stored on behalf of the recipient; there's no need to have 5 separate records from Schuyler when one will do with the needed Count number.

As always, thanks in advance for your help!

[comment]

What is the intended file format for the export, and how exactly should the data appear in this file?

Also, will you be including more than one parent record in the export? If so, make sure we understand exactly how this should appear in the result.

 

[Rich S]

Wow, you're fast! Here's one of the reports "old style" where it's one record per school district.

2_7_21 through 2_13_21.xlsx

 

...and yes, the weekly report will include more than one parent record; how many, that depends on how many events took place that week.

Edited March 3, 2021 by WF7A

[comment]

I am having a hard time "mapping" the file to your description. I was expecting to see 3 columns: Activity, District and CountOfAttendees. 

 

[Rich S]

I can see why you're having trouble. 

 

In the Attendee table is DistrictCount__lxn--that's the field where I _think_ a number should be pushed in for the the count-per-district; the manually entered number (in the Excel spreadsheet) is Count_Attendees_lct. 

District names aren't represented in the Excel spreadsheet proper but are listed by NDE code in the District_Name 2::NDE_Code_lxt column.

Activity was renamed Topic__lxt in the Excel Spreadsheet.

 

Mind you, I inherited this mess so I'm trying to fix the file; if you only could see the rest of the relationship graph--sheesh!

[comment]

I am afraid you have lost me at this point. Could we simplify this to the minimum required to solve the current problem? 

IIUC, you have a structure of:

Activities --< Attendance >-- People >-- Districts 

and you want to export a file in the format of:

Activity	District	CountOfAttendees
Activity A	District 1	10
Activity A	District 2	7
Activity A	District 3	5
Activity B	District 1	6
Activity B	District 2	4

Which should be easy to produce by exporting from the Attendance table while grouping by the People::DistrictID field.

 

[Rich S]

Sorry for the confusion--simplifying the problem, as you've posted, clarifies things a lot!

 Indeed, grouping--in Attendance--should work; would getting the CountOfAttendees be best calculated using a Summary field or is there a better choice?

Edited March 4, 2021 by WF7A

[comment]

A summary field.

Note that after you have selected grouping, you will get two instances of the summary field when adding it to the export field order: one will be shown as 'YourSummaryField' and the other as  'YourSummaryField by YourBreakField'. You want the latter.