Extract File User Guide - Florida Department of Financial Services
Contents
1. Tp Book1 Microsoft Excel Hx Home Insert Page Layout Formulas Data Review View 7 OX H e A R Ww L From Access TE a d Connections AJ d 4 Clear E m Data Validation 7 EIE en 3 La From Web Er LM A properties HP e Reapply EE O Ha Consolidate bi Li BES wr P From Other Existing i TIE zl Sort Filter s Textto Remove E Group Ungroup Subtotal Li From Text Sources Connections Ally SOM URS AJ Advanced Columns Duplicates 7 What If Analysis z m Get External Data Connections Sort amp Filter Data Tools Outline 3 Ler 1 a n Ea 7 ym m fos Mos o7 dos 7 Al ME fe B C D E F G H I J K L M N o p a R S E iE 2 3 4 5 6 7 8 B E 10 11 12 13 14 15 16 17 18 M gt M Sheetl Sheet2 Sheet3 1 l I Ready Num Lock HH U et 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 Lookin Deko 0000000000000 BG My Documents Ere di 8 My Computer Documents my Network Places 2 Merge_480000_S Shortcut to MergeFiles on dfsnt amp vintdO1 My Me 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 ma2. 7 a Conditional Formatas Cell Formatting v Table Styles Y 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 v Select 7 KM 90 0 0 Conditional Formatas Cell Q Sort amp Find amp Formatting Table v Styles v Filter v Select v B EH S em EK H mm 7 mm eo EF 1 B em 88 BT VENDOR S8 la e2 R 23 82 98 ARS RSS 10 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide 9 FACTS File Layout Conversion Template Microsoft Excel Ax LAE TJ Home Insert Page Layout Formulas Data Review View OX Fi p pri a v J z s Haley geiet Sr Fy B 2 iani EE o cL i E Lad SW Delete 9 Paste gt vr Ar S Same l a o llegia ao Conditional Formatas Cell yy Sot amp Find amp EE y B I U EE i EE oe I fo E RENE 7 Table v Styles iz Format 2 Filter Select 7 Clipboard f Font El Alignment fs Number Iu Styles Cells Editing do c Vika BS gt RS Ha CB7 yO fe x B0 BZ CA cc CD m Cc M M File Layout Data 3 i Ready Num Lock DI D 22 4 Loading Step Once the spreadsheet data is verified and updated with additional required data access the FACTS web application F
3. 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 ASSUMDUONS EEE Eme 3 MUNN 3 Adelcy AEE FOC EE EEE 4 o Direct Data Entry Process Le 4 e User Interface Batch Process 7 sessi eene enne nnne nennen nnns enne 11 e Agency Batch Transmission Process 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 SCHS FACTS System Code Validation Tables FACTS User Manual Assumptions Li 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 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 Agency has secu
4. 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 V I Home Insert Page Layout Formulas Data Review View ox H n A R W a From Access E i39 Connections AJ P ce 3 Data Validation 7 al wE aE di g E Z 7 k 7 aa _ From Web LE ep Properties Ae Reapply 333 fa Consolidate L t s From Other Existing Refresh f Z Sort Filter v Textto Remove pg Group Ungroup Subtotal From Text ou Connections Al Edit Link l YZ Advanced Columns Duplicates i What If Analysis A Get External Data Connections Sort amp Filter Data Tools Outline E en 07 B G D E F E il MAIN JADD 480000 Race To The Top 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 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 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 35 Other 9 MAIN ADD 480000 Repl
5. 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 460000 lorida s participating local education agencies LEAs receive al 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 General 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 PX Where do you want to put the data 9 Existing worksheet CO 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
6. Import Text File Look in Desktop bu Gm My Documents esed 2 My Computer Documents my Network Places Merge_480000_S i Shortcut to MergeFiles on dfsnt amp vintdOo1 Desktop My Documents My Computer My Network Places Files of type Text Files m Cancel x 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 D 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 yduboseb Desktop Merge 480000 5 txt IN ADD 480000 Bace To The Top Services A Z2010 09 01 2014 06 30 1 1 9 lz DLEL Other Florida s Participating local education agencies LEAs receiv _ ATN ADD 480000 Bace To The Top Services A 2010 09 01 2014 06 30 a PLBL Other Fleride s participating local education agencies LEAs receiv
7. To The Top ervices 942010 6 30 2014 480000 ace To The Top ervices 3 2010 6 30 2014 480000 ace To The Top ervices 942010 6 30 2014 480000 Replacement Kitchen Hood er 8411 2003 9 9 9999 Exhaust Sys Rhode Bldg 480000 Vending Machine Placement ervices 130 2004 11 30 2013 Services 480000 Building Renovation DOE ervices 1071972004 3 3 3333 Project Number 24027010 DOE Project Number 24027000 480000 Race To The Top ervices 912010 6 30 2014 480000 480000 480000 480000 Race To The Top ervices 942010 6 30 2014 Race To The Top ervices 942010 6 30 2014 Race To The Top ervices 942010 6 30 2014 Race To The Top ervices 942010 6 30 2014 e MM AR TG IR 0 EEE d 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 Format as Cell Formatting y Table Styles Contract Record Type la M lt va A E v M v p 3 19593 2 NN gt JO a JUR s Piani c 090 7 Rot AM o 59109 9810s 93107 os a 8 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide FACTS File Layout Conversion Template Microsoft Excel mx a E Lj Conditional Formatas Cell Formatting v Table v Styles ea ET FACTS File danda Conversion Template Microsoft Excel op
8. 480000 Race To The Top d 3 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 Lx award of their share of 351 Other Z Prepare EG OpenDocument Spreadsheet 480000 Race To The Top 1 Save the workbook in the Open award of their share of 35t Other d Document Format 480000 Replacement Kitchen Hood Exhaust Sys Rhode Bldg Sen b l 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 rutces z XPS t Publish b or XPS file 480000 Building Renovation DOE Project Number 24027010 0 Xn 480000 Close award of their share of 35t Other 480000 award of their share of 35r Other 23 Excel Optjons gt X lt Exit Excel 480000 20 DLBL DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 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 DLBL Other Florida s participating local education agencies LEAs receive an award of their share of 351 Other 27 MAIN MAIN ADD 480000 28 DLBL DLBL Other Florida s participating local education agencies LEAs receive an a
9. 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 InfoGMyFloridaCFO 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 password provided via SCRS InfoQ9MyFloridaCFO 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
10. Get External Data Connections Sort amp Filter Data Tools Outline I A a r vr Ad E M M pm pm A P 2 m gt Js Pes 6 090 7 Pt Jo 0 os 9107 os JS Al v Qu B A B G D E F E MAINTADD 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 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 Flo
11. LE EE EL LE 1 1 1 vending Machine Placement Services Services A 11 30 2004 11 30 2013 Sole sourcel ll I I I I I None LIN ALTININ A Machine eios Eodcm TLL LLELEEELE ELE EE E E E E EL 10101 Building Re oject Number 24027010 DOE Project Number 1110 19 2004 97 789991 1111 Jexemption 111 LL v Iso AAT ly ININ A zl B TPTI Till and Ma enance and bu ilding of the gn Annex Daytona Beach Blind services Campus Cost ursem FELTET l A BN MAIN pilservicesl alll19 1 201016 30 2014 lentitlement I LL LLLETYINLINLIN A Implementation has not INIO DLBL peser iT s participating Med education agencies LEAs receive an award of their share of 350 000 000 based re the 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 ert se ipate The MOU and projects are sd 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 I 11 PEEP TEEPE TEEPE TEEE TEEPE 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 pas
12. Point 2007 e Connd Programs and set up network connections and user Microsoft Office accounts Visio 2003 g Printers and Faxes Fiaoe To The Top Servioes A 3412010 5 30 2014 da J bluzone F ther Help and Support der to gt Search All Programs Pp O Run Log off Shut Down Average 172997 5385 Count73 Min O Max 480000 Sum 2248968 j CI kl le Ct ustat SSFCBBMEAA gt DTE LLL 11 52 AM Tuesday ff Microsoft Exce Microsoft Pow t SCRS File For f Inbox Micro 3 zr 1 10 2012 2 Double click the Regional and Language Options icon Control Panel MEIR File Edit View Favorites Tools Help Qs QB Search fF Folders Fv Address B Control Panel v Go amp Accessibility Options 9 Regional and Language Options e Control Panel Add Hardware Remote Control f such to Category Ve Add or Remove Programs GRun Advertised Programs i guid 3 Administrative Tools Scanners and Cameras AMORS Ume and Gates XX APPN Node Properties Scheduled Tasks Automatic Updates Security Center i Configuration Manager SigmaTel Audio Date and Time 9 Sounds and Audio Devices amp Speech W System Taskbar and Start Menu S User Accounts Bi Windows CardSpace Windows Firewall See Also Windows Update 9 Help and Support S Network Connections Ll Phone and Modem Options lt Program Download Monitor 41 objects 16 of 22 Florida A
13. acement 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 S35t Other 100nonn Ace Tha Tan EMEN e M 4 Sheetl Sheet2 Sheet3 2 A Ready Num Lock h Save the Excel file 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 orde
14. arator 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 a To save only the active sheet click OK a 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 c Book1 Microsoft Excel EP x s kd s o view 9 9 x Home Ins Save a copy of the document 1 1 Clem z f z Insert c F New General P er HA Lad FS A Excel Workbook asl SK Delete 2 21 G A s J aa Save the file as an Excel Workbook E 9 48 68 Conditional Formatas Cell m e Sort amp Find amp Paste New La Open Formatting Table Styles usd ormat 2 Filter Select e 7 Slide fx Excel Macro Enabled Workbook Va Number E Styles Cells Editing Clipboard S j Save Save the workbook in the XML based and Slides Qutli b macro enabled file format a7 UE Lose merkes le _______ e F s E Save As T i i i bd A moon 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
15. are or if not familiar with sFTP software 4 Ensure Agency has secure File Transfer Protocol SFTP 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 classification 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 c 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 op
16. ccountability 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 Z Regional Options Languages Advanced Standards and formats This option affects how some programs format 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 789 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 changes 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 Emr 4 44 Digit grouping symbol Digit grouping 123 456 789 4 Negative sign symbol Negative number format 4 Display leading zeros List separator Measurement system 4 Apply Note After saving the data file in the prescribed text format you may want to change the List amp 99 sep
17. ceive 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 O 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 450000 gt f When the Import Data dialog box appears click OK Import Data 2 X Where do you want to put the data 9 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 Excel Hmx Home sert Page Layout ormulas Data Revie 58X L From Access g Donezon Al BEI WK Clear te Eg Data Validation A L From Web ZF Properties Zv Z A Wo Reapply 333 Fia Consolidate Gs L From Text EE EE m Menthe XE ee Tr PT rer ED What If Analysis CTOUP Ungroup Subtotal
18. e Fields My Computer Senate Database Contracts of 500K or Greater Sevice Type Shorts eO 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 c5v may contain features that are not compatible with CSV Comma delimited Do you want to keep the workbook in this format a To keep this format which leaves out any incompatible features click Yes a To preserve the features clik No Then save a copy in the latest Excel format a To see what might be lost dick Help 19 of 22 Florida Accountability Contract Tracking System FACTS aka SCRS Extract File User Guide a he 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 Top Services A 9 1 2010 6 30 2014 I Entitlement 1 1 1 1 lYINI INI IN A implementation has not in INTO DLBL DLBL other Flor ida s participating Toral education agencies LEAs receive an award of their share of 350 000 000 pasat on the Title I formula In order to receive an ard eac pproved Final Scope of work detailing t activities and deliverables across 13 Fru erte aligned to the Memorandum of understanding MOU they signed in order to participate MOU and projects are aligned to e eeu E of the Race to the Top program as iden
19. ke 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 Mext 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 D 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 ADD 480000 Bace To The Top Services A Z010 09 01 2014 06 30 11 9 lz DLEL Other Florida s Participating local education agencies LEAs receiw _ ATN ADD 480000 Bace To The Top Services A 2010 09 01 2014 06 30 a PLBL Other Fleride 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 d Semicolon d Treat consecutive delimiters as one d Comma Text qualifier F Space m Other Data preview a 1 9000 oo c LEAs re
20. l 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 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 occ
21. r 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 OLO 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 Es 480000 S Microsoft Excel en Y ET y Home Insert Page Layout Formulas Data Review View 2 x cm mo WR B g E 4 Calibri ILA Text T HB Ui nee Ar Delete Y Bill DuBose 80 9 4 9 9 Conditional Formatas Cell me s A sert amp Find amp m E ei 168 769 Formatting Table Styles Ed Format 2 Filter 7 Select Internet SE k Alignment E Number E Styles Cells Editing y Documen Internet Explorer pr hy E mail My Recent hor Microsoft Office J Documents E T F T G H I i J K L T M I N s E i Microsoft Office s My Computer Race To The Top Services A 8412010 6 30 2014 E Excel 2007 My Network 1 eM 2 Microsoft Office 8 Places deti Word 2007 tivities Control Panel he 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 Power
22. re 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 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
23. rida s participating local education agencies LEAs receive an award of their share of Ss Other M Sheetl Sheet2 Sheet3 3 Ready Num Loc i y o 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 SCHS 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 Ax P ae e m Ox a B P ism v General R E i 2 AF PT d S 3 E o 753 Conditional Formatas Cell zi Sort amp Find amp Formatting Table Styles 7 Filter Select 7 Editing 7 of 22 C Florida Accountability 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 v Styles 7 480000 ace To The Top ervices 97172010 5 30 2014 480000 ace
24. rom 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 Ax tpe Insert Page Layout mm m e View IX m Jm i From mm H la Dem A aj SAT amp Clear te Data Validation 7 al E a E gE az ia From Web T Properties Reapply EEEI i Fia Consolidate t va From Other Existing Refresh __ zl Sort Textto Remove Group Ungroup Subtotal E From Text Sources Connecions AN v S Edit Links H9 What If Analysis da Filter T JZ Advanced Columns Duplicates Get External Data Connections Sort amp Filter Data Tools Outline x Ze Al vg p B C D E F G H I J K L M N o p a R 5 gt ica 18 M gt M Sheetl Sheet2 Sheet3 3 l 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
25. sword provided via SCRS InfoGMyFloridaCFO 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 be 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 manua
26. tified by the u s Department of Education Standards amp Assessme rove Instruction Great Teachers and Leaders and Turning Around the Lowest Achieving Schools other T i ik Ui 1 Thu l n 1i 1 T n H l I l 1 B MAIN MAIN ADD 480000 Race To Th sla i119 016 30 2014 Entitlement I E ELELLETELTYINI TI 1 11 s 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 r r taent of o Improve Instruction Great Teachers and Leaders and Turning yu th Lowest Achieving Schools other 1 JA NEETI LIAN MAIN MAIN ADD 480000 Race To The Top ser sIAII19 016 30 2014 I entitlement I FLITTTTITYINETTTTI j 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 111 i imm nnm MAIN MAIN ADD 480000 Rac s AI1 9 016 30 2014 I I I Entitlement ITIIIITIYINITIIILI s i 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 ye eer the ASS jotner iT TI HORTON OE Re plac ys Rhode Bldg other A 8 11 2003 9 9 9999 Exempt TT TT None IN AI IY ee le jens yey yte Kitchen Hood Exhaust pf Rhode Bldg Cost Reimbursement IIEILELEEETEEEEEEEE EE EL E
27. tion 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 prior 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
28. urred 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 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 softw
29. ward of their share of 351 Other 29 MAIN MAIN ADD 480000 3n DLA NIRI Other Elorida s narticinatine local education aee FAS receive an award of their share of S Othe 45 gt Sheet1 Sheet2 Sheet3 4 2 Ready Num Lock J Start e T9 3 me ens 2 e G ac Bc ec Bo Bee f Inbox Micro 75 Conversion Fil j 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 Top 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 FACTS aka SCRS Extract File User Guide 7 When the Save As dialog box appears enter the prescribed file name e g Batch OLO Number YYYY MM DD then select CVS Comma delimited in the Save as type field click Save IN My Documents 3 My Computer My Recent Documents My Network Places 5480000 S J amp DFS Non Competitive Solicitation of Commodities and Services Reporting Website Data Fields Desktop 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 Tabl
Download Pdf Manuals
Related Search