Skyline Manual

Example Queries

Example Queries

Previous topic Next topic  

Example Queries

Previous topic Next topic JavaScript is required for the print function  

When creating a select statement or account code validation query the same syntax can be used.

All entries from an Excel worksheet.

In the example statement the account information required was everything in the worksheet called sheet1. When the settings were tested there was an error message returned stating that the worksheet name was not recognized. The worksheet name entered needs to be exactly the same as in the workbook. In the example a space was included which was not in the workbook.
Select Statement used when entered correctly for example:

select * from [sheet1$]


No headings used in the Excel worksheet.

If there are no headings used in the Excel file the fields are referred to as F1, F2 F3 etc. When the users see a list of account codes the headings will show as F1, F2 F3 as shown in the example below.

To show a descriptive name in the heading you need to amend to Select Statement to include the names, for example:
select F1 as [Work Order], F2 as [Cost Centre], F3 as [Department] from [Sheet1$]

The users will now see a lost of account codes with headings.


Select specific Excel fields.

In the example below the field Nominated Owner is not required.

Amend the select statement to include the fields that you require. In the example below the field headings Code, Project & Department have been inserted in the statement. Click Test Settings to check your statement. Only the required fields should be shown, as per the example.

Select Statement used for example - select [code],[project],[department] from [sheet1$].

Rename existing field headings

You can display a different field name on the website from the field name contained in the file. For example you might want to present to the end user the field name "Account Code" instead of "Code". All or just one field can be re-named.

Example of one field being re-named:

select [code] as [Account Code], [project], [department] from [sheet1$] where [Number]={0}

Example of all fields being re-named:

select [Number] as [Project Number],[Name] as [Project Name], [description] as [Project Description] from [sheet1$] where [Number]={0}

The syntax where [Number]={0} needs to be added when creating a query for the account code validation. It shows which field contains the account code that will be verified with the code entered by the user. The field name used needs to be the original name that is contained in the file.

If you are creating a statement query where [Number]={0} is not required the user will select an account code from a list.

infoNote: If you are using a CSV file the field names are F1 for the 1st field. F2 for the 2nd field etc. To set the field names that are displayed to the user, use the syntax F1 as [Work Order]. The name of the fields should always be in square brackets as in the example shown below.

Select F1 as [Work Order], F2 as [Cost Centre], F3 as [Description] from Repro.csv where F1={0}