SQL Connect
Connecting to a SQL database is done from the Integrations screen on the main Pitchly account screen.
Host
Username
Password
Port (if other than 1433)
Whether connection uses SSL
Name of the database
Name of the table (or view)
Whitelisting IP addresses with permission to access the SQL server is recommended, but not required. If you choose to whitelist, you will use the following IP addresses:
34.197.187.203
34.197.229.75
34.197.156.92
34.197.222.74
107.20.48.156
34.203.255.33
44.196.194.190
44.205.138.82
44.205.90.197
52.87.29.254
After the SQL server is connected, the Pitchly account will be populated with tables from the SQL database. By default, it will only import the table specified, but if Also import referenced tables is selected, all tables referenced by the originating table will also be imported and linked automatically in a chain reaction.
All data types, references, files, etc. are automatically assumed by the structure and setup of their SQL database. But these “best guesses” can be changed by us as needed.
Field types
Pitchly automatically maps field types in SQL to the closest equivalent field type in Pitchly, but there are some instances where you may want to use a different field type.
An example would be where you want to display a list of possible values in the filter for a field, so your employees can easily filter by checking values from the list instead of free-typing the value. SQL has no concept of dropdowns because the value is just stored as normal text, so it’s not possible for Pitchly to automatically know all the possible values that field will contain.
In this situation, the Pitchly team can modify the field type for you, but it is something we have to do manually on our backend. In the case of a dropdown field, we’ll also need a list of all the possible values you would like to show in that dropdown. While you can’t change the field types yourself today, no change is necessary on your side.
Primary field
The primary key (in Pitchly, it’s called a primary field), is an automatically generated ID that is not human-friendly.
In Pitchly, it’s possible for you to keep your primary key set to this computer-generated ID but for us to display a different value as if it’s the primary field. This is valuable because it allows Pitchly to display friendly-looking references instead of cryptic random letters and numbers. It also does not require you to change your primary key and references across your entire database in order to make it look nice in Pitchly.
An example of this would be if an attorney has the ID att-123 and the name Jane Doe, we can assign Pitchly’s primary field as the attorney’s name, Jane Doe, while you leave the ID as the primary key in their SQL database. Users in Pitchly will see “Jane Doe” whenever that attorney is referenced instead of “att-123”.
Like changing field types, the primary field can be changed on our backend to refer to any field, but our engineering team must do it manually.
Documents app setup
Normally, when the Documents app is installed on a database, it automatically creates a new field called “Template” which will store the name of the template a record is associated with. Because SQL Connect is currently read-only and we can’t create new fields in your database, however, this requires a different approach.
The choice of approach will be different depending on whether you are already storing a value in the table that you want to associate with each template. If there is an existing field that correlates to the desired template:
We will need all the possible values in this field. If it’s a “Matter Type” field, we will need a list of all the possible matter types in the database. We will need the exact values (uppercase/lowercase, etc. matters)
Connect the SQL database to Pitchly through the Integrations interface.
Provide the Pitchly team with the following information:
The table you’re connecting Documents to.
The field from step #1 that correlates to the template.
All the possible values that from step #1.
Engineering will manually designate this field as the field Documents should look at to determine which template to show per record and will return with already-created blank templates in the Documents app named for each template type.
Once complete, we will notify you that your account is ready to use If a field does not exist to correlate to the desired template:
If a field does not exist to correlate to the desired template:
Before connecting, you will need to create a new field in your SQL database. This field will store the name of the template that will be used to show records in the Documents app. You will create this field as a standard text field (ie: a varchar(255) field). It can be named anything, but it could be called “Template”.
Connect the SQL database to Pitchly through the Integrations interface.
Notify the Pitchly team with the following information:
The table you’re connecting Documents to.
The field that was created in step #1 that correlates to the template.
Engineering will manually designate the created field as the field Documents should look at to determine which template to show per record and will return with already-created blank templates in the Documents app named for each template type. Do not change the names of these templates, or they may stop working!
Once complete, we will notify your that your account is ready to use
Other apps
All other apps, like Proposals and Slides, will work out of the box with SQL. However, you must configure any desired references in your SQL database that you would like to use in a document template.
Also keep in mind that SQL databases can reference fields in non-SQL databases. The databases can live side by side, even tables from different SQL databases.
Field changes (maintenance)
Any and all changes to the fields in your SQL database as displayed in Pitchly have to be manually handled on our backend by engineering, since field changes are not automatically reflected or sync’d over time.
By default, all fields are imported at the time the connection was made, but we can manually hide or change certain fields (including their names or data types in Pitchly) if you choose.
Images & files
For an image or file to show in Pitchly as an “attachment” field, it must be saved as a "binary", "varbinary", or “image” field type in SQL.
Notes
Pitchly’s connection to SQL is currently read-only. We can’t yet write back to the original SQL database.
SQL does not support Dropdown Multiple or Reference Multiple fields because they do not exist in SQL - we invented them for Pitchly. However, if a Dropdown Multiple is desired to show a comma-delimited list of values (such as for a document template), this can be accomplished creating a SQL “View” and following the directions in the following link. This will convert your list of values into a single comma-delimited text field: SQL Server
Last updated
