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)
      1. It should only contain the 15 standard Illinois case types. If not, highlight any missing case types.
      2. Unmerge the cells > Apply the formatting to the entire worksheet.
  5. Case Type (CT)
    1. Sort by CaseCategoryReferenceCode, then Description.  
    2. Visible column: highlight any "No's" that also have +AC and/or +FIXCMS around columns AE - AG. 
    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
    5. Added 11/19 by Tony: On the Allow Initial Filing column, set an orange highlighting conditional format for "NO" values
    6. 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"  
    7. FORMULA:  =AND(LEN(J2)>0,C2="NO")
        1. Conditional formatting > Highlight Cell Rules > Equal to "TRUE"
        2. "GAL- is item w fee inactive?"
    8. FORMULA:  =IF(LEN([@[Filing Fee Reference Code]])=0,"",VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:F,1, FALSE)) 
        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.)
        2.  "GAL- FF chk"
    9. Added 12/1 by Kayte--In Row 2 of the CMS Code Column: FORMULA:  =[@[Reference Code]]
        1. Copy down thru the Reference Code Column.
  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. Sort by Description. 
    2. Highlight the "Reference Code" column.
      1. From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
    3. 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.
    4. 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".
    5. FORMULA:  =IF(LEN([@[Filing Fee Reference Code]])=0,"",VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:F,1, FALSE))
        1. =isna($Y2) > Apply "red" highlight formatting.  > Copy formula > Paste "formatting (R)".
        2. Double check in "Reference Code" cell. 
        3. "GAL- FF chk".
    6. Changed by Tony 11/16 p.m.: FORMULA:  =IF(LEN([@[Filing Fee Reference Code]])>0,[@[GAL-CC_fr_CCFC]]=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 "
    7. 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.
    8. 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?"
    9. 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"
  8.  Filing Fee (FF)  
    1. Added 11/29/2017 by K8:  Highlight the "Reference Code" column.
      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"
  9. Filing Component (COM) 
    1. Added 12/1/2017 by K8 FORMULA: =VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:H,3,FALSE)
      1. =isna($Y2) > Apply "red" highlight formatting.  > Copy formula > Paste "formatting (R)".
      2. "GAL- FF chk".
  10. Case Category Filing Code (CCFC)
    1. FORMULA:    =VLOOKUP([@[Filing Code Reference Code]],'Filing Code (FC)'!F:F,1,FALSE)       
        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)
  11. CaseType_PartyType
    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"
  12. 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.
Subpages (1): Client Email
Comments