Coord Walkthrough:

The first thing you need to do is read this: If you screw this up, you can totally and completely hose your data. Do *not* do this in bulk the first few times. Make two or three dummy records, experiment until you are comfortable. and *keep a copy of your original .csv export file*, so you can restore if needed

General Background to keep in mind

I am writing this without validating with the Foundation IT staff, although I will be following up to see how accurate my guesses are after I publish this.

Somewhere in the Internet, there is a bunch of servers. One or more of them holds some databases that house all the data we use. We see a subset of that associated with our campaign, primarily associated with the last field on the exports: Coordinator ID . This is the unique identifier for your campaign, and allows you to see the applications and records that are relevant to you without seeing the hundreds of thousands of similar records. The full data set would be overwhelming, and you also don't have any need to see the data for other campaigns

Each individual record also have a unique identifier of some type that you're not going to be able to edit, but is used to tell one record from another. There are four Bulk Update options; these are the relevant 'keys' for each of them

Toy Drop Site: Request ID
Family Application: Request ID
Agency Application: Request ID
Distribution Event: Event ID

If you upload a record with an existing number, it will update that record

If you upload a record with a 0, it will create a new record

Let's go through the various options

Bulk Update: Toy Drop Sites

Whether you're updating existing records or creating new ones, the first thing you need to do is get a copy of the raw data:

LCO Site > Manage Online Forms and Requests > Become a Toy Drop Site > Review Form Submissions

This will pull up a list of search options you can use to narrow your search. In my case, I've got a lot of records. Like, a lot

Ugh. Let's select just 2024 to make the file more manageable

Much better. Click 'Export Results to Excel. This will create a file: dashboard-become-drop-site-form.csv

Open it up in Excel. You'll see a bunch of data

Now, before we go into details on what we can do, do not, under any circumstances, change the header column

The upload tool is dependent on the columns appearing in a specific sequence with the header exactly as it is. Your best case if you mess with this is a failed upload. The second worst is you completely hose your campaign's application data

The worst worst is you screw up *everyone else's data*. This tool is *not* hardened like something that has a million users, and it would not surprise me if there was an unknown defect that could let someone accidentally enter a record for Bobby Tables and mess things up. I'm not going to try finding out either. It's your responsibility to do this correctly or not do it at all

So, let's talk about our two cases:

Updating existing records

So, we have our file of records. What can we do

Well, the first thing we want to do is get rid of records we're not interested in touching. Let's say for the sake of argument we only cared about applications with a Status of "transferred". We would sort on column B, status, to find just the records with the appropriate status, and delete the rest. If there are no changes to the other records, there's no reason to upload them later, so delete them from the file to avoid any issues

The assumption is that you're doing this if you want to update many records at once. Otherwise you would update an individual record in the UI rather than going through this process

What are the typically changed fields?

Column B: Status - there is a list of potential status...

... most of which PACSHT will never use for drop sites. These are for our *convenience*; there's no point in tracking sites with this level of grain if it isn't adding any value to the campaign.

However, if we wanted to move a bunch into a "scheduled" or "completed", this would be where to do it. Note that these are case sensitive, and despite what the list above has, they're *all* lower case. I think. I haven't messed with some of the Box/Toy related ones, as those are new to me

Column N: Make Public / Column Y: Show on Map

These fields will determine which show on the public list (assuming it's set as active) and/or the Toys for Tots drop site map

Default is FALSE. Set to TRUE to make visible

There are other fields that can be updated, but for my campaign I generally don't need to do that in bulk for drop sites

What I do need to do is...

Creating New Records

At a high level, creating a new record from scratch requires figuring out what to put in each of the 38 separate attribute columns.

Bleh.

90% of the time, what I am doing is taking an entry from the previous year and applying it to this year. So let's take an example

First I download the previous year's extract

... then filter it to the records I want to replicate for this year. Most of the time this is keyed to the email address of the contact from column L

These are the fields that will need to be updated to create a new record, in order:

Column A: Request Date - Set as today's date

Column B: Status - Set as pending

Column N: Make Public - Set as FALSE to make it hidden, and TRUE to make it visible

Column O: County - You *only* need to change this if there have been any format updates between years, which happens occasionally.

It's important that the new records reflect the *current* format, no matter what the historic format might have been

Column V: Start Collection Date - Set for this year. I usually default to Oct 1st - 10/1/202x

Column W: Date Pickup - ditto. Needs to be this year, within the collection window for your campaign

Column AC: Comments - I usually wipe whatever is in here as the historic comment likely isn't useful for the current year

Column AK: Request ID - this must be set at 0 to create a new record

Save the file, then go to Uploading a file

Bulk Update: Family Applications

I never create new family applications, I only update existing ones. Here's the process, and here's why I do so

LCO Site > Manage Online Forms and Requests > Family Toy Request Form > Review Family Submissions

This will pull up a list of search options you can use to narrow your search. While there are many fields, I only usually need the top three in this case

These are used to narrow the search to the relevant portion

Status: Useful to only pull out your Pending applications

Year: Set to the current year

Filter by Distribution Event: Narrows by Distribution Event if needed

For now, let's just filter by Year

(I'll have a lot more before the campaign ends)

Open the file. Note that I've cleared out some data in this case because our family and agency applicant data shouldn't be visible, even in a FAQ

Ok, I've got a file of families. What am I doing with it?

Two major things:

Changing the Status field, column C

There are several possible values that will be used during the campaign

Important note: This process should not be used to disapprove an application. Each application that is disapproved needs a disapproval reason, and it will generate an email to the applicant when it is disapproved. The upload bypasses these steps, which is not acceptable - applicants that are disapproved need to understand they are, so they have time to seek assistance elsewhere.

Updating the Distribution ID, column AF

This is a five digit number that will be used to group applications into Distributions - the time and place the gifts will be picked up

More details on both of these will be the relevant part of the Family App walkthrough

Save the file, then go to Uploading a file

Bulk Update: Agency Applications

As with family applications, I rarely use this process to create new records

LCO Site > Manage Online Forms and Requests > Agency Toy Request Form > Review Agency Submissions

Why do we need to edit an Agency application?

The primary need is to update the application with the final requested number of children.

Starting on column Z, there will be entries like so

Boys Newborn to 2 years max 50 per gender
Girls Newborn to 2 years max 50 per gender
Boys 3 to 5 years max 150 per gender

etc

The final agreed upon numbers should be updated in the appropriate cells.

Save the file, then go to Uploading a file

Distribution Events

LCO Site > Manage Online Forms and Requests > Toy Application Tools > Distribution Events and Dates

There's no filtering criteria here; just hit Export Results to Excel

It'll start like this

but there are a lot of details in here

I use this almost exclusively to create events in bulk, filling in the details later

Event ID - set as 0 for new records

Event Title - Make this descriptive, as it will be going out to the applicants

Date - self explanatory. MM/DD./YYYY

Time - ditto. Time window of the distribution event

Details - leave blank for now. You will want to fill in the details for each event manually

Slots - 0

Coordinator ID - whatever was in the file you exported

Save and upload

Uploading a file

Ok, you've updated the file, now what?

Save the file as Excel Workbook
Go to Manage Online Forms > Bulk Import for the appropriate section
Read the damn disclaimer: READ THE INSTRUCTIONS BELOW COMPLETELY BEFORE ATTEMPTING. IF DONE INCORRECTLY YOU RISK CORRUPTING OR LOSING YOUR DATA.
- If this is your first time doing this, *read and understand* this stuff
Scroll to the bottom
Select your saved file
Take a deep breath
Hit the red "Import" button

Boom, done

It didn't work. What do?

Depends on the error. Here are some examples

Formatting issue

This error occurs when the data format was lost on a date column. The system was expecting to see 8/27/2024, but instead saw 45531

How to fix:

Download a fresh extract
Open the new extract in Excel
Copy a row
Go to the original extract
Right click Paste Special, select the option at the bottom
Paste Special Formats

Then re-upload the file

"not a valid name"

I *think* the issue here is that the system is expecting to see specific values as the name of the tab the data is on. The system is looking for whatever comes out of the export:

Drop Site: dashboard-become-drop-site-form
Agency: dashboard-toy-request-form-expo
Family: dashboard-family-requests-form-
Distributions: export-distribution-events

Easiest fix: Create a fresh export; copy and paste values into the new sheet, save and import the new one

Unexpected Value

This seems to happen when the value that is placed in a cell does not match the expected values for a drop down.

Easy fix: put an expected value in

Zero records uploaded

Generally there are three causes here:

Coordinator ID is not your own. The system will not allow you to make updates for another campaign that way
Field is blank when a value is expected
You tried to load an empty file

Page updated

Google Sites

Report abuse