This section deals with setting up the queries to extract the data from the database for users and then deliver the result to users when they request it.
There are 3 parts:
1. Properties: Choose a data source, view and what type of query to use.
2. Query Builder: Build a query to extract the relevant data.
3. Message Builder: Decide how the message will be accessed by the end user and how it will be formatted when retrieved.
We will create a simple query to extract customer details based on entering the first letter of their name.
Properties tab
Enter a name and description for the first query to find customer details beginning with the first letter of their name, say 'Customer Names'.
We will use Select Query as this extracts data from the database. Update Query edits existing data and Insert Query adds new data.
Next, select the database and view we created. Click Save.
Ignore the timeout field for now, as it's only used when we setup for the user to reply to their first request for data if user replies have been implemented. If a user does not respond in a given time frame, the query expires.
Query Builder tab
Next, move to the Query Builder Tab. In the Define Criteria window you can write your queries using the easy to understand Query Builder, or if you're comfortable with writing SQL, you can edit it directly below.
Click the link <Click here to add a new condition >
The query builder uses drop down menus which you select to build your query.
The Query Builder writes SQL in the SQL Editor as you create it, and you can also write SQL directly in to the SQL Editor tab below and test it.
Click the red colored Customers.CustomerID (Table.Field) to select the appropriate field, in this case it will be ContactName
Next, select the appropriate operator for this query, in this case it is Starts with
Next we select the User Input item from the drop down menu. The User Input item is used for the end-user as their input field when they send their queries from their mobile device. You can add more conditions and use multiple User Input items combined with values or dates.
Now we have a sentence which reads:
Choose records where all of the following apply: Where Customers Contact Names start with [User Input]
Note, you can also change how records are chosen with the drop down menu item like below
Another menu is also available by clicking the number next to each condition as shown below.
This allows you to Add, Delete, Enable/Disable and Add a bracket to the query.
As the wizard has been completed, the SQL statements have been written automatically below and can be edited directly. Any queries which are run, are always based on the manual SQL statement.
Next, click the Sorting and Grouping of Results tab slider.
Here you can specify the order the data is reported back to the end user. For this example we will just order by company name from A to Z (ascending).
If you click back to the Define Criteria tab, you'll see the SQL statement has been updated to reflect these new conditions.
Testing the Query
Next we will test the query to make sure we get the correct results back.
Click the Query Result tab
For testing, we'll input the letter a and press the Test button. You will see a result like below.
This shows the query is working and sending back the desired results. It lists all the Contact Names beginning with the letter a.
Next we will define which email address, or SMS and IM keywords to use for this query and define how the results look when sent back to the end-user.
Message Builder tab
Click the Message Builder tab
Here you select if you want to make available email, sms or IM (or all 3) for accessing this query and if a keyword is used to distinguish between other queries.
First, we will attach an email address to this query. The email address we setup earlier appears in the drop down menu and we select that.
Note on Keywords: Because we can have unique email addresses for each query, we don't need to assign a keyword to separate this query from others. Although if you only have one email address you would separate each query by a keyword.
eg: contacts@company.com finds contacts, employees@company.com finds employee data, sales@company.com finds sales data
Or, use one email address for all queries and separate them by a keyword.
eg. queries@company.com is the only email address used. Keywords are contacts, employees and sales. These are entered before the [User Input} item we setup earlier.
The Results area lets you choose how many results are to be returned to the end-user.
We fill in the rest of the fields if we wish to use SMS and IM.
Note: SMS and IM must use keywords to separate each query because there can be only one instance of each running at a time on the host computer.
In this example we have used the word cont as a keyword (short for contacts).
This means our query will be written like:
cont [User Input]
Click Response Message Format tab at the bottom of the screen.
This screen shows the format of the message the end user will receive when they send their query to Mobile Data Now. This information is taken from the View created earlier and is automatically populated for this query.
You can add additional text to the message as needed. Data is shown as %Table.Field% and can be deleted as required.
Test your query in the Message Test Result tab to see the format of the message end-users will see. (Note: Data is often case sensitive!)
