SQL Database

Apptoto provides a “Sync Agent” that can run on a Windows desktop and connect Apptoto to your SQL Database for the purposes of syncing Calendars and Address Books information.

To get started you will need:

1. The database to be “on-premises”.   (note: If it is “hosted” in the cloud, then the “Sync Agent” may need to run within the hosting company’s infrastructure, or a VPN connection may be needed).
2. A Microsoft Windows computer that is up and running and connected to a network that has access to your SQL database 90% of the time or more. (Apptoto will sync once an hour automatically but only if the computer is on and connected)
3. The ‘Connection String’ for connecting to your database.
   1. NOTE: If you are not using Microsoft SQL Server, then you will need to configure a 64-bit ODBC Data Source configured to access your SQL Database on the machine that you are going to install the Sync Agent. If you are planning on running the Sync Agent as a Windows Service, then you should set the Data Source up under System, otherwise you can just set it up under User.
4. The username and password for an Apptoto account. You can signup here if you haven’t already: https://www.apptoto.com/signup

*The Apptoto Sync Agent should be installed on a machine that will be up and running often. Apptoto will only be able to sync data when this agent is up and running.

*The Apptoto Sync Agent will sync once an hour automatically once fully configured. You can trigger an on-demand sync as outlined in Step 7.

Step 1) Download and Run the installer

Please email support@apptoto.com for instructions on downloading the setup.exe file.

Download and run that installer and let it complete.

Step 2) Run Apptoto > Apptoto Sync Agent from the Windows Start menu

If the shortcut doesn’t appear in the Windows Start menu, then run “C:\Program Files (x86)\Apptoto\ApptotoAgent\ApptotoTray.exe” directly.

Step 3) Log into Apptoto

Step 4) Configure your SQL Database connection

Change the connection type to “SQL – ODBC” and enter in the proper connection string.

If your database is a Microsoft SQL Server database, then we recommend you change the “SQL Provider” to “Microsoft SQL Server” and then set the connections string to:

• Server=<HOST>;User Id=<DBUSERNAME>;Database=<DATABASE>;

You should then put the database password in the Password field… like so:

For ODBC Connections:

If you would like to use a ODBC connection to connect Apptoto to your database, then leave the “SQL Provider” as “ODBC” and then use the following connection string format:

• DSN=<ODBCDSNNAME>;Uid=<DBUSERNAME>;

You can then enter the “Password” for the database in the “Password:” field.  Like so:

If no username is needed, then you can just set the connection string to DSN=<ODBCSOURCENAME>;  .   Normally you won’t be able to run the sync agent as a service though, unless you provide a username for the odbc connection.

Note: No data will be synced after this step is complete. You will be able to pick which calendars you want synced in Step 6 below.

Step 5) Test Connection and Save

Now, click “Test Connection”. If is succeeds then click “Save”.

*You might also want to check the “Launch at Startup” checkbox to make sure that the Apptoto Sync Agent is always running, even after a restart.

Step 6) Configure Calendar (Support Assistance Required)

At this point, an Apptoto support representative will be able to configure the Calendars in Apptoto based on the SQL Schema in the database you’ve connected. So please send an email to support@apptoto.com requesting assistance.

Running Apptoto Sync Agent as a Windows Service

*You may be able to skip the steps below if the “Run as Windows Service” checkbox is not greyed out for you. In that case, you can just check it and click “Save”.

If you prefer to run the sync agent as a Windows Service (which is nice because then the user does not have to be logged into Windows for Apptoto to be connected) then follow these steps AFTER you’ve set it up.

1. If the “Run as Windows Service” checkbox is greyed out, then close the Apptoto Sync Agent by right clicking on it in the System Tray and clicking “exit” and rerun Apptoto Sync Agent as an Administrator.
2. Check the “Run as Windows Service” checkbox and click “Save”.  Follow the prompts.

If the “Run as Windows Service” checkbox stays grey, and you are not able to check it, then follow these steps to configure it from the command line:

1. Close the Apptoto Sync Agent in the Windows System Tray
2. Open a command line prompt, cd to c:\Program Files (x86)\Apptoto\ApptotoAgent
3. Run “ApptotoService.exe -install”
4. Click “Yes” when it asks if you want to copy the info over.
5. Open “Services” management console and set the Apptoto Sync Agent Service to start “Automatically (Delayed Start)”
6. Manually start the Apptoto Sync Agent Service the first time.

If you need to update the settings, then run the Apptoto Sync Agent tray application again, save the settings, and then follow the steps above to copy the settings over to the correct profile.

Running more than one Apptoto Sync Agent on the same machine (Multiple Sync Agents)

This is useful if you want to sync different calendars with different Apptoto accounts, or if you need to connect the Sync Agent to multiple databases.  To do this follow these steps

1. Create a shortcut to the “c:\program files (x86)\Apptoto\ApptotoAgent\ApptotoTray.exe” executable.
2. Set the command line arguments for that shortcut to “—profile <PROFILENAME>“ .    Replace <PROFILENAME> with the 2nd Apptoto’s username, or the 2nd database name.  Only use alpha-numeric characters.
3. Start the Apptoto sync agent using that shortcut.  You will be prompted to sign into Apptoto again.
4. If you decide to run the Sync Agent as a Windows Service (Recommended), a new service will be created with the profile name.

You can create as many shortcuts with different profile names as you like.

Read Query

Once the Apptoto Sync Agent is up and running and connected to your Database through ODBC… Apptoto support will be able to set up a one or more calendars through it. If you’re curious about how this works technically, keep reading, otherwise just work with Apptoto Support to make it happen1

Each calendar will be configured with a different SQL query. The query will run once an hour and sync appointments (using the designated Start Time field) 7 days into the past and 30 days into the future in 1 week chunks. That time range and chunk size can be adjusted.

The standard set of fields that we can pull in through the query include:

• Unique Identifier for the appointment (external_id) [REQUIRED]
• Title of the appointment (title)
• Created date/time. Should be a datetime field. (created)
• Updated date/time. Should be a datetime field. (updated)
• Description/body/notes of the appointment (content)
• Start Time. Should be a datetime field. (start_time) [REQUIRED]
• End Time. Should be a datetime field. (end_time)
• Participant name (participant_0_name)
• Participant phone (participant_0_phone) [PHONE OR EMAIL REQUIRED]
• Participant Email (participant_0_email) [PHONE OR EMAIL REQUIRED]

If your database has multiple phone/email addresses (e.g. home, mobile, work), then we can pull those in too. Apptoto will use the mobile number if one exists, otherwise it falls back to home, then work.

If your database has separate date and time fields, then the query will need to combine them into one. e.g.

CAST(Date as DATETIME) + CAST(Time AS DATETIME)

Also, multiple participants can be handled, but the query needs to return all of the participants in each row.

Also, if there isn’t a “title” field” then Apptoto can be set up to create appointments in Apptoto with a default title. e.g “Appt with {{ participant_0_name}}”.

Lastly, besides the standard fields… we can pull any other field in from the SQL query and store it as a custom field in the event in Apptoto. E.g. if you have a field for the type of appointment, then we can stash that in event.custom.appt_type and use it in messages or conditions inside of Apptoto.

Update Query

(optional)

Apptoto does have the ability to write back to your database when clients respond. For example, we can update a specific column if someone confirms, or cancels.

To do that, Apptoto Support will have to come up with an “UPDATE” statement that sets the field you want updated. The WHERE statement will use the unique appointment id to only update one appointment.

Troubleshooting

The Sync Agent won’t let you log into Apptoto

Please check that you do not have a firewall configured to prevent outbound connections from the Apptoto Sync Agent process.