Process court configuration workbook [Goodin Config]

The following tasks will be completed by the Goodin Configuration team.

NOTE:

• Any record added by GAL has a "No Location ID" that is blank.

• AD* & AR* (any with an asterisk) - Do not remove these. These remain because the Courts need to be able to file on these.

• Never delete any "description with the word "obsolete" in it.

1. Locate the file to convert at - S:\development\E-filing\eFileIL filing fee configs\CMS interface rollout

   1. If the file is more than a day old, e-mail Eric/Tyler and ask if it is the most current file.

   2. Update the "Update Log, Summer 2017" file with the Reqst clearance date and the Tyler clears date

2. Run the configuration review tool.

• 1. Double click on the tyler-conversion file.

  2. Select the Goodin Master file (It will prompt the file name to select in the upper-left hand of the screen.)

  3. Open the Tyler Config. folder and select the County Master file. The conversion run begins. "All done" displays when it is complete.

     1. Look for error messages. They will appear at the bottom of the screen and will indicate if a county has more descriptions (duplicates) than the master contains. Ask Tony if errors are found.

     2. Rename the file by adding "GAL MODS" to the end of the file name.

     3. Move the converted file from the "Tyler Config." folder to the "CMS Interface Rollout" folder.

     4. Move the original file from the "Tyler Config." folder to the "Processed Prods." folder.

     5. Move the .txt file in the CMS Interface folder to the "Old" folder.

3. Worksheet Formatting - CT, FC, FF, CC_FC, CT_PT

• 1. Unmerge the cells

  2. Apply the formatting to the entire worksheet.

4. Case Category (CC)

   2. 1. It should only contain the 15 standard Illinois case types. If not, highlight any missing case types.

         1. Unmerge the cells > Apply the formatting to the entire worksheet.

5. Case Type (CT)

• • 1. Added 5/15/2018 by Tony: Assuming that the party type (PartyN) columns aren't already on the CT tab and there are CT_PT records, add the party types to the CT tab using the button on the Configuration tab. Then wipe the data rows (not the headers) on the CT_PT tab.

• 1. Sort by CaseCategoryReferenceCode, then Description.

  2. Modified 06/01/2018 by Tony: Visible column: most should wind up "Yes".

  3. Changed 11/25 by K8: Click in cell G2 and freeze panes (View > freeze panes > freeze panes).

  4. Highlight the "Reference Code" column > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK

     1. Added 11/19 by Tony: On the Allow Initial Filing column, set an orange highlighting conditional format for "NO" values

     2. Eliminated 5/15/2018 by Tony, since PTs are now right in the CT tab. FORMULA: =IF([@[Allow Initial Filings]]="YES",IFERROR(VLOOKUP(F2&"",CaseType_PartyType!C:C,1,FALSE),VLOOKUP(F2,CaseType_PartyType!C:C,1,FALSE)),"")

        1. =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)". (K8's isna tips: Highlight whole column. Nav to Cond'l Format/New Rule. After you type the open paren in the Format Value field, click column header. Now remove the $ and add the end paren. If it doesn't work, try hitting Save before you try it.)

        2. Double check "Reference Code" cell. > Label "GAL-CTPT chk"

  5. FORMULA: =AND(LEN(J2)>0,C2="NO")

• • • 1. Conditional formatting > Highlight Cell Rules > Equal to "TRUE"

      2. "GAL- is item w fee inactive?"

• • 1. Changed 05/31/2018 by Tony: FORMULA: =IF(LEN([@[Filing Fee Reference Code]])=0,"",VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:G,2, FALSE))

       1. 1. =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R). (K8's isna tips: Save before you try it. Click column header instead of row 2.)

• • • 1. "GAL- FF chk"

• 6. Added 12/1 by Kayte--In Row 2 of the CMS Code Column: FORMULA: =[@[Reference Code]]

• • • 1. Copy down thru the Reference Code Column.

• 7. Added 06/01/2018 by Tony: Highlight in orange any Filing Fee References which don't have the Goodin format.

6. Party Type (PT) Added 12/1 by Kayte--on the Description column, put a conditional format to spot duplicates

7. Filing Code (FC)

• • 1. Added 5/15/2018 by Tony: Check the case types. Assuming that the case category (CatN... the case types) columns aren't already on the FC tab and there are CC_FC records,

       1. Add the categories to the FC tab using the button on the Configuration tab.

       2. Wipe the data rows (not the headers) on the CC_FC tab.

       3. Check that there is a separate FC entry for each case type

       4. Check that each FC only has one case type (e.g. fix the F/OP problem), unless fees require subtype-level FCs (e.g. counter-petitions/claims)

• 1. Sort by Description

     1. Highlight the "Reference Code" column.

        1. From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK

     2. Highlight the "Visible" column.

• • • 1. From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Equal To "NO"

      2. Search for "NO" values in the "Visible" and highlight red.

• 2. Eliminated 5/15/2018 by Tony, since CC's are now right in the FC tab.

• • 1. FORMULA: =VLOOKUP(F2,CaseCategory_FilingCode!D:K,8,FALSE)

       1. =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".

       2. Double check in "Reference Code" cell.

       3. "GAL-CC_fr_CCFC".

    2. Changed 05/31/2018 by Tony: FORMULA: =IF(LEN([@[Filing Fee Reference Code]])=0,"",VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:G,2, FALSE))

       2. 1. =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".

          2. Double check in "Reference Code" cell.

• • • 1. "GAL- FF chk".

• 3. Changed by Tony 5/16: FORMULA: =IF(LEN([@[Filing Fee Reference Code]])>0,[@[Cat 1]]=IF(LEN([@[Filing Fee Reference Code]])>0,LEFT([@[Filing Fee Reference Code]],FIND("-",[@[Filing Fee Reference Code]])-1),""),"")

• • • 1. Conditional formatting > Highlight Cell Rules > Equal to "FALSE"

      2. "GAL-FF case type matches FC case type "

• 4. Scroll thru column U and look for any stopping over JJ's notes (notes that Tony added). If so, move Tony's notes to a blank column and notify him.

  5. Added 12/06 by Kayte: FORMULA=AND([@Visible]="NO",LEN([@[Filing Fee Reference Code]])>0)

• • 1. Conditional formatting > Highlight Cell Rules > Equal to "TRUE"

    2. "GAL- is item w fee inactive?"

• 6. Added 12/9 by Tony FORMULA=IF(LEN([@[Filing Fee Reference Code]])>0,VLOOKUP([@[Filing Fee Reference Code]],'Filing Component (COM)'!H:H,1,FALSE),"")

• • 1. "FF on COM"

• 7. Add 5/15/2018 by Tony: FORMULA=[@[Cat 1]]&[@[CMS Code]]

• • 1. "GAL-dup chk"

8. Filing Fee (FF)

• 1. Added 11/29/2017 by K8: Highlight the "Reference Code" column.

     1. 1. From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK

  2. Added 12/9 by Tony FORMULA =IF(LEFT([@Description],5)="(Misc",VLOOKUP([@[Reference Code]],'Filing Component (COM)'!H:H,1,FALSE),"")

• • 1. "GAL-Misc. COM chk"

• 3. Added 5/31/2018 by Tony: Delete any unused entries (those with Has References = NOT_USED)

  4. Filing Component (COM)

• • 1. Added 12/1/2017 by K8 FORMULA: =VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:H,3,FALSE)

    2. =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".

       1. "GAL- FF chk".

9. Case Category Filing Code (CCFC)- Note by Tony 5/17: THESE ARE UNNECESSARY NOW THAT THE CCs ARE INDICATED RIGHT ON THE FC TAB

• 1. FORMULA: =VLOOKUP([@[Filing Code Reference Code]],'Filing Code (FC)'!F:F,1,FALSE)

     1. 1. =isna($M2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".

        2. Double check in "Filing Code Reference Code" cell.

        3. "GAL-FC chk".

  2. Changed by Tony 11/16 p.m.: MUST BE IN COLUMN K!!!! FORMULA: =[@[Case Category Reference Code]]

• • • 1. "GAL-CC_redisplay"

• 3. Added by Tony 01/01/2018: Set a Conditional Format for uniqueness on col. D, since FCs are supposed to be one-to-one with case types)

10. CaseType_PartyType- Note by Tony 5/9: THESE ARE UNNECESSARY NOW THAT THE PTs ARE INDICATED RIGHT ON THE CT TAB

• 1. FORMULA =IF(ISNA(VLOOKUP([@[Case Type Reference Code]],'Case Type (CT)'!F:G,2,FALSE)),VLOOKUP([@[Case Type Reference Code]],'Case Type (CT)'!D:G,4,FALSE),VLOOKUP([@[Case Type Reference Code]],'Case Type (CT)'!F:G,2,FALSE))

     1. =isna($L2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".

     2. "GAL-CT chk".

  2. Highlight "Display Order" column > Conditional formatting > Highlight Cell Rules > Equal to "0". If any zero's return > Change the display order on "P's" to "1" and "D's" to "2"

• • 1. Party Type Reference Code > Filter "P's". They should have a "display order" of "1". The "D's" should be "2s". Change any as needed.

• 3. Changed by Tony 11/17 p.m.: FORMULA =C2&D2

• • 1. Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK

    2. "GAL-uniq_chk"

11. Filing Code_Filing Component Tab

• • 1. Added by K8 12/12 at 12:34 p.m. Clear this tab of all of its contents

       1. Do Cntl/A to select all of the cells. Then right-click and do Clear Contents.

• • E-Mail Tony and Kayte the file has been configured. Indicate any highlighted (duplicate) items found on the Filing Code tab - Reference Code column.

  • Update the "Update Log, Summer 2017" log with the date the file was configured.