This article refers to features and functionality that exists in QueryConnect v220.127.116.11 and later
This article will provide knowledge on the basics of creating a connection to a data source (Excel will be the example used) and the process to produce a fully automated data import to a SharpCloud story.
Creating a New Connection
To create a new connection, simply select the “Add a new connection” icon located on the bottom toolbar section, as shown above. A new dialog will appear providing you different types of connections that can be created with QueryConnect.
Connections can be grouped together by using folders, allowing a group of connections to be processed at once. This means a story or group of stories can be updated from one or many sources at once using QueryConnect.
For more information about Folders with QueryConnect, please refer to Creating and using Folders within QueryConnect Article.
- Database Connection
Under the section “1. Database Connection”, you can configure the connection to choose the file (applies to only Excel and Access) or the source of the data (other connection types).
You can also change the connection name and provide a description (optional) to the connection.
Filename can be pasted in from the source (if needed) or a file can be chosen by using the “Choose File” option (for Access and Excel).
Once the file/path has been chosen, you can test the connection to ensure that there is an active connection between the source and QueryConnect. If there seems to be an error, ensure that all the relevant tools have been downloaded from the About section in the main toolbar.
After connecting to the desired database, the next stage is to query the data to generate Items/Relationships/Resource URLs/Panel Data. Each type of data has its own sub-section as shown below:
In this example, the item data resides on a sheet called “Items” within the Excel document, so a simple query string was generated to select the data from that sheet.
Note: QueryConnect will auto populate the Query String field with an example query to suggest how to create the query for a user who has minimal experience in doing so.
You can preview the results by pressing “Preview SQL Results” to ensure that the data is being imported in the way that was expected.
The process can be repeated for relationships as shown below, using data from a sheet called “Relationships”
The process can be repeated for any of the story entities that you wish to bring in from the data source.
Remember, there is no limit on the number of connections that can be created, allowing the potential for lots of different data sources bringing in different story entities into one or more stories.
- Update Story
This stage is a pinnacle step, where the story can now be updated with the data from the relevant data source(s).
The server can be amended to match the instance of SharpCloud you are using, for example, my.sharpcloud or uk.sharpcloud or an on-premises instance. In this case, the story that will be getting updated resides on the “my” instance of SharpCloud.
Username is the username that was created when you first created your SharpCloud account. If forgotten, on the sign-in page of SharpCloud, there is a “Forgotten Credentials” section where you can have your username resent to the associated email address.
The password is the SharpCloud password and not any other password such as an Office 365 password if you authenticate using Office 365.
Story ID/URL is for the story you wish to update with the data that has been selected throughout this process. If the URL is pasted in, the story ID will automatically be extracted.
Unpublish unmatched items means that if the box is ticked, items in the target story that do not match a record in the incoming data will be unpublished.
Build Relationships – Relationships can also be built from the items data using a RelatedItem column, where external ID’s of related items will form the relationships.
QueryConnect can generate batch files which using other tools can be used to create automation for the update process.
These batch files can be configured to be encrypted at different levels to suit a range of requirements.
- Encrypted (User)
This level of password encryption means that the batch file can only be run by the user who generated the batch file on the same computer the batch file was made on.
- Encrypted (Machine)
This encryption level means that the batch file can be run by any user using the same computer
- Base64 Encoded
This level of encryption means that the file can be run on other machines by other users.
Generating a batch file creates an executable file (.exe) that can be scheduled to run automatically. You can use a tool such as Task Scheduler (pre-installed on most Windows OS Devices) to have the executable file run at a frequency of your choosing.