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.
- Locate the file to convert at - S:\development\E-filing\eFileIL filing fee configs\CMS interface rollout
- If the file is more than a day old, e-mail Eric/Tyler and ask if it is the most current file.
- Update the "Update Log, Summer 2017" file with the Reqst clearance date and the Tyler clears date
- Run the configuration review tool.
- Double click on the tyler-conversion file.
- Select the Goodin Master file (It will prompt the file name to select in the upper-left hand of the screen.)
- Open the Tyler Config. folder and select the County Master file. The conversion run begins. "All done" displays when it is complete.
- 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.
- Rename the file by adding "GAL MODS" to the end of the file name.
- Move the converted file from the "Tyler Config." folder to the "CMS Interface Rollout" folder.
- Move the original file from the "Tyler Config." folder to the "Processed Prods." folder.
- Move the .txt file in the CMS Interface folder to the "Old" folder.
- Worksheet Formatting - CT, FC, FF, CC_FC, CT_PT
- Unmerge the cells
- Apply the formatting to the entire worksheet.
- Case Category (CC)
- It should only contain the 15 standard Illinois case types. If not, highlight any missing case types.
- Unmerge the cells > Apply the formatting to the entire worksheet.
- Case Type (CT)
- 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.
- Sort by
CaseCategoryReferenceCode, then Description.
- Modified 06/01/2018 by Tony: Visible column: most should wind up "Yes".
- Changed 11/25 by K8: Click in cell G2 and freeze panes (View > freeze panes > freeze panes).
- Highlight the "Reference Code" column > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
- Added 11/19 by Tony: On the Allow Initial Filing column, set an orange highlighting conditional format for "NO" values
- 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)),"")
- =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.)
- Double check "Reference Code" cell. > Label "GAL-CTPT chk"
- FORMULA: =AND(LEN(J2)>0,C2="NO")
- Conditional formatting > Highlight Cell Rules > Equal to "TRUE"
- "GAL- is item w fee inactive?"
- 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))
- =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.)
- "GAL- FF chk"
- Added 12/1 by Kayte--In Row 2 of the CMS Code Column: FORMULA: =[@[Reference Code]]
- Copy down thru the Reference Code Column.
- Added 06/01/2018 by Tony: Highlight in orange any Filing Fee References which don't have the Goodin format.
- Party Type (PT) Added 12/1 by Kayte--on the Description column, put a conditional format to spot duplicates
- Filing Code (FC)
- 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,
- Add the categories to the FC tab using
the button on the Configuration tab.
- Wipe the data rows (not the headers) on the CC_FC tab.
- Check that there is a separate FC entry for each case type
- 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)
- Sort by Description
- Highlight the "Reference Code" column.
- From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
- Highlight the "Visible" column.
- From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Equal To "NO"
- Search for "NO" values in the "Visible" and highlight red.
- Eliminated 5/15/2018 by Tony, since CC's are now right in the FC tab.
- FORMULA: =VLOOKUP(F2,CaseCategory_FilingCode!D:K,8,FALSE)
- =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".
- Double check in "Reference Code" cell.
- "GAL-CC_fr_CCFC".
- 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))
- =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".
- Double check in "Reference Code" cell.
- "GAL- FF chk".
- 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),""),"")
- Conditional formatting > Highlight Cell Rules > Equal to "FALSE"
- "GAL-FF case type matches FC case type "
- 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.
- Added 12/06 by Kayte: FORMULA=AND([@Visible]="NO",LEN([@[Filing Fee Reference Code]])>0)
- Conditional formatting > Highlight Cell Rules > Equal to "TRUE"
- "GAL- is item w fee inactive?"
- 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),"")
- "FF on COM"
- Add 5/15/2018 by Tony: FORMULA=[@[Cat 1]]&[@[CMS Code]]
- "GAL-dup chk"
- Filing Fee (FF)
- Added 11/29/2017 by K8: Highlight the "Reference Code" column.
- From the toolbar menu, select > Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
- Added 12/9 by Tony FORMULA =IF(LEFT([@Description],5)="(Misc",VLOOKUP([@[Reference Code]],'Filing Component (COM)'!H:H,1,FALSE),"")
- "GAL-Misc. COM chk"
- Added 5/31/2018 by Tony: Delete any unused entries (those with Has References = NOT_USED)
- Filing Component (COM)
- Added 12/1/2017 by K8 FORMULA: =VLOOKUP([@[Filing Fee Reference Code]],'Filing Fee (FF)'!F:H,3,FALSE)
- =isna($Y2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".
- "GAL- FF chk".
- Case Category Filing Code (CCFC)- Note by Tony 5/17: THESE ARE UNNECESSARY NOW THAT THE CCs ARE INDICATED RIGHT ON THE FC TAB
- FORMULA: =VLOOKUP([@[Filing Code Reference Code]],'Filing
Code (FC)'!F:F,1,FALSE)
- =isna($M2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".
- Double check in "Filing Code Reference Code" cell.
- "GAL-FC chk".
- Changed by Tony 11/16 p.m.: MUST BE IN COLUMN K!!!! FORMULA: =[@[Case Category Reference Code]]
- "GAL-CC_redisplay"
- 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)
- CaseType_PartyType- Note by Tony 5/9: THESE ARE UNNECESSARY NOW THAT THE PTs ARE INDICATED RIGHT ON THE CT TAB
- 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))
- =isna($L2) > Apply "red" highlight formatting. > Copy formula > Paste "formatting (R)".
- "GAL-CT chk".
- 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"
- 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.
- Changed by Tony 11/17 p.m.: FORMULA =C2&D2
- Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK
- "GAL-uniq_chk"
- Filing Code_Filing Component Tab
- Added by K8 12/12 at 12:34 p.m. Clear this tab of all of its contents
- 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.
|