Appendix - Database Tools

From the File menu, select Database Tools.

Applies To: 

• Single Asset Register
• All Asset Registers

Description:

• Each asset register will have its depreciation value updated based on the calculation date set in the database tool. Eligible assets are those that are registered, active, have an effective life that is incomplete, and have their calculations up-to-date.
• Depreciation values can be determined by selecting a calculation date. To update the depreciation value, the following fields must have been completed for the registered asset: 
  • Depreciation Method
  • Starting Value
  • Start Depreciation Date
  • Effective Life (Years)
  • If these values have not been entered, the current value will be set to 0.

Viewing Results:

• Depreciation tab of the Goods Details dialog on the Asset Register screen (see Asset Register).
• Contract Balance field on the Application Work Flow screen (see Application Work Flow).

Applies To: 

• n/a

Description:

• Updates the sys_key table with the highest pkid value in each TFS table. Resolves error messages that appear from trying to insert a non-unique record into a TFS table.

Applies To: 

• n/a

Description:

• Checks each TFS table to make sure it contains a record with a pkid of 0. If the record is missing it is added. These records are important for being able to link and retrieve data correctly.

Applies To: 

• Current Client
• All Clients

Description:

• For each client selected based on the ‘Applies To’ list, links between records are checked to make sure they’re correct, and if incorrect they are reset to 0 or another valid link. Invalid links may have been caused due to problems in previous versions of TFS, and can result in TFS not being able to display or retrieve data correctly. This is most commonly seen when viewing reports, or running mail merge.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each client and application selected based on the ‘Applies To’ list, if they do not have a folder path linked already one is created for them. The path is where linked files are saved by default for each client or application.

Applies To: 

• All Clients

Description:

• Checks client and application folders to ensure that a path to a valid folder exists and that its naming convention matches that of the client and application. If a path specified is not in the standard TFS client files path, then the client.f_nonStndFolder record is set to True.

Applies To: 

• Current Client
• All Clients
• All Entities

Description:

• For each entity selected based on the ‘Applies To’ list, if the entity has all it’s marketing participation and format flags set to no, then we will update the format flags to Yes, and the participation flags will be updated based on the following criteria.
  • Entity is a Client: 
    • Client Participation and All Entities Participation = Yes
  • Entity is a Contact:
    • Client Participation and All Entities Participation = Yes
  • Entity is a Service Provider:
    • Client Participation = Yes (if they’re also a client)
    • All Entities Participation = Yes
    • Generic Supplier Participation = Yes
    • Vendor Participation = Yes (if they have a role of Vendor)
    • Referrer Participation = Yes (if they have a role of Referrer)

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, all goods items are checked to make sure they have a linked address for storing the location of the goods. If they do not then an address is created and linked to the goods item.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Start Date:

• (Required if no End Date.) If both dates are entered, this will select all applications whose completion due is within the date ranges entered. If only the start date is entered then all applications whose completion due is equal to the start date are selected.

End Date:

• (Required if no Start Date.) If both dates are entered, this will select all applications whose completion due is within the date ranges entered. If only the end date is entered then all applications whose Completion due is older or equal to the end date are selected.

Description:

• Applications selected must have an empty completion actual date, and not have a status of cancelled or deleted, and have a completion due date that puts it in the range of the date selections entered.
• For each application selected based on the ‘Applies To’ list and the dates entered, the Completion Actual date will be set to the value in Completion Due, the stage will be set to complete, and the status will be set to inactive.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, if the settlement date is empty then the completion due date will be set to empty. Otherwise it will be calculated as follows.
• If payments are in advance and residual = 0 then:
  • Completion Due = settlement month + (term – 1) months e.g Settled 1/10/2007, term = 12, Completion Due 1/9/2008
• If payments are in advance and residual > 0 then:
  • Completion Due = settlement month + (term) months e.g Settled 1/10/2007, term = 12, Completion Due 1/10/2008
• If payments are not in advance then:
  • Completion Due = settlement month + (term) months e.g Settled 1/10/2007, term = 12, Completion Due 1/10/2008

Applies To: 

• n/a

Description:

• Updates the renewal dates for multiple clients who have Premium Financed? set to Yes and a Renewal Date that is within the dates specified. Appends the Months To Add figure to the renewal date. These fields can be found on the Client Service Provider Screen (Client > Service).
• Premium Financed: Indicates which clients are to be updated based on whether their premium is financed.
• Start Date: Renewal start date.
• End Date: Renewal end date.
• Months to Add: Indicates the number of months added to the renewal date.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, each revenue record attached to the application is checked. It checks to see if the broker total is 0, if it is then any revenue splits linked to the revenue record are deleted. If the broker total is greater than 0 then it looks for any revenue splits. If there isn’t one then a split is created based on the default template split. If there is one then it is checked to see if it is a complete split(valid) and it updates each split to make sure the amounts are correct and that the entity selected to receive the split is correctly linked.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, all the revenue items, not including the primary application brokerage, are added up to calculate how many revenue items there are and their total value, how many have been paid and how much is not yet paid.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, the total repayments are updated by adding all the repayments together to calculate the totals.

Applies To: 

• Current Application
• All Applications
• Current Client
• All Clients

Description:

• For each application selected based on the ‘Applies To’ list, the work flow stage is updated based on dates entered into the work flow section for the application.

Applies To: 

• Current Client
• All Clients

Description:

• For each client contact selected based on the ‘Applies To’ List, the total count of Assets and Liabilities for each contact is calculated and updated in the contacts entity record.