Updating Contacts in Smartphones
/Contacts and Calendars synchronize automatically and over-the-air between Time Matters and smartphones when you use the free Time Matters feature for Exchange Synchronization. You also need MS Exchange running either on your server or on a hosted Exchange server that you can subscribe to for about $12 per month per user.
Changing Field Matching between Time Matters and your Smartphone
You can modify the basic field matching between Time Matters and your smartphone for phone numbers, e-mail addresses and other information. After you first synchronize your smartphone, you may want to change the field matching by adding more fields or by changing around the ones you matched the first time.
It is not difficult to add or change matched fields. The Time Matters Help explains how to do this under Contents | Import/Export and Synchronization | Synchronizing Data | MS Exchange Synchronization. But the changes won't affect the existing Contacts in your smartphone until you update them using a query or procedure like the one described here.
SQL Query to Update Contacts in Smartphones
The following SQL query can be used to update all the Contacts in your smartphone so that they will have the changes you have made to the field matching.
The query is designed to run in batches every 6 minutes to avoid losing updates. It is important to have the Time Matters Exchange Synchronization process running in the background when you run this query. That allows the updates to occur in batches of about 1000 records in a database with fewer than 10,000 Contacts. (If you have more than 10,000 Contacts, you shouldn't try to sync them all to your smartphone. Performance can be slow with 5,000 to 10,000 Contacts on today's smartphones. Beyond that number, you may find it unacceptable. You can apply filters in your Exchange Synchronization setup to limit the number of Contacts you sync to your smartphone.)
Because this query makes a change to each Contact record you should:
- Backup Time Matters first.
- Have some experience working with Microsoft SQL Server Management Studio.
The query puts the number 1 into a field that normally is hidden behind the Memo field of the Contact record, User 18 or Custom 18 (CON1_06_08). That change causes the Contact records to be updated in MS Exchange and then copied to your smartphone.
Heads Up: If your Time Matters database makes some other use of this field, do not run this query, but rather modify it to update an unused field.
The query, below, is for Time Matters 9 Enterprise. For Version 10, replace "tm9user" with "lntmuser" and replace "TimeMatters9" with "TimeMatters10".
Download as text file: TM9_UpdateContacts.sql
SQL Query: TM_UpdateContacts.sql
-- =============================================
-- Author: Wells H. Anderson - info@activepractice.com
-- Create date: 2010-01-25
-- Description: Update Contact records to update synced records in MS Exchange.
-- Delays are needed to allow complete updating.
-- =============================================
UPDATE tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'A') AND (last_name < 'C')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'C') AND (last_name < 'G')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'G') AND (last_name < 'J')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'J') AND (last_name < 'M')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'M') AND (last_name < 'R')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'R') AND (last_name < 'T')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters9.tm9user.contact
SET CON1_06_08 = '1'
WHERE (last_name > 'T')
You can copy and paste the above code into a New Query window in SQL Server Management Studio. Be sure to backup and to check the code using the Check icon before running this query.
If you have any questions or suggestions, please let us know:
Click here to send us an e-mail...
For Time Matters 10, use the following Stored Procedure to update all Contacts:
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'A') AND (last_name < 'C')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'C') AND (last_name < 'G')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'G') AND (last_name < 'J')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'J') AND (last_name < 'M')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'M') AND (last_name < 'R')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'R') AND (last_name < 'T')
WAITFOR DELAY '000:06:00'
UPDATE TimeMatters10.lntmuser.contact
SET CON1_06_08 = '0'
WHERE (last_name > 'T')