Importing CSV data for the Midweek Meeting

ajtruckle commented 7 months ago

Is your feature request related to a problem? Please describe. Some scheduling applications have the ability to export assignment information in CSV format. Can the Import from Database History feature be extended to support importing of CSV data files?

Describe the solution you'd like It would be great if the Meeting Editor could populate the schedule (assignees) from the selected CSV files.

Describe alternatives you've considered At the moment, we have a need to use another scheduling application, I am manually re-inputting the same assignees into MSA. Importing would simplify the process tremendously.

ajtruckle commented 7 months ago

I have been working on this feature:

[!IMPORTANT]
The text file must be UTF8 + BOM encoded.

Supported Delimiters

Comma
Semi-colon

Column Headings

Date
Person
PartType
Assignment
School

PartType

Part Type
Chairman
AuxiliaryCounselor
OpeningPrayer
ClosingPrayer
TreasuresTalk
Gems
BibleReading
Apply1
Apply2
Apply3
Apply4
Apply1Assistant
Apply2Assistant
Apply3Assistant
Apply4Assistant
Living1
Living2
Living3
CBS
CBSReader
Host
CoHost
ZoomAttendant

School

Audience	Value
Main Hall	1
Class 1	2
Class 2	3

Sample Data [^footnote]

The data would be comma (or semi-colon) delimited:	Date	Person	PartType	Assignment
2024/05/27	Brother 1	Chairman	Chairman	1
2024/05/27	Brother 2	OpeningPrayer	Opening Prayer	1
2024/05/27	Brother 3	ClosingPrayer	Closing Prayer	1
2024/05/27	Brother 4	TreasuresTalk	1. Benefit Fully From Divine Education	1
2024/05/27	Brother 5	Gems	2. Spiritual Gems	1
2024/05/27	Brother 6	AuxiliaryCounselor	Auxiliary Counselor	2
2024/05/27	Student 1	BibleReading	3. Bible Reading	2
2024/05/27	Student 2	BibleReading	3. Bible Reading	1
2024/05/27	Student 3	Apply1	4. Starting a Conversation	2
2024/05/27	Student 4	Apply1	4. Starting a Conversation	1
2024/05/27	Student 5	Apply2	5. Following Up	2
2024/05/27	Student 6	Apply2	5. Following Up	1
2024/05/27	Student 7	Apply3	6. Talk	2
2024/05/27	Student 8	Apply3	6. Talk	1
2024/05/27	Student 9	Apply1Assistant	Assistant: 4. Starting a Conversation	2
2024/05/27	Student 10	Apply1Assistant	Assistant: 4. Starting a Conversation	1
2024/05/27	Student 11	Apply2Assistant	Assistant: 5. Following Up	2
2024/05/27	Student 12	Apply2Assistant	Assistant: 5. Following Up	1
2024/05/27	Brother 7	Living1	7. Make Wise Decisions About Work and Education	1
2024/05/27	Brother 8	CBS	8. Congregation Bible Study	1
2024/05/27	Brother 9	CBSReader	8. CBS Reader	1

[^footnote]: The Assignment column is not actually imported to MSA. It is assumed that you have already prepared the schedule by downloading the monthly data first.

ajtruckle commented 7 months ago

We have been working on an additional generic importer for CSV. It uses the concept of field mapping.

Progress so far:

ajtruckle commented 7 months ago

@cengizu @stecchio66

Some aspects of the field mappings concept need to be discussed.

Away Talks
Students
Teaching

Away Talks

MSA supports an unlimited number of away talks. So it would be tricky to import using this system. A The CSV-Weekend Meeting importer could be implemented that uses multiple rows.

Students

The CSV-Midweek Meeting importer is ideal because it uses multiple rows per meeting. So we don't have an inordinate number of columns in the file.

We potentially have 9 assignments, per school, making it 27 additional columns. I just don't like it.

Teaching

Most congregations only have the Teaching role in the main hall and no other classes. In which case we could support it. But MSA technically supports a Teaching assignment in all classes.

I am open to suggestions, if you have any. 💡🤔

ajtruckle commented 7 months ago

Links to drop lists have been updated with some changes.
We are currently working on importing the CSV data.

To facilitate the import process, I added extra properties to the JSON mapping file. Example:

{
  "FieldMapping": [
    {
      "Field": "Date",
      "FieldIndex": 0,
      "IncludeField": true,
      "Meeting": "Weekend",
      "MeetingIndex": 1,
      "LinksTo": "Date",
      "LinksToIndex": 0
    },
    {
      "Field": "Congregation",
      "FieldIndex": 1,
      "IncludeField": true,
      "Meeting": "Weekend",
      "MeetingIndex": 1,
      "LinksTo": "Congregation",
      "LinksToIndex": 7
    },
    {
      "Field": "PublicSpeaker",
      "FieldIndex": 2,
      "IncludeField": true,
      "Meeting": "Weekend",
      "MeetingIndex": 1,
      "LinksTo": "Speaker",
      "LinksToIndex": 8
    },
    {
      "Field": "OutlineNumber",
      "FieldIndex": 3,
      "IncludeField": true,
      "Meeting": "Weekend",
      "MeetingIndex": 1,
      "LinksTo": "Talk Number",
      "LinksToIndex": 9
    },
    {
      "Field": "OutlineName",
      "FieldIndex": 4,
      "IncludeField": false
    },
    {
      "Field": "Song",
      "FieldIndex": 5,
      "IncludeField": true,
      "Meeting": "Weekend",
      "MeetingIndex": 1,
      "LinksTo": "Song",
      "LinksToIndex": 6
    },
    {
      "Field": "Confirmed",
      "FieldIndex": 6,
      "IncludeField": false
    },
    {
      "Field": "Hospitality",
      "FieldIndex": 7,
      "IncludeField": false
    },
    {
      "Field": "Notes",
      "FieldIndex": 8,
      "IncludeField": false
    }
  ]
}

The importing functionality is now operational, but I still need to incorporate validation checks.

Note:

One of the fields must link to the Date item in the drop-list.
The file must be UTF8+BOM encoded.
The first line must be the headings row.
Records should be sorted in date ascending order.

cengizu commented 7 months ago

Just a suggestion for the ease of use:

When CSV is selected, the user can be better guided if some buttons other than Detect (Check, Uncheck All and Import) are disabled.

ajtruckle commented 7 months ago

@cengizu

When CSV is selected, the user can be better guided if some buttons other than Detect (Check, Uncheck All and Import) are disabled.

Good idea. Those buttons are now set to disabled when you select the CSV radio option:

Then, the Check All / Uncheck All buttons get enabled after you have detected the fields:

But, the Import button is only enabled when you have a valid field mapping configuration:

🤔 The Date Links to is mandatory, so I need to cater for that.

Links To

I have added these items to the Weekend Links To list:

Middle song number
Closing song number

🤔 Should the Open Prayer / Close Prayer assignments be together in the list?

Validation

A certain amount of validation is now being performed for each field:

Does the publisher exist in the database?
Does the congregation exist in the database?
Is the song number valid?
Is the talk number valid?

🤔 It is not an exhaustive validation at the moment. For example, it will not confirm:

If the publisher has the given assignment in the database.
If the speaker exists for the given congregation in the database.
If the talk number is assigned to the given speaker.

New betas are on their way to you for testing.

ajtruckle commented 7 months ago

@cengizu

Not sure what is going on my my commits. They keep closing the ticket!

cengizu commented 7 months ago

@ajtruckle

ajtruckle commented 7 months ago

@cengizu

I had a bit of a mishap while trying to sort things out—I accidentally lost all my code changes! 😅 But, thankfully, I managed to recover the code. And I opted to switch back to the stable release of GitHub Desktop instead of using the beta version. It seems to be working better now.

Validation

It is not an exhaustive validation at the moment. For example, it will not confirm:

If the speaker exists for the given congregation in the database.

It became apparent that the fields in the CSV file I have been testing with (an export from another application) have the fields in a specific order:

This works to our advantage because they are in the right order of priority:

Date
Congregation
PublicSpeaker
OutlineNumber

This means that we can do some extra validation (as long as the user selects the relevant fields!):

If the speaker exists for the given congregation in the database.

ajtruckle commented 7 months ago

@cengizu

Just corrected another issue. You could double-click the Field cell to into edit mode. I have now stopped this behaviour.

New betas on their way to you.

ajtruckle commented 7 months ago

@cengizu

The GUI has been simplified:

The delimiter setting has been removed because we don't need it anymore. We are now using the CkCsvW CSV parser and it automatically detects the comma or semi-colon delimiter for us. 😊

ajtruckle commented 7 months ago

@cengizu

Moved the controls around:

😊

cengizu commented 6 months ago

@ajtruckle

This looks good, you've created a progression from left to right. It is more intuitive for all kinds of users.

ajtruckle / meeting-schedule-assistant-support