Issue

Customer would like to enable the following feature from the 4.6.15 Release notes

Resolution

***The follow steps were provided by Mr. Mike Rogers. All credit to him for creating the feature, and making an easy document to follow***

Create an Interface

Provisioning: Query -> Suite Level Setting -> Interfaces
Click Add interface
Enter an interface name – doesn’t matter what they call it. I called it CADDB
Click Import XML. Enter the location of the file containing the local query metadata.
CLICK THIS LINK FOR THE META DATA: https://drive.google.com/file/d/1enusRkY4ffuygqobpfcYtXFabV2cWfKt/view?usp=sharing
It can also be found in the CAD/Mobile Provisioning guide, section 28.3.1.1 – Adding a New Interface step 4. However, a copy/paste from the provisioning guide doesn’t work, as the provisioning guide has line breaks in the middle of the URLs. These need to be removed to make the file valid.
Click Save
Yes, you really wanted to click save!
Note that the message area says that the interface was not only added, but that the add was successful:
Select the newly created interface, and click View/Modify
Click “Enable Local SQL Query”
Enter the SQL connection string. This will be one of:

Data Source=P1-SQL-Listen\PremierOne;Initial Catalog=UnifiedCAD;Trusted_Connection=Yes
(Sites where the primary db server is active)

Data Source=DR-SQL-Listen\PremierOne;Initial Catalog=UnifiedCAD;Trusted_Connection=Yes
(Sites where the DR db server is active)

Data Source=PCADDB01-T\PremierOne;Initial Catalog=UnifiedCAD;Trusted_Connection=Yes
(interface on Training)

Data Source=SCADDB01-G\PremierOne;Initial Catalog=UnifiedCAD;Trusted_Connection=Yes
(interface on Staging)

Data Source=SCADDB01-S\PremierOne;Initial Catalog=UnifiedCAD;Trusted_Connection=Yes
(interface on Staging)

Note that site-specific server naming conventions may be used, so the connection string may need to be customized. Demo/test environments will also be different. In general, the Data Source value is whatever you use to connect to the database in SSMS.

Click test. Message should be “Database Connect Successful”
The connection string will be hidden when they click Save, so make a note of it.
Click Save

Provision Query Types – one per stored procedure

Provisioning Query->Suite Level Setting->Query Types
Click New
Enter the name for the query in Display Name – Other settings are optional
Click Interfaces Tab
Click on interface then Add>
Click Interface Fields Tab
Enter the SQL query string. This will be one of:

exec GetRadioInfo_ForLocalQueryInterface {RadioId}	Query accepts a Radio ID. No agency ID allowed as radio ids are unique across all agencies
exec GetTalkgroupsForUnit_ForLocalQueryInterface {UnitID}	Query accepts a unit id. Can enter E1 if there is only 1 E1 in the system. Otherwise, have to enter FD/E1. Shift ID is supported and may be required to make unit id unique.
exec GetVehicleInfo_ForLocalQueryInterface {AgencyId}, {VehicleId}	Query has 2 fields – agency and Vehicle id. Agency can be in its own field (could be defaulted) or Vehicle id may contain agency, (e.g. FD/VEH1234) in which case agency ID field can be left empty
exec GetVehicleInfo_ForLocalQueryInterface NULL, {VehicleId}	Alternate version of Vehicle query where agency id (if required to make vehicle unique) has to be entered as FD/VEH1234 in unit id field. Query cannot access working agency setting, so if the user enters VEH1234 only that vehicle has to be unique.

Click Done
(Note – my stored procedure name is different from the “Official” name)
Click form tab
Fields are needed for the things in {}. The name of the field doesn’t matter, but is probably best to use something similar to what was typed in {}. Click Form Field dropdown. If a reasonable field name does NOT exist, click Add New Form Fields >> to create one
Fill in Create form Field dialog, then click Add then Save.
Back in the form tab, select field. Make sure Required and Visible are checked. Click Green +
While row is still selected at top, enter Interface/Query Set/Fields at bottom. Top of the screen creates a field in the form. Bottom says where the data goes. Click green + at bottom
Repeat for other values in {} from Interface Fields, if more than one set of {} was used. If there was only one, click Save