The Financial Overlap tab is used to manage QA/QC for record overlap of the financial project records. A query for Financial Overlap must be run from this tab, it is not run in any other way. To run a Financial Overlap click the Open button in the lower orange panel and enter an Upload ID to test, then press the Ok button. Possible overlap records are recorded in a special table that contains the TPL_IDs of the test and potentially matching records. This tab provides an interface to assist with identifying and reconciling these potential database issues.
The overlap identification process is only started if you initiate it on this tab. To run an overlap query click the Open button on the lower left panel. A dialog box will open where you can enter an Upload ID against which to run the test, click the Ok button to run the test. A query is run to test if the new records are similar to any other financial records already entered into the database. The overlap query uses the following criteria to identify possible overlapping records.
- TPL_ID is not equal to the current records TPL_ID (do not match to itself)
- Upload_ID is less than the current records Upload_ID (in a prior upload)
- State equals the current records State
- Close year is greater than or equal to one year less than the current records close year
- Close year is less than or equal to one year more than the current records close year
- Purchase type is equal to the the current records purchase type
- Reported acres is greater than or equal to 10% less than the current records reported acres
- Reported acres is less than or equal to 10% more than the current records reported acres
- County is the same for both records but only if both records have a county recorded, otherwise don’t use county as a criteria
If a test record matches another record using this criteria then both the test and match TPL_IDs are written to a row in the Financial Overlap table.
The Financial Overlap tab of the Conservation Almanac Data Management Tool is used to find and resolve potential overlap records in the Financial Overlap table. The Overlap Filter is used to find overlap table records. Use one or more of the criteria to find overlap records. Records can be filtered by upload, date, user or status. The table to the right of the Overlap Filter will populate with overlap records if any exist for the filter entered.
With overlap records in the table, select a record to view the potentially overlapping financial data and activate the Overlap Resolution Tools form. The editable fields of the table will populate the form fields; status and comment. A query will populate the values for both of the potentially overlapping records to the right of the form. You can compare all of the financial data of the two records using this interface.
If you decide that these two financial records are not overlapping then you can set the overlap record status to Dismiss by clicking that radio box. You can also enter a comment if you wish.
If you decide that these records are a direct duplicate of each other then you can press the Projects button to go to the Project tab to delete the overlapping project record. Then return and set the overlap status to Deleted. You can enter a comment if you wish.
If you decide that these records are the same but you need to merge some data from one record to another you can click either to Project button to open both records in the Project tab where you can edit one or the other record. You could also click the Contribution button to edit Contributions for one record or the other. After finishing data editing, return to the Financial Overlap tab to reset the status of the overlap record to Merged.
Overlaps are only resolved once the status is changed from Possible.