- Calculating the 1098T amounts in Student Billing
- Creating a Record Export to pull the Student Name, Import ID, and SSN
- Creating an Export from the Import Section to pull the Tax information that has been Calculated
- Combine and Cleanup the two Spreadsheets to prepare for upload in to the third party system
Part I – Calculating the 1098T Amounts
This process is completed within Student Billing in the Adminstration Section. For more information on this process, please refer to: How to calculate 1098T amounts
Note: If the amount are incorrect, or some students are not calculating and should be, please refer to: 1098T does not generate for students
Part II – Creating the Records Export to pull the Student Name, Import ID, and SSN
In the Part III we will be pulling the Tax information. In that process, SSN is one of the Fields that we can pull, but for Security Purposes, the SSN is partially Masked: XXX-XX-1234. To pull the Entire number, we have to pull the SSN from an Export.
To help Combine this Export with the Export in Part II, we must include a Field that we can use to help Excel Merge the Two Documents together: Record Import ID.
To keep the system from pulling too many records in the Export, we will want to create a Query to use in the Export for the students within the Specific years that we are looking for.
Note: If you are not worried about limiting the number of Students returned in the Export Results, you can skip the Query and Continue with the Export.
To create the Query:
- Navigate to Query within Student Billing
- Click NEW – Select Records
- Click OK
- On the Criteria Tab, Expand Record, Expand Student, Expand Student Progression, and Select Academic Year
- In the Pop-up box, select the operator of one of, and then select ALL the Academic Years that we want have included in the 1098T calculations in Part I
- Click OK
- On the Output tab, Expand Record and select Full/Organization Name
- Save and name the Query
Once it is saved, close the Query and head over to the Export Section.
To create Export:
- Navigate to Export Section of Student Billing
- Click New – Select Student, and Set the Format to CSV
- Click Create Now
- On the Filters tab, For Students, change All to Selected
- In the Pop-up, select the Radial for Query and select the Query we just created.
- Click OK
- On the Output tab, Expand Student and Select:
- First Name
- Last Name
- Import ID
- On the Output tab, Expand Address and Select the Address fields that you would like to include
- Click Preview
Note: Preview will only display the first 15 lines of the results.
Note: If the SSN is partially masked in the Preview, you can unmask it in the formatting for the field: Only the last four numbers of the Social Security Number are exporting
Part III- Creating an Export from the Import Section to pull the Tax information that has been Calculated
The information is not able to be pulled from a Query or an Export. To complete this process, we have to use Import’s Export Function to pull the information.
- Within the Student Billing module, navigate to Administration and select Import
- Under Record, Select Record, and click New Import
- On the General Tab, What do you want to do, select Export Records
- On the General Tab, Click in the Import File Line and name the file
Note: It should save to the default location set in Tools> Options> File Locations. If no location is defined, it will save to the Desktop. You can also Click the ellipses (…) and Choose the Location, and name the file here as well.
Note: The File will save as a Text File, and will have to be converted when opened in Excel
- On the General Tab, in the Use Field, select Record Import ID
- On the Fields tab, Select the Following Fields:
- Record> Import ID
- Tax Years> Tax Year
- Tax Years> Create Date
- Tax Years> Payments Received
- Tax Years> Amount Billed
- Tax Years>Has reporting method Changed
- Tax Years>Adjustments for Prior years
- Tax Years> Scholarships and Grants
- Tax Years> Adjustments for Scholarships/Grants for Prior Years
- Tax Years> At least Half time Student
- Tax Years> Graduate Student?
- Tax Years> Insurance Contract Reimbursement/Refund
- Tax Years> Reason?
Note: Since the System will not Let us filter on the Specific Tax Year that we need to pull, we need to re-add items Step 6 b-m for each year of Tax information that is within Student Billing. For Example, if in Student Billing there is information for 2015, 2016, 2017 we will have to add the Field two more times.
- Add Fields from Step 6 b-m repeatedly until all the tax years can be pulled.
Note: When this information is added multiple times the System Will sort and Group them via the Extension column.
- Once all the fields have been entered, Save the Parameter
- Then Click Create Now
Part IV- Combine and Cleanup the two Spreadsheets to prepare for upload in to the third party system
Now that the File has been created, you will need to open the text file in Excel and convert it over so the information can be sorted and cleaned up.
The following Article is from Microsoft’s knowledge base to guide you through Opening and converting the file created in Part III
Once the file has been converted, you will need to filter out all the Records and Years that we do not need for the 2017 1098T forms.
Once the File has been Filtered down, you will need to combine the Export Created in part II and the File in Part III to include the rest of the Fields needed to Submit the 1098T files in the 3rd party system. This can be done by using the Vlookup function in Excel.