Data collection form integration

[katherinelilly]

What: Create a master data management form that collates information from the distribution plan, map of affected areas, and needs assessment data collection form.
Outcome: The operations team in the field is supporting the BRC to streamline their data collection and management, and so would like to create a mini database that integrates these different data sources and can be filtered by municipality.
Format: Excel Power Query or another (simple!) database tool
Priority: Urgent
Deadline: Wednesday, June 5
Link on GitHub: link

[adakita-01]

Hi Katie,

Thank you for assigning me this task.

Questions:

1. Do we have access to the distribution plan?
2. Kobo assessment: @jeanne-crump completed the form on the IFCR instance. Could it be shared to include the data?

Thank you!

[katherinelilly]

Hi Carla!

Yes, I am attaching the distribution plan here. Plano de Distribuição de Doações CVB SOS RS 2024.xlsx

Jeanne says she will give you access to the Kobo form. To provide a bit more context, the Brazilian Red Cross team has been collecting so much secondary data (and soon primary data), but they do not have a way to organize the information so they are using multiple different data files. The Ops Manager suggests that it would be useful to create a master dataset where the data can be organized. They would like to have the data at the municipality level, including data points like number of affected people, items distributed, if there is a Red Cross branch, what the needs are (from the Kobo form questions), what organizations are also working there, and things like that.

I was thinking that something similar to the 4W data collection form may also be useful to incorporate?
01.03.02.06. 4W template.xlsx

It is a bit of a vague request but perhaps if you are able to create a "draft 1", I can share it with the team and then they can adjust it as needed. Please let me know if you have any questions!

Thank you,
Katie

[jeanne-crump]

Hi Carla,

The damage assessment doesn't have any submissions to Kobo yet, but I've set up the form. I've attached the Word version of the assessment form that I converted into a Kobo form. I can add you as an editor to the form so you can see the data table, if that works? Just share your Kobo username.

Heads up: I had copied and pasted text from their Word document into the Kobo form directly (which I now realize was a bad idea!), so the HTML script carried over into the label names unfortunately. It may require an added bit of cleaning to organize the column headings in the raw data export, but happy to help with that if needed! Let me know if you have any questions yet.
Avaliação de Riscos e Necessidades - CVB Operação SOS RS - Atualizado_JC questions.docx

[adakita-01]

Thank you so much for all the documents.

@jeanne-crump The Kobo user name is carlatapiabaldis

[jeanne-crump]

Ok, I just added you to have permissions to view form and submissions. I think that should allow you to export submission data but if not let me know.

[adakita-01]

Hi!

I've uploaded the first draft of the Excel Power Query tool. Please review it and let me know if it aligns with your expectations.

The raw data encompasses all datasets from BRC_branches, Plano de Distribuição de Doações, and Kobo Assessment.

The Master_dataset should be functional for querying, utilizing links with the Raw_data archive.

Master_dataset.xlsx
Raw_data.xlsx

[jeanne-crump]

Nice! Glad you were able to organize the columns from the Kobo assessment so clearly!

[JonathanGarro]

Thanks @adakita-01 and @jeanne-crump - what is the process for updating this as new submissions come in?

[adakita-01]

@JonathanGarro Just copy and/or updtate the data entries in the Raw_data.xlsx > Avaliação_Riscos_necessidades. The same sheet is also present in the Master_dataset.xlsx file. Both the raw_aval_riscos and master_aval_riscos dataframes have the same structure.

**Replace Data in the Master Sheet:**
    In the master dataset >> Data tab >> Get Data -> From Workbook. 
    Select the same Raw_data.xlsx file and the Avaliação_Riscos_necessidades sheet again.
  
**Schedule Refresh:**
    To ensure that the master dataset is always up to date, perhaps we can set up a scheduled refresh.
    Data tab >>  Queries & Connections >> Right click: properties
    Set the refresh options as needed, such as refreshing when the file is opened or setting a refresh interval.

By setting up the data connection and configuring the refresh options, you can ensure that the master dataset is automatically updated with the latest data from the raw dataset. This eliminates the need for manual data transfers and ensures consistency.

[JonathanGarro]

@adakita-01 The field is interested in turning this into a dashboard. Is that something you think you'd be interested in? We'd open up a separate task card here for it and could triage among other members if you're not interested or if PowerBI isn't in your skillset, but wanted to give you the first offer in case you were interested.

[adakita-01]

@JonathanGarro Sure! I could try it.

[adakita-01]

Hi @JonathanGarro ,

I noticed that the issue was moved to "On Hold". Nevertheless, here is the dashboard draft template. Sorry, I can't upload .pbix documents here, and the project Dropbox is full.

Thanks and cheers,
Carla