Spreadsheet: 850313d1700672966-formula-to-combine-fields-into-1-cell-operation-holiday-2023-application-11102023-responses-2
Sheet: PROCESS FLOW

STEP NO. | OWNER | TASK/PROCESS | FREQUENCY
1 | Theresa | Assign each new applicant a FAMILY ID Number | As Needed
1 | [NAME] | Verify all required documents (i.e. ID) are attached to the application, verify the WISH LIST does not contain any items that violate our policy (i.e. CASH, but request for back rent help is okay, gift cards OK). DO NOT go into great detail to verify the documents, "skim over" them to see that they were attached. If not, HIGHLIGHT RED, enter REJECTION DATE, enter [NAME]). If the file DOES NOT have any errors (missing information, wish list violations), turn the color ORANGE to let other team members know that once they finalize the bios, the row can be turned YELLOW for data refresh upload. | As Needed
1 | Theresa | Mark the row APPROVED by entering an APPROVED DATE in column D | As Needed
1 | Theresa | Complete the FINAL BIO field. Read the user entry bio along with any special circumstance information the applicant has provided. BRIEFLY SUMMARIZE their request in no more than 4 sentences maximum. This will automatically generate the ADOPTION BIO field used for the DATA TRANSFER PROCESS. There is a formula that runs (COLUMN G) that will gather all of the information from multiple cells to create a final data transfer text. | As Needed
1 | Theresa | Shade completed rows YELLOW (awaiting data transfer) | As Needed
1 | [NAME] | Open the macro using [NAME] on the toolbar (EXTENSIONS > SHEETGO). Run macros for both NJ and CT, which will transfer all of the Google sheet data into an Excel app (NOTE: the macro DOES NOT TRANSFER FROM EXCEL BACK TO GOOGLE SHEETS AND IS A ONE-WAY TRANSFER). | As Needed
1 | [NAME] | Open the Website Transfer file after the macro has been run. Copy the following data onto a NEW DATA REFRESH WORKSHEET WITH TODAY'S DATE (we process at 4 pm daily; if a heavy load, we process twice per day at 12 noon and 4 pm, in this instance each data refresh will end with today's date and -1, -2, ect. For example if you were to run 2 data refreshes for 11262022, the first would end with 11262022-1, and the second would end with 11262022-2 and so on. You can make as many data refreshes as needed in the event there is a high volume of applications to upload as it is MORE IMPORTANT THAT THE WEBSITE ACCURATELY REFLECTS OPEN ADOPTIONS AS DONORS ARE EAGERLY AWAITING THE UPDATES THROUGHOUT THE DAY. FIELDS TO COPY ONTO THE DATA REFRESH SHEET: 1. FAMILY ID NO 2. ADOPTION BIO ** NOTE: Do not forget to ensure the quantity availabe is set to 1; do not forget to put the category as either NJ or CT so that when it is transfered to the website it accurately is placed onto the correct category tab | As Needed
1 | [NAME] | Save the data refresh file as an excel and a .csv file. The .csv file is what you will use to upload onto the website. | As Needed
1 | [NAME] | Access iPage using the credentials provided to you for operationholiday.net | As Needed
1 | [NAME] | Go to the STORE tab and UPLOAD THE NEW DATA REFRESH (.CSV) FILE. You will notice that the file count on the right side of the screen has now increased. You can only ADD TO THE PRODUCT LISTINGS, YOU CANNOT OVERWRITE. Therefore, you must take extra caution to ensure your data refresh file is correct. | As Needed
1 | [NAME] | Once the file has uploaded, you must manually go into each of the products that were just uploaded and take the first sentence of the adoption bio, copy and paste it into the EXCERPT FIELD. This will force the product formatting to accept "hard returns" and properly space out the layout. | As Needed
1 | [NAME] | Verify the product(s) appear properly formatted on the website. If so, click PUBLISH to save all information. | As Needed
1 | [NAME] | Go back onto the Google Sheets (not the Excel app) and mark the column for the DATA REFRESH DATE | As Needed
1 | [NAME] | Enter the status for the DATA REFRESH STATUS (i.e. LISTED -- new uploads; ADOPTED for new adoptions) | As Needed
1 | [NAME] | When a notification comes in for a new order and/or the stock for an item is now out (which means the 1 product - FAMILY ID has been adopted, you must IMMEDIATELY TAKE IT OUT OF INVENTORY. To do this, go into the STORE, PRODUCTS and DELETE THE PRODUCT. The sale information will remain under ORDERS. On the Google Sheets mark the FAMILY ID as ADOPTED with the DATE | As Needed
1 | [NAME] | Once a family has been adopted, go into the ORDERS and take the donor's full information, copy and paste it onto the Google sheet in the corresponding column(s). | As Needed
1 | [NAME] | Send the donor the CONFIRMATION OF ADOPTION (DONOR) email, replacing the FAMILY ID in the email subject line and the applicant's main point of contact name, address, phone number and email in the body of the template. Send this to the donor only. | As Needed
1 | [NAME] | Send the donor a text advising them "Seasons Greetings! This text is to confirm that we have received your pledge to adopt a family(ies). Please check your email inbox/junk/spam for the complete family identification information and reply email or text that you have received this information. Regards, Operation Holiday® Team | As Needed
1 | [NAME] | Send the applicant the email template [NAME] OF ADOPTION. You should always as a practice follow-up with a text since our emails commonly go into spam/junk mail. "Dear Operation Holiday Applicant. YOU HAVE BEEN ADOPTED. Please check your email inbox/junk/spam for additional detaiils. Regards, Operation Holiday® Team | As Needed
1 | [NAME] | Update the Google sheet with the date of the donor notification email. Be sure to follow up with all donors if you have not heard a response from them. If they do not respond within 48 hours, RELIST THE FAMILY onto the website. | As Needed