Summary

Recipients of CARES act funding for Covid19 related work must report closed case information to LSC quarterly beginning with the second quarter of 2020. Use LegalServer reports to export the spreadsheet of closed cases. With minor changes, this can be uploaded into the LSC grant reporting portal.

Outreach totals are to be reported quarterly, and agencies can optionally report total new opened cases as well. LegalServer reports can tally the opened case totals needed for the LSC CARES reporting.

Tagging Cases and/or Timeslips as COVID-19 Related

Please see our May 2020 community call on this topic on YouTube here: https://youtu.be/63-PGNSr5KA

Closed Cases Report for LSC CARES COVID-19 - Overview

Timing & Submission

This report is quarterly and not cumulative.

You’ll report cases by uploading a spreadsheet to the LSC grant portal, currently GrantEase.

Report Filters

Closed during the reporting period

CSR Eligible

Related to COVID-19

(the field YOUR agency uses for this may vary, but you may use the Disasters field, which is available on every LegalServer site.)

Case Report: 8 Columns of Data Needed

LSC is asking for these 8 columns in the spreadsheet, in this exact order, with this exact naming convention

Funding Source Code LSC COVID, Other LSC, or Non-LSC, transformed into 001, 002, 003

Staffing Code Staff/PAI, transformed as S or P

Problem Code LPC, like 63

Closing Code A-L

Gender Code M, W, O, U

Veteran Status Code N, V, U

Ethnicity Code A, B, H, N, O, W, U

Year of Birth YYYY

Quirks

• • • Note that the columns must be in the order above.

    • The columns in the spreadsheet you submit must have the exact header names designated by LSC.

    • What LSC is calling Ethnicity is probably Race in your system.

    • It is possible, using the Transformer and the logic tools LegalServer has in report column headers, to produce a report exactly as needed by LSC with no further manipulation.

• • Unless or until you transform each of the data items using the Transformer and configure the logic tools we've added to columns in reports, you will need to use find/replace in your spreadsheet program once you export the data. For instance, find/replace ‘Male’ with M, and in the Veteran column, replace ‘Yes’ with ‘V’.

Closed Case Reports for COVID-19 Reporting - Build Help

Planning Your Reports

We suggest making two reports. One for error checking and one to export and submit. Examples are attached to this article.

CARES Covid19 Quarterly - Closed - Non-Transformed - Error Checking

CARES Covid19 Quarterly - Closed - Non-Transformed - For Submission

Find examples of these two reports here, in the Example Reports list.

You can upload these to your site and use them, if you use the Disasters field to identify COVID-19 cases.

The Error Checking versions will have the convenient Matter/Case ID hyperlink in them, which you don’t submit to LSC.

If you decide to use LegalServer’s transformer to save time using find/replace in the exported spreadsheet, you can use these reports as a basis to build two more reports in the future with less find/replace action needed after export:

CARES Covid19 Quarterly - Closed - Transformed - Error Checking

CARES Covid19 Quarterly - Closed - Transformed - For Submission

Build a Simple Non-Transformed Error Checking Report

Use a basic cases report and Save As something like: CARES Covid19 Quarterly - Closed - Non-Transformed - Error Checking

Remove or hide all the columns except the required columns and Matter/Case ID#

Before you change the heading names, the columns will look like the list below, since these are the most likely LegalServer source fields you’ll use:

Matter/Case ID#

and

Funding Code

PAI Case?

Legal Problem Code

Close Reason

Gender

Veteran

Race

Date of Birth

Report Filters

Add a filter: Date Closed

Case Disposition: Closed

CSR Eligible: True/Yes

Disasters: Is any of Coronavirus

(note your agency may have chosen a different way to tag cases as Coronavirus- related)

Build Another Similar Report That You'll Adjust for LSC Covid Reporting

Use a basic cases report or another report you’ve already created and Save As something like:

'CARES Covid19 Quarterly - Closed - Non-Transformed - For Submission'

Eliminate all the columns from the report except the required columns

Funding Source Code LSC COVID, Other LSC, or Non-LSC, transformed into 001, 002, 003

Staffing Code Staff/PAI, transformed as S or P

Problem Code LPC, like 63

Closing Code A-L

Gender Code M, W, O, U

Veteran Status Code N, V, U

Ethnicity Code A, B, H, N, O, W, U

Year of Birth YYYY

Filters for Your CARES Covid Report to LSC

Date Closed: (pick a quarter, or Any Selected). Leave viewer-modifiable)

Case Disposition: Is any of Closed

CSR Eligible: True/Yes

Manipulate Columns to Match LSC Template Requirements

Hide any columns other than the 8 columns below

Order the columns:

Funding Source Code

Staffing Code

Problem Code

Closing Code

Gender Code

Veteran Status Code

Ethnicity Code

Year of Birth

Rename the columns if needed

Reformat the columns if needed

Go to Date of Birth column header and change the format to: Year Only

Go to Legal Problem Code and change the format to: First Word

Go to Staffing Code and change the Yes/True Value and the No/False Value

Go to the Veteran Status Code column, edit it, and change the True , False and Null values, like this:

Optional: Use Find/Replace To Conform Values

Export your results to .xls, and find/replace values as needed.

If you don't use the Transformer and the column options tools to conform the report's output, you'll need to use a more manual method of find/replace in your downloaded report spreadsheet. For instance, unless you have added a custom lookup field to your grants to track this data point, all values in the Funding Source column should be changed to 001, 002, or 003 depending on whether the funding code is LSC Covid, LSC (something else), or non-LSC. LegalServer can’t automate that for you, since we don’t know what you’ve named your LSC grants, and those are not universal.

Essential Step! Modify Your Exported Report

In your spreadsheet editing software, take out all top rows other than the column names

Down at the bottom left of the screen, name the individual sheet in this workbook: Data Entry

Advanced: Use the Transformer Where Needed

You do NOT need to use the transformer if you don’t want to. You can export your data to .xls and find/replace values in your spreadsheet program, then upload your spreadsheet to the LSC portal.

What is the Transformer?

See our help information here:

http://help.legalserver.org/home/reports/lookup-transform-and-export-index

You do NOT need to use the transformer if you don’t want to. You can export your data to .xls and find/replace values in your spreadsheet program, then upload your spreadsheet to the LSC portal.

Using the Lookup Transformer For COVID19 Reporting - Webinar Recording

https://us02web.zoom.us/rec/share/55JOH-6zrD1OQ4nu5VyBHaURAb75eaa8h3RL__UImEmoSGvX1oIw3RM9PCZQxHGn

What data do I need to Transform / Conform for LSC’s Report?

These values can be transformed automatically with the LegalServer Transformer

Staffing Code Staff/PAI, must be reported as S or P to LSC

Gender Code M, W, O, U are the only values LSC accepts

Ethnicity Code A, B, H, N, O, W, U are the only values LSC accepts

These columns cannot be transformed automatically because they are not lookups

Funding Source Code 001,002,003 (these can’t be transformed, currently)

Veteran Status Code N, V, U (no need to transform- see boolean value options in the report header)

Opened Cases Report for LSC CARES - Overview

This report is optional as of 7/2020

Timing & Submission

This report is quarterly, and not cumulative. This is for cases newly opened in the quarter.

Filters

Opened in the reporting quarter

LSC Eligible = Yes/True

Disasters: Is any of Coronavirus

Opened Cases Report Columns

Funding Source Code

Staffing Code

Problem Code

Closing Code this will be blank

Gender Code

Veteran Status Code

Ethnicity Code

Year of Birth

Quirks

Note that the columns in the report submitted must be in the order above, with conforming headers

What LSC is calling Ethnicity is probably Race in your system

Outreaches Report for LSC CARES

Outreach Report Timing & Submission

This report is quarterly, and not cumulative. There is no spreadsheet to upload. Just run the report and put the total number of outreaches and the total number of attendees in the appropriate boxes on the LSC portal.

Outreach Report Filters

Outreach date is in the reporting quarter.

Outreach Report Columns

Number of Outreaches

Number of attendees actual

Number of attendees estimated

Delivering Your LegalServer CARES Reports to LSC

Export to XLS from LegalServer

When your report is ready to submit, run the report and click the Excel icon, which you will find to the right of the screen. This will download the tabular data to your computer in .xls format

GrantEase Portal

LSC staff will tell you where to report. As of July, 2020, the reporting portal is here:

https://www.lsc.gov/grants-grantee-resources/our-grant-programs/basic-field-grant/grantease-training

Uploading to LSC's Portal

LSC says you cannot re-submit a quarterly report to LSC unless they determine there are errors! Triple check your data spreadsheet before uploading.

When you upload to LSC, their portal may give you error messages. If this happens, go back to view your report in LegalServer and figure out which case you need to update.

You can edit the spreadsheet you have generated before uploading to LSC if necessary.