Title Line 1 - Florida Department of Financial Services
Contents
1. Filter Y JZ Advanced Columns Duplicates amp Get External Data Connections Sort amp Filter Data Tools Outline x 1 G Se B C D E F G H J K L M N O p Q R 5 ica 18 M 4 gt Sheetl Sheet2 Sheet3 2 Ready Num Lock b Click the Data tab click From Text locate extract file using the Import Text File browser e g Merge OLO Number double click the file Import Text File Look in E Desktop 3 My Documents esed My Computer Documents my Network Places 480000 5 i Shortcut to MergeFiles on dfsnt amp vintdOo1 Desktop My Documents My Computer Network Places Files of type Text Files m Cancel 12 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide c Once the Text Import Wizard appears click the Delimited button and make sure Start import a row is set at 1 and File origin is Windows ANSI click Next Text Import Wizard Step 1 of 3 The Text Wizard has determined that your data is Fixed Width If this is correct choose Next or choose the data type that best describes your data Original data type Choose the file type that best describes your data Characters such as commas or tabs separate each field Fixed width Fields are aligned in columns w2. 768 88 Formatting Table Styles Ed Format e Filter 7 Select Internet SE k Alignment Number Styles Cells Editing y Documen Internet Explorer y hy E mail My Recent ler Microsoft Office 5 Documents E F T H T i J K L T M I N E Microsoft Office 49 Computer Race To The Top Services A 90102010 6 30 2014 2 Excel 2007 My Network 1 2 Microsoft Office Word 2007 tivities Control Panel ho is Solitaire gned in Set Pr Provides options for you to customize the appearance a Microsoft Office Acces and functionality of your computer add or remove PowerPoint 2007 e Connd Programs and set up network connections and user Microsoft Office accounts Visio 2003 ga Printers and Faxes Face To The Top Servioes A 31112010 6 30 2014 da J bluzone 1 ther 9 Help and Support dor to Search All Programs gt O Run BD Log off Shut Down Average 1729975385 Count 73 Min O 480000 Sum 2248968 CI TQ Ct ustat DTE LLL 11 52 Tuesday El Microsoft Exce Microsoft Pow SCRS File For f Inbox Micro zr 1 10 2012 2 Double click the Regional and Language Options icon Control Panel File Edit View Favorites Tools Help Qs Q Search Folders iiv Address 2 Control Panel v amp Accessibility Options 9 Regiona
3. Excel Workbook 22 SK Delete 21 G aa Save the file as an Excel Workbook E 48 68 Conditional Formatas Cell m Sort amp Find amp Paste New Va Open Formatting Table Styles gt us ormat 2 Filter Select ye 7 Slide fx a Excel Macro Enabled Workbook Number E Styles Cells Editing Clipboard S 1 Save Save the workbook in the XML based and Slides Qutli b macro enabled file format a7 so ita noite na F s E Save As T i i i E A Save the workbook in a binary file format 480000 Race To The Top optimized for fast loading and saving bward of their share of 35 other El Print b as Excel 97 2003 Workbook 480000 Race To The Top d HI Save a copy of the workbook that is fully award of their share of 351 Other compatible with Excel 97 2003 480000 Race To The Top award of their share of 35 Other Y Prepare ES OpenDocument Spreadsheet 480000 Race To The Top 1 Save the workbook in the Open award of their share of 351 Other d Document Format 480000 Replacement Kitchen Hood Exhaust Sys Rhode Bldg Sen b PDF or XPS EE Cost Reimbursement n m x endi e eme e E Publish a copy of the workbook as a PDF Other ne ng Mean scemen r XPS Publish b or XPS file 480000 Building Renovation DOE Project Number 24027010 0 480000 Close award of their share of 5351 Other 480000 award of their sha
4. Agency Contact form is available on our website http www myfloridacfo com aadir statewidecont ractreporting htm Form requires the signature of a director or equivalent Signed forms should be emailed to SCRS Info myfloridacfo com System User s Access Access Control POC responsible for signing and submitting access request forms to SCRS Info myfloridacfo com Access Request Form will be posted on our website next week User IDs o Up to 20 characters o Last Name First Name J Middle Initial INumerical suffix A System User s Access Passwords o FACTS will provide a default password that must be changed upon the initial sign in o Up to 20 digits o Must contain at least 1 instance from 3 of the following 4 categories English UPPERCASE letters A Z English lowercase letters a z 10 base numeric digits 0 9 Non Alpha special characters e g 5 96 A Agency Meetings Agency Meetings We have met with or scheduled meetings with all agencies who have requested meetings Does anybody else need to meet with us Responses to the Agency Impact Statements have been posted on the website gt Other Items Implementation Dates gt Deadline for requesting implementation dates is February 17 2012 Request them through the project email SCRS Info myfloridacfo com o After 2 17 2012 we will assign you a conversion date gt Th
5. DEPARTMENT OF FINANCIAL SERVICES Mesina Name Florida Accountability Contract Tracking System 5 FACTS Workshop 3 2012 8 30 am 11 00 am 1 FACTS User Manual e Test File Conversion Extracts e Training e Agency s Points of Contact and User Access e Agency Meetings e Other Items e Next Meeting Florida Accountability Contract Tracking System FACTS February 3 2012 Agenda FACTS User Manual Test File Conversion Extracts raining Agency s Points of Contact and User Access Agency Meetings Other Items Next Meeting gt FACTS User Manual FACTS User Manual Policy Work Group assisted in the development of policies and proofing the manual FLAIR Education group added screen shots and created the FACTS User Manual The draft manual without screen shots is available on the project web site The final manual will be available in a few weeks FACTS User Manual Reminder A contract is any written agreement between two or more parties All contracts must be reported no later than April 2 with the exception of the following contract types Grant Award Agreements Grant Disbursement Contracts Purchase Orders gt FACTS User Manual Important Definitions Contract Type Contract Status Contract s Change Type gt Method of Payment gt Major Deliverable Contract s Performance Metrics
6. JA a JUR s 56 090 7 5 JB o oo Mos 93107 a v 8 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide FACTS File Layout_Conversion Template Microsoft Excel 4 E Lj Conditional Formatas Cell Formatting Table Styles Ee IT FACTS File danda Conversion Template Microsoft Excel op 7 Conditional Formatas Cell Formatting Table Styles 9 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide FACTS File Layout_Conversion Template Microsoft Excel UE Sot amp Find amp 2 Filter Select 0 e lt 0 0 Conditional Formatas Cell Q Sort amp Find amp Formatting Table Styles Filter Select 7 B S H 3 BM X BO EF 3 BA 88 BT VENDOR 2318 39 28 R 2 8 88 3 5 2 38 8 2 8 10 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide FACTS File Layout_Conversion Template Microsoft Excel x TP ees Home Insert Layout Formulas Data Review View OX z s Hap 5Inset Sr ES B ES EE LS cone i ES 14 SW Delete 9 Paste gt 9 9 Conditional Formatas Cell Sort amp Find amp y B 1 U
7. 1 19 016 30 2014 Entitlement 11 1 11111 y ici i of 350 000 000 ETE on the wi ir y the U S Department o mn nstruction Great Teachers and Leaders and Turning Around the Lowest Achieving Schools jother TIT i imm nnm MAIN MAIN ADD 480000 Rac 5 9 016 30 2014 Entitlement 1 1 i i e of 350 000 000 based on the ed Final Scope of work detailing their ned in or to participate y the U S Department of ruction Great Teachers and Leaders and Turning arto the ASS jotner ti Ti HORTON OE Re plac ys Rhode Bldg other A 8 11 2003 9 9 9999 Exempt None IN AI IY odo yey ive Kitchen Hood Exhaust e Rhode Bldg Cost Reimbursement 1111111111111 010001111 1111111 vending Machine Placement Services 11 30 2004 11 30 2013 Sole 111 11 1 INZA ININ A Machine ado Eodcm LD DA Building Re oject Number 24027010 DOE Project Number 110 19 2604197 5 79999 TTT 1exemprion 11111 LL v Iso AAT ly IN N A zl B TPTI Till and Ma enance and bu ilding of the q Annex Daytona Beach Blind services Campus cost ursem 1114111 1 111 BN MAIN 4 5 1 19 1 2010 16 30 2014 INI IN A Implementation has not IN O DLBL pi s participating Med education agencies LEAs receive an award of their share of 3
8. 3 Validation and Cleaning Steps a Verify existing data and add additional required data using the information contained in the FACTS User Manual and the SCRS FACTS System Code Validation Tables documents The web application enforces data integrity rules for many of the required fields to avoid inaccurate data from being reported b Referto the 3 of Direct Data Entry Process above c Which ever method used to validate and update the extract data stacked or horizontal remember that the data must be placed back in the same order as received without the column labels in order to export the data back to a up loadable text file format 15 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide 4 Loading Steps a Once the spreadsheet data is verified and updated with additional required data save the file in the format and with the prescribed file name Batch number date code e g Batch_830000 2012 03 31 so the FACTS web application can process In order to save the Excel file in the required format some Windows settings may need to be 8 480000 5 Microsoft Excel en Y ET y Home Insert Layout Formulas Data Review View Q cm mo WR B g E 4 Calibri ILA Text T nee Ar J Delete Y Bill DuBose mI 49 9 Conditional Formatas Cell me sert amp Find amp m E sl
9. DOE Project Number 24027010 DOE Prc 14 DLBL N A Remodeling of the O amp A Center and Maintenance and building of the Library Annex Dayton Cost Reimbursement 15 MAIN ADD 480000 Race To The Top 16 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 35 Other 17 MAIN ADD 480000 Race To The Top 18 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of Ss Other M 4 gt Sheetl Sheet Sheet3 2 Ready Num Loc i 0 h Save the Excel file 3 Validation and Cleaning Steps a Verify existing data and add any additional required data using the information contained in the FACTS User Manual and the SCRS FACTS System Code Validation Tables documents The web application enforces data integrity rules for many of the required fields to avoid inaccurate data from being reported b Some agencies may want to insert rows at the top of the Excel spreadsheet to enter the field names for each Record Type using the Department of Financial Services SCRS FACTS File Format for Agencies document to help identify the data in each row and column 480000 S Microsoft Excel mHmx P ae Ox a B Xi ism General R i 3 9 E 50 50 Conditional Formatas Cell zi Sort Find amp Formatting Table Styles Filter Select Editing 7 of 22 C Florida A
10. FACTS aka SCRS Extract File User Guide 7 When the Save As dialog box appears enter the prescribed file name e g Batch Number_YYYY_MM_DD then select CVS Comma delimited in the Save as type field click Save IN Documents 3 My Computer My Recent Documents My Network Places a 81480000 5 J DFS Non Competitive Solicitation of Commodities and Services Reporting Website Data Fields Des DMS Commodity Conctractual Service Codes amp DOT FLAIR Contract Data None Competitively Solicitation of Contracts As of 11 30 11 Proposed Method of Payment Codes My Documents Proposed Method of Procurement Codes SCMS Concession Table Fields d SCMS Disbursement Table Fields My Computer E Senate Database Contracts of 500K or Greater Sevice My Network File name Save as type Excel Workbook Save Cancel 8 When the next dialog box appears click Yes Microsoft Office Excel Bacth 480000 20120110 csv may contain features that are not compatible with CSV Comma delimited Do you want to keep the workbook in this format To keep this format which leaves out any incompatible features click Yes a To preserve the features click No Then save a copy in the latest Excel format To see what might be lost dick Help 19 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File
11. Places File name Files of type Text Files Cancel 4 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide c Once the Text Import Wizard appears click the Delimited button and make sure Start import a row is set at 1 and File origin is Windows ANSI click Next Text Import Wizard Step 1 of 3 The Text Wizard has determined that your data is Fixed Width If this is correct choose Next or choose the data type that best describes your data Original data type Choose the file type that best describes your data Characters such as commas or tabs separate each field Fixed width Fields are aligned in columns with spaces between each field Start import at row File origin Windows ANSI Preview of file C Documents and Settings duboseb Desktop Merge_480000_5 txt IN 20505 480000 To The Top Services Al 2010 05 01 2014 06 30111 I2 PLBL Other Florida s participating local education agencies LEAs receiw ATN ADD 480000 Bace To The Services A 2010 09 01 2014 06 30 j a PLBL Other Florida s participating local education agencies LEAs receiv d Uncheck any Delimiters boxes and check Other enter the Pipe character in the field next to Other Hold Shift key and press the Back Slash key click Next Text Import Wizard
12. Step 2 of 3 This screen lets you set the delimiters your data contains You can see how your text is affected in the preview below Delimiters Tab d Semicolon d Treat consecutive delimiters as one d Comma Text qualifier F Space m Other Data preview a 1 ngu oo c LEAs receive gt 5 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide e Click Text button click Finish Text Import Wizard Step 3 of 3 This screen lets you select each column and set the Data Format Column data format de General General converts numeric values to numbers date values to dates and all remaining values to text Do not import column skip Data preview Florida s participating local education agencies LEAs receive 480000 f When the Import Data dialog box appears click OK Import Data 2 X Where do you want to put the data Existing worksheet CO New worksheet g The data will appear in Excel Each parent record starts with MAIN and will roll into multiple rows that represent the 7 different Record Types MAIN Contract CTCH Contract Change Amendment BUGT Budget Info VNDR Vendor Info DLBL Deliverable Info CSFA CSFA Info CFDA CFDA Info 6 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide Book1 Microsoft
13. 2 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 3 MAIN ADD 480000 Race To The Top 4 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 5 MAIN ADD 480000 Race To The Top 6 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 7 MAIN ADD 480000 Race To The Top 8 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 9 MAIN ADD 480000 Replacement Kitchen Hood Exhaust Sys Rhode Bldg 10 DLBL N A Replacement Kitchen Hood Exhaust Sys Rhode Bldg Cost Reimbursement 11 MAIN ADD 480000 Vending Machine Placement Services 12 DLBL N A Vending Machine Placement Services Other 13 MAIN ADD 480000 Building Renovation DOE Project Number 24027010 DOE Prc 14 DLBL N A Remodeling of the O amp A Center and Maintenance and building of the Library Annex Dayton Cost Reimbursement 15 MAIN ADD 480000 Race To The Top 16 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 17 MAIN ADD 480000 Race To The Top 18 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 5351 Other AONODO anTa Tha Tan EMEN e M 4 Sheetl Sheet2 Sheet3 2 4 Ready Num Lock h Save the Excel file
14. 7188 JE 5 ES 5 fo 138 ana gt Table Styles 1 Format Filter Select Clipboard Font Alignment fs Number Styles Cells Editing do c Vika BS RS Ha CB7 yO fe x BO BZ CA cc co Cc M Layout Data 2 mM Ready Num Lock E LH 1 4 Loading Step Once the spreadsheet data is verified and updated with additional required data access the FACTS web application From the FACTS web application manually enter the required contract data elements using the Excel file as the reference document e User Interface Batch Process 1 Extract File Pickup Step a Using the Agency sFTP software access the sFTP site b Login with the sFTP User name and password provided via SCRS Info MyFloridaCFO com c Retrieve Agency Extract files 2 Opening the Extract file s with Microsoft Excel Steps 11 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide a Open Excel application m Book1 Microsoft Excel ne Insert Page Layout m e View 2 M Jm i From mm ys la A a fa amp Clear te 8 Data Validation al a az ia From Web T Properties Reapply sa Fia Consolidate t ro From Other Existing Refresh __ zl Sort Textto Remove Group Ungroup Subtotal From Text Sources Connecions Alc S Edit Links H9 What If Analysis
15. Commodities and Services Reporting data and the State Contract Management System Senate DB Refer to the document Department of Financial Services SCRS FACTS File Format for Agencies document for more details on the data records 3 Loading Steps a Configure local system to export data according to the Department of Financial Services SCRS FACTS File Format for Agencies document Remember to save the file with the proper format and naming convention Batch OLO number date code e g Batch_830000 2012 03 31 Transmit the batch file with the Agency s sFTP client software user name and password provided via SCRS Info MyFloridaCFO com The sFTP client will provide a confirmation that the file was transmitted successfully or a notification that an error occurred If an error was experienced in saving the Agency Batch data load investigate and resubmit prior to 5 PM All Batch data load files will be processed between the hours of 5 00 PM and 6 45 PM The information will be validated and loaded into the FACTS web site database If an error was encountered it will be written in an output log file and placed on the error screen of the web application Essential information is passed through as a second batch loading process to FLAIR If errors are encountered the errors will be captured in the same output log file described in the earlier paragraph Valid data loaded under the batch process will be in FLAIR within 24 hours from the
16. Contract s Financial Consequences Test File Conversion Extracts Test File Conversion Extracts CDs of agency conversion data are available after today s meeting Final data will be available on 3 5 2012 as of 2 29 2012 Extract File User Guide is available on the project web site http www myfloridacfo com aadir statewidecont ractreporting htm Test File Conversion Extracts Help sessions for conversions and creation of upload files will be held on February 9 10 2012 in the FLAIR classroom This is a voluntary process It might be easier to key the initial data in manually DFS has been the test pilot for the conversion process gt DFS Pilot Conversion Process DFS Pilot Conversion Process Demo Excel template built by DFS Training Training Agency Addressed Memo regarding training will be issued next week Data Conversion assistance February 9 10 2012 FACTS training o Focus is Online input o Begins February 27 2012 o 2 sessions per day oJust in Time training A Agency s Points of Contact POC and User Access Agency s POCs Requesting three points of contact Technical Access Custodian gt Public Inquiries Technical contact is required if you will send or receive batch files Public Inquiries contact will be displayed on the system to the public A Submission of Agency s POCs
17. Excel sert Layout ormulas Data Revie 58X L From Access g AJ WK Clear te Data Validation A Li From Web A Properties 2 Z A Reapply EE Fia Consolidate HE a L From Text p spc FS nia lo lid de el ap O PT Ed what if Analysis Ungroup Subtotal Get External Data Connections Sort amp Filter Data Tools Outline 14 A E A PU 2 m s J 4 JE 5 07 65 Moo 9107 Pos EE 1 B B G D E F E MAIN FADD 480000 Race To The Top _2 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 35 Other 3 MAIN ADD 480000 Race To The Top 4 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 35t Other _5 MAIN ADD 480000 Race To The Top _6 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 35t Other _7 MAIN ADD 480000 Race To The Top 8 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 35 Other 9 MAIN ADD 480000 Replacement Kitchen Hood Exhaust Sys Rhode Bldg 10 DLBL N A Replacement Kitchen Hood Exhaust Sys Rhode Bldg Cost Reimbursement 11 MAIN ADD 480000 Vending Machine Placement Services 12 DLBL N A Vending Machine Placement Services Other 13 MAIN ADD 480000 Building Renovation
18. Interface Batch process is for agencies that are familiar with Microsoft Excel and have a large number of contract records to load Agencies must have the ability to save the Microsoft Excel worksheet into a file format easily processed by the FACTS web application Once the Agency user has verified and updated the file for required data the Excel spreadsheet with contract data is saved in the format and with the prescribed file name that the FACTS web application can process The user then logs into the FACTS website and can select the upload option to upload the data This requires that the Agency user have a sFTP user name and password provided via SCRS Info MyFloridaCFO com The third option also referred to as the Agency Batch Transmission process is used when the agency has a high volume of contract records hundreds or more and is looking for a technical FACTS web application loading solution The agency will generate a Batch file in the format and with the prescribed file name that the FACTS web application can process at the Agency and transmit it via SFTP to the FACTS web application This requires the agency to request a SFTP account via SCRS Info MyFloridaCFO com 3 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide Agency Level Processes e Direct Data Entry Process 1 Extract File Pickup Steps a Using the Agency sFTP software access the sFTP using the user name and passw
19. 50 000 000 based Title I formula In order to receive an ard each participating LEA must have an approved Final scope of work detailing t activities and deliverables across 13 rejects aligned to the Memorandum of Understanding mou they ye in order to patic ipate The MOU and projects are 2 gned to the four assurance areas of the Race to the Top program as identified by the u 5 Department of Education Standards amp Assessments Usin Data to Improve Instruction Great Teachers and Leaders and Turning ye the Lowest Achieving Schools lother 11 b Access the FACTS web application select the upload option to upload the agency contract data and follow the on screen instructions This requires a SFTP user name and password provided via SCRS Info MyFloridaCFO com c Agency will receive a confirmation that the file was transmitted successfully or a notification of errors If an error was experienced in saving the Agency Batch data load investigate and resubmit prior to 5 PM d All Batch data load files will be processed between the hours of 5 00 PM and 6 45 PM The information will be validated and loaded into the FACTS web site database If an error was encountered it will be written in an output log file and placed on the error screen of the web application e Essential information is passed through as second batch loading processes to FLAIR If errors are encountered these errors will b
20. User Guide a The saved file will look like this when opened with Microsoft Notepad Bacth_480000_ 20120110 Notepad File Edit Format View Help MAIN MAIN ADD 480000 Race To The 19 1 201016 30 2014 Entitlement 11 1 1 1 lYINI INI IN A implementation has not in LBL OLBL other Flor ida s participating Toral education agencies LEAs receive an award of their share of 350 000 000 pasat an the Title I formula In order to receive an eac pproved Final Scope of work detailing t activities and deliverables across 13 sel aligned to the Memorandum of Understanding mou they signed in order to participate MOU and projects are aligned to es E of the Race to the Top program as identified by the u s Department of Education Standards amp Assessme rove Instruction Great Teachers and Leaders and Turning Around the Lowest Achieving Schools other ik Ui 1 Thu l n 1i 1 T n H 1 l 14 B MAIN MAIN ADD 480000 Race Th sla 1119 016 30 2014 Entitlement ELELLETELTYINI TI 1 11 5 LEAS recei ve an award of their share of 350 000 000 based Am the roved Final Scope of work detailing t anes m order to Participate of the Race to the Top program as identified by the u s oe of o Improve Instruction Great Teachers and Leaders and Turning pad th Lowest Achieving Schools 111111 AN MAIN MAIN ADD 480000 Race To The Top 5
21. al General converts numeric values to numbers date values to dates and all remaining values to text Do not import column skip Data preview f When the Import Data dialog box appears click OK Import Data Where do you want to put the data Existing worksheet New worksheet g The data will appear in Excel format Each parent record starts with MAIN and will roll into multiple rows that represent the 7 different Record Types MAIN Contract CTCH Contract Change Amendment BUGT Budget Info VNDR Vendor Info DLBL Deliverable Info CSFA CSFA Info CFDA CFDA Info 14 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide Ex Book1 Microsoft Excel EMEN Home Insert Page Layout Formulas Data Review View A X R W From Access E La Connections AJ Y Data Validation al wE aE 3 y E 7 7 y aa _ From Web a Properties AE Reapply aos Ea Consolidate L s From Other Existing Refresh f Z Sort Filter Y Textto Remove Group Ungroup Subtotal 4 From Text Connections Aly Edit Link l 7 Advanced Columns Duplicates Ep What If Analysis Es 5 Get External Data Connections Sort amp Filter Data Tools Outline en 07 B G D E E il MAINTADD 480000 Race To The Top
22. ccountability Contract Tracking System FACTS aka SCRS Extract File User Guide Following the steps listed above will also allow agencies to use the Excel filtering function to group Record Types 480000 S Microsoft Excel mx Conditional Formatas Cell Formatting Table Styles 480000 ace To The Top ervices 97172010 6130 2014 480000 ace To The Top ervices 92122010 6 30 2014 480000 ace To The Top ervices 3 2010 6 30 2014 480000 ace To The Top ervices 92122010 623022014 480000 Replacement Kitchen Hood 811122003 3 3 3393 Exhaust Sys Rhode Bldg 480000 Vending Machine Placement ervices 130 2004 1123022013 Services 480000 Building Renovation ervices 1071922004 3 3 3333 Project Number 24027010 DOE Project Number 24027000 480000 Race To The ervices 9212010 623022014 80000 80000 480000 480000 Race To The Top ervices 92122010 6 30 2014 Race To The Top ervices 941 2010 6 30 2014 Race To The Top ervices 911 2010 6 30 2014 Race To The Top ervices 391142010 6 30 2014 e MM AR o di Some agencies may want to use the Excel spreadsheet template developed by DFS that take the record of a contract and lays out he data in horizontal line instead of the stacked version above FACTS File Layout_Conversion Template Microsoft Excel Conditional Formatas Cell Formatting Table Styles Contract Record Type 14 m E y Pp p
23. e captured in the same output log file described in the earlier paragraph f Valid data loaded under the batch process will be in FLAIR within 24 hours from the time the web system process the batch file Remember loading processes are scheduled to run on regular working days g All errors encountered during the batch process will be posted by 5 AM the next business day e Agency Batch Transmission Process 1 Extract File Pickup Step a Using the Agency sFTP software access the sFTP site b Login with the user name and password provided via SCRS Info MyFloridaCFO com c Retrieve Agency Extract files 2 Validation and Cleaning Steps Validation and cleaning steps should be used prior to transmitting agency data back to the FACTS web application a Conduct manual or system procedures to open the file b Validate the data contained in the agency batch file to make sure the data is correct Some data stored in the source extracts may not be correct The FACTS contract web application enforces data integrity rules for many of the required fields to avoid inaccurate data from being reported c Clean the data by moving into the correct position in the batch file or by deleting erroneous data and entering the correct data Please refer to Agency Subject Matter Experts who are familiar with FLAIR 20 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide data CFO Non Competitive Solicitation of
24. ion for confidentiality 8 Prior to one time merge file delivery to Agency identify old contracts and designate with the proper status to prevent review of expired data from the following 3 sources a State Contract Management System Senate database b FLAIR Contract module CFO Non Competitive Solicitation of Commodities and Services Reporting System data 9 Receive one time merge file from DFS 10 Extract data into Excel spreadsheet format and re format in accordance with the option chosen for the agency to review with subject matter experts refer to FACTS Extract User Guide Review and validate all data make corrections clean the data and add the remainder of contracts and data in accordance with Agency internal policy refer to 7 above Based on the option selected by the agency proceed with one of the following a Direct Data Entry Manually enter the data directly into the FACTS web application b User Interface Batch Process Save the re formatted final spreadsheet pipe delimited flat file using the prescribed name e g Batch OLO Number YYYY MM DD and upload into the FACTS web application c Agency Batch Transmission Process Save the re formatted final spreadsheet pipe delimited flat file using the prescribed name e g Batch OLO Number YYYY MM DD and transmit the batch file using the Agency s SFTP client software 13 If errors occur in saving the batch data load investigate correct and re submit p
25. is is only for initial batch loads gt a Next Steps Request date for conversion no later than February 17 Sign Up for Conversion Help Session if needed Next Workshop o February 17t 8 30 11 00 DOR Conference Ctr A Florida Department of Financial Services Florida Accountability Contract System FACTS Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide Contents Reference documents needed for reviewing and updating Agency extracted contract data 3 ASSUMDUIONS NER Eme 3 MPM A 3 AGENCY Level FOC CS SC Sy ii ai ariba 4 o Direct Data Entry Process ommum 4 e User Interface Batch Process 7 sessi eene enne nnne nennen ona enne 11 e Agency Batch Transmission 5 sse nennen nennen enne enne nennen nennen 20 2 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide Reference documents needed for reviewing and updating Agency extracted contract data 1 2 3 Department of Financial Services SCRS FACTS File Format for Agencies SCRS FACTS System Code Validation Tables FACTS User Manual Assumptions 1 2 3 Agency has identified their Agency s Point of Contact POC and Technical Point of Contact TPOC Agency has obtained user ID and password to log into the SCRS web application for Direct Data Entry Agenc
26. ith spaces between each field Start import at row File origin Windows ANSI Preview of file C Documents and Settings duboseb Desktop Merge_480000_5 txt IN 20505 480000 To The Top Services Al 2010 05 01 2014 06 30111 I2 PLBL Other Florida s participating local education agencies LEAs receiv ATIN 255 480000 To The Services A 2010 09 01 2014 06 30 _ a PLBL Other Florida s participating local education agencies LEAs receiv d Uncheck any Delimiters boxes and check Other enter the Pipe character in the field next to Other Hold Shift key and press the Back Slash key click Next Text Import Wizard Step 2 of 3 This screen lets you set the delimiters your data contains You can see how your text is affected in the preview below Delimiters Tab Semicolon Treat consecutive delimiters as one F Comma Text qualifier Space ie ome Data preview lorida s participating local education agencies LEAs receive 480000 lorida s participating local education agencies LEAs receive gt 13 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide e Click Text button click Finish Text Import Wizard Step 3 of 3 This screen lets you select each column and set the Data Format Column data format C Gener
27. l and Language Options e Control Panel Add Hardware Remote Control such to Category Ven Add or Remove Programs GRun Advertised Programs i guid 3 Administrative Tools Scanners and Cameras AMORS UMES and Gates APPN Node Properties Scheduled Tasks Automatic Updates Security Center Configuration Manager SigmaTel Audio Date and Time Sounds and Audio Devices d Speech W System W Taskbar and Start Menu 98 User Accounts Windows CardSpace Windows Firewall See Also Windows Update 9 Help and Support S Network Connections lo Phone and Modem Options Y Program Download Monitor 41 objects 16 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide 3 When the Regional and Language Options dialog box appears click Customize Regional and Language Opti F Regional Options Languages Advanced Standards and fornats This option affects how some programs tomat numbers currencies dates and time Select an item to match its preferences or click Customize to choose your own formats Customize Samples Number 123 456 729 00 Currency Time 11 53 11 AM Short date 1 10 2012 Long date Tuesday January 10 2012 Location To help services provide you with local information such as news and weather select your present location 4 When the Customize Regional Options dialog box change
28. ord provided via SCRS Info MyFloridaCFO com b Login with sFTP user name and password c Retrieve Agency extract files 2 Opening the Extract file s with Microsoft Excel Steps a Open the Excel application Tp Book1 Microsoft Excel Hx Home Insert Page Layout Formulas Data Review View OX H e A R L From Access E d Connections AJ y Clear EJ m Data Validation EIE en 3 19 From Web Er LE properties HP e Reapply EE __ Ha Consolidate dL P From Other Existing i zl Sort Filter s Textto Remove 5 Group Ungroup Subtotal Li From Text Sources Connections Ally SOM 2 Advanced Columns Duplicates What If Analysis e m Get External Data Connections Sort amp Filter Data Tools Outline E a Ea la ym m Mos o7 dos 7 Al ME B C D E F G H J K L M N o p Q R S E iE 2 3 4 5 6 7 8 a 10 11 12 13 14 15 16 17 18 M 4 gt M Sheetl Sheet2 Sheet3 2 A Ready Num Lock HH U b Click the Data tab click From Text locate the extract file using the Import Test File browser e g Merge OLO Number double click the file Import Text File CEN amp My Documents My Computer Documents 9 Network Places 12 Merge_480000_S amp Shortcut to MergeFiles on dfsnt amp vintdO1
29. re of 5351 Other 23 Excel Options gt X lt Exit Excel 480000 20 DLBL DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 5351 Other 21 MAIN MAIN ADD 480000 22 DLBL DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 23 MAIN MAIN ADD 480000 24 DLBL DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 25 MAIN MAIN ADD 480000 26 DLBL OLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 27 MAIN MAIN ADD 480000 28 DLBL OLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 29 MAIN MAIN ADD 480000 30 OLA NIRI Other Elorida s narticinatine local education FAS receive an award of their share of S Othe 4 Sheet1 Sheet2 Sheet3 4 2 Ready Num Lock J start ca ne me ens E e G Ec Ec Ec ES f Inbox Micro 2 Conversion Fil Ej Microsoft Pow Policy 18 of 22 if the Library Annex Dayton Cost Reimbursement Race To The Top Race To The Top Race To The Top Race To The Race To The Top Slide 16 of 23 Office Race To The Top Race To The Top Race To The Top 11 22 AM Friday Microsoft Exce 1 13 2012 Florida Accountability Contract Tracking System
30. rior to 5 PM 11 12 14 Check the error screen for any errors that occurred during the batch upload process post by 5 AM the next business day correct and re submit or manually enter correction 22 of 22
31. s click the Numbers tab enter the Pipe character in the Line separator field Hold Shift key and press the Back Slash key and click Apply Customize Regional Options KAEA Numbers Currency Sample Positive 123 456 789 00 Negative 123 456 789 00 Decimal symbol Mo of digits after decimal E Digit grouping symbol Digit grouping 123 456 789 4 Negative sign symbol Negative number format Display leading zeros List separator Measurement system E Apply Note After saving the data file in the prescribed text format you may want to change the List 4 3 separator setting back to the comma 17 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide 5 When the next dialog box appears click OK Microsoft Office Excel The selected file type does not support workbooks that contain multiple sheets To save only the active sheet click To save all sheets save them individually using a different file name for each or choose a file type that supports multiple sheets Cancel 6 Back at the Excel sheet click Save As Other Formats Ey 1 Microsoft Excel EP x fa kd o View x Home Ins Save a copy of the document 1 1 Clem f z Insert c F New General P er Lad FS A
32. time the web system processes the batch file Remember loading processes are scheduled to run on regular working days All errors encountered during the batch process will be posted by 5 AM the next business day 21 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide AGENCY DATA CONVERSION CHECKLIST 1 Identify Agency Point of Contact POC and Technical Point of Contact TPOC 2 Obtain user ID and password to log into FACTS web application for Direct Data Entry 3 Ensure Agency has a Microsoft Excel version that allows users to import and export text file instructions Minimum requirement is Microsoft Operating System Windows XP Contact Agency technical staff if using earlier versions of Microsoft Operating System and software or if not familiar with sFTP software 4 Ensure Agency has secure File Transfer Protocol sF TP software to retrieve and transmit data files required for both the User Interface Batch and Agency Batch Transmission options 5 Request sFTP account via SCRS Info MyFloridaCFO com 6 Identify and track all contract agreements in one central location 7 Develop internal policy defining how the Agency will leverage FACTS to comply with the Laws of Florida 2011 049 and Section 215 985 F S Identify what elements or types of contracts are classified as confidential as defined by Agency legal advisors NOTE Dates and amounts of contracts are not necessarily a classificat
33. y has a Microsoft Excel version that allows users to import and export text file instructions Minimum requirement is Microsoft Operating System Windows XP contact Agency technical staff if using earlier versions of Microsoft Operating System and software Agency has secure File Transfer Protocol SFTP software to retrieve and transmit data files required for Agency Batch Transmission only Contact agency technical staff technical staff if using earlier versions of Microsoft Operating System and software or if not familiar with sFTP software Agency has requested a secure File Transfer Protocol SFTP account via SCRS Info MyFloridaCFO com to retrieve and transmit data if this option is selected for the agency Introduction An agency should consider using one of the three following options to load data into the Florida Accountability Contract Tracking System FACTS SCRS The first option referred to as the Direct Data Entry process is completed via the direct FACTS web application data entry online Agency personnel would manually type in the information necessary to load the contract data into the FACTS web application This is a non technical option and it will take some careful planning to enter the Agency s contract data during the conversion period March 1 through March 31st Agency personnel hands on training will be made available during February and March The second option also referred to as the FACTS web application User
Download Pdf Manuals
Related Search