SQL Data Source Designer

SQL data sources are based on a database view.

When creating a SQL data source , properties in following sections need to be defined.:

• Data
• Columns
• Information

Data

This section includes following configuration settings:

Property	Description
Name	The name of the Data Source
View	The database view used to create the Data Source
Time Zone Default Setting	For SQL data sources, specify whether the entity used for creating the database view is time zone aware or not. (Default selection is None) None: Entity is not time zone aware Server: Entity is time zone aware Note: If the entity used for the view is time zone aware and you select None, the Lobby Element will not support any date/time conversions.
Where (Condition)	The condition to be used for the 'WHERE' section in the query. Here it is possible to use Page Parameters defined in the Page Properties Configuration dialog. E.g. Company = $COMPANY$. Remember to append the $ sign.
Group By	The column to be used to group the data
Order By	The column to be used to order the data

Figure: Example Definition

Columns

This section is used to manage which columns to use from the view. You can add columns, remove columns and re-arrange the order of the columns.

Property	Description
Column	This shows the selected column name as it appears in the database table.
Name	Users can define an alias for the selected column and this Name is used to refer the selected column for the rest of the lobby designers.
Data Type	This indicates that, what is the data type of the selected column and the most of the cases it automatically detect by the application. Note : In certain scenarios there could be data type mismatches, In such cases, manually select the data type from the data type dropdown menu. [More Info.]

Figure: Columns Section

There are two options to add a column.

• Click Add button and a new row will be created where you can manually type Column, Description and select Type.
• Click View Columns button which opens the column chooser dialog. All available columns in the view are displayed in the left side of the dialog, select a column and click on the swap button in the middle to add it.

Figure:Column Chooser

Data Type Mismatch

When creating an SQL data source in IFS Cloud, you can select any data type from the database. IFS Cloud will automatically assign the corresponding database data type to the SQL data source. However, in some cases, there may be data type mismatches. In such instances, manual adjustment of the SQL data source's data type is necessary to prevent error prompts.

Example: Examining the following SQL data source, it contains two data columns (DATE_TYPE , DATE_TIME_TYPE) sharing the same data type (DATE).

But these two data columns possess two different characteristics.

DATE_TYPE: Date

DATE_TIME_TYPE: Date with Time stamp

If you utilize this data source in a lobby element without specifying the correct data type, it may trigger error messages. To avoid this, ensure to select the appropriate "Data Type" from the "Columns" section of the data source, as demonstrated below.

Information

In this section you can see/modify the author of the data source, define key words to make it easier to find the data source in the Data Source Designer and write a text with additional information about the page etc. The following is available:

Property	Description
Component	Component of the Data Source (By Default it will be 'CONFIG')
Author	Author of the Data Source
Keywords	Keywords which can be used when searching for the Data Source
Descriptive Text	Description about the Data Source
Last Modified	Last Modified Date and Time of the Data Source
Copied From	If the Data Source is a copy of another Data Source, this field shows the name of the Original Data Source

Figure:  Information Section

How To Use User Defined View In SQL Data Sources

In here we are trying to address the user case that, The users need to insert a new View to the database and use that View to create a new SQL Data Source. For this, need to do some pre arrangement before it uses in SQL Data Source Designer.

prerequisite : Install IFS Developer Studio and connects to relevent Database.

1. Open IFS Developer Studio IDE and open any .veiws file. Then you will see the section called ------PUBLIC VIEW DEFINITIONS------- .

2. Write the code that will create a new view in the database under that section. Ex: here we create a view with the name "Closed_Shop_Ord_View".

   

3. Then right click on the .views file and click on "Generate Code And Deploy ". If the view is successfully deployed to the database, you will get the message like below.

   

4. Then login to the IFS Cloud and navigate to the page called "Refresh Server Cache" which is under the navigation entry

   "Solution Manager" > "System Information and Utilities".

​

1. Select the list item "Dictionary" and click on "Refresh Cache" button. Once you click, will appear a dialog box and then press "Ok". This will refresh the Dictionary cache and rebuild.

   

2. To check the build status, in the same page("Refresh Server Cache" page) select the "Dictionary" item and click the "Background Jobs" button. That will navigate you to a new page and will display the current building state of the selected cache. The status will change "Posted" > "Executing" > "Ready" respectively. To get the latest state of the build, click on the page refresh button from time to time.

3. Once after the status is set to "Ready", you are ready to use the created view in the Data Source Designer.