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-test 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.
    3. 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.
  4. Case Type (CT)
    1. Sort by CaseCategoryReferenceCode, then Description.  
    2. Click in cell E2 and freeze panes (View > freeze panes > freeze panes). 
    3. Scroll over to around columns R thru X.In the "Visible" column, locate the "No's). Check to see if they have anything in columns R thru  X that also have a plus (+). If so, highlight the entire row.
    4. Locate CMS Codes that don't look like our numeric CMS codes (any code that starts alphabetically). Example: CV-L2
      1. Highlight them and the reference filing fee code in "yellow".
        1. Leave the ones that have an asterisk*. Example: L*
    5. Highlight the "Reference Code" column > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
    6. Added 11/19 by Tony: On the Allow Initial Filing column, set an orange highlighting conditional format for "NO" values
    7. 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"  
    8. FORMULA:  =AND(LEN(J2)>0,C2="NO")
        1. Conditional formatting > Highlight Cell Rules > Equal to "TRUE"
        2. "GAL- is item w fee inactive?"
    9. 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"
  5. 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.
  6. 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"
  7. 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"
  • 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