USING placeholders

You can use placeholders in AutoSQL to make your email messages or filenames use variables like date, time, the number of results rows, or even a part of the result itself.

A common example is to use the {date} and {time} place holder in the output filename. Instead of overwriting the same file every day, this will create a new one each time. This way you can also save the history of queries.

Fields which can have placeholders

  • Name of the output file
  • Subject of the email
  • Message text of the email

By right clicking in a field you see the available placeholders:

Clicking one of the options will insert it in the text.

Different date and time format in filenames

The output path is limited to {date}, {date-1} and {time}. Actually also the format is different. Normally the dates and times are outputted in the regional settings format of the OS. However, for the output filename the date is always outputted as YYYYMMDD and time as HHMMSS.

Possible place holder details

Most placeholders are straight forward, but below you’ll find a detailed explanation.

{result:10}

This will put in a table with the first 10 rows of the result in the email message, this can be used to preview the complete result set or even to have the full in there if you know the number of rows are limited. You can change the 10 into any number of maximum lines, but be carefull. If the message becomes too large, it might not be accepted by your email provider.

An examples output in Gmail:

{date} and {date-1}

The {date} placeholder will always put in the current date. However, if  you run the report with data of yesterday, you can also subtract a day with the -1 addition: {date-1} will always print the date of yesterday. Actually you can also get tomorrow with {date+1} and replace the 1 with any number. Eg to print the date of 7 days ago you can use {date-7}.

{time}

Outputs the time the query was ran.

{rows}

Outputs the number of rows in the result set of the query.

{output path}

Inserts the complete path to the output file. Especially when a placeholder was also used in the filename this can be useful to provide a link. Eg when you output the query to a network drive a user can copy the path to access it directly and you don’t need to attach it.

{action}

Outputs the name of the action.

{query}

Output the SQL query that was executed.

An example of using placeholders