# Lookup

**URL:** <https://forum.xeelo.com/t/lookup/388>\
**Category:** Admin Section\
**Created:** [March 13, 2018, 2:52pm UTC](https://forum.xeelo.com/t/lookup/388 "2018-03-13T14:52:59Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![michal.jurnik](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.xeelo.com/michal.jurnik/32/1071_2.png) [@michal.jurnik](https://forum.xeelo.com/u/michal.jurnik)\
**Post date:** [March 13, 2018, 2:52pm UTC](https://forum.xeelo.com/t/lookup/388/1 "2018-03-13T14:52:59Z")

</div>

The main purpose of using lookup is to return value according to specified key or numeric range. It can be set on **Object Template Line** as client lookup or through **Server-lookup** calculation as server lookup.

**Fields header**

| Field | Description | Restrictions |
| --- | --- | --- |
| Name | Name of the lookup | maximum 50 characters |
| Matching Type | It allows to choose between exact string and numeric range. 
- Exact string is just a key that should matches in a formula. I.E.: Vendor ID from the picture above. 
- Numeric range type defines ranges that should match with provided number. The key has to fall in the range. I.E.: 1-5, 6-10, 10-100. In case the key is number 3, it falls into the first range 1-5 and return the value according it 

 | |

**Fields Values**

| Field | Description | Restrictions |
| --- | --- | --- |
| Source FROM value object line | Primary key value that is matching with key (Vednor ID). Value for definition of the start of numeric range if the **numeric range** is selected | maximum 255 characters |
| Source TO value object line | Secondary key value for definition of the end of numeric range. It's not applied if the **exact string** is selected | maximum 255 characters |
| Return value object line | Return value | maximum 255 characters |
| Filter | Filter that can be used to selected only some of the value. | maximum 255 characters |

&nbsp;
## Filling

There are three ways of filling:

1. Manually by populating attributes - just fill the attributes of lookup in **Values**
2. Automatically by mapping attributes to specific object - **Lookup Object**
3. Automatically by executing SQL statement - **Lookup External**

### Values

Nothing else than adding few values in lookup.

 ![2018-03-13_15-52-01](https://canada1.discourse-cdn.com/flex027/uploads/xeelo/original/1X/26593a8229ee7f3d633006d82b88013ef2ad464a.png)

### Lookup Object

Unlike the manually filling, automatically is very often used in the real life. The reason is that it allows to the system to transfer value from **Object A** to **Object B**. In picture below, there is example of transferring Vendor Title (return value) according to Vendor ID (key), chosen in Reference.

 ![2018-03-13_15-18-30](https://canada1.discourse-cdn.com/flex027/uploads/xeelo/original/1X/d989fd9e43c1aa6da1c9d68d99af36a4bbde8864.png)

### Lookup External

Another way to fill lookup is by using external source, more precisely getting data from external database. This option is possible for “ **On premise** ” solutions. If Xeelo is installed on a server where also other external sources are then it is possible to fill lookup with data from them. If Xeelo is installed on a separate server then desired external source, but the server is linked to the server where Xeelo is installed, it would be still possible to get data from that ex. source. This **would not work on Azure** as this solution is entirely enclosed.  
The data can be obtained by **Select statement** :

 ![50](https://canada1.discourse-cdn.com/flex027/uploads/xeelo/original/1X/43367a0f782b2133f15689b5aa1bbe22072d2b3a.png)

**Fields**

| Field | Description | Restrictions |
| --- | --- | --- |
| External lookup name | User friendly name of the external source | maximum 50 characters |
| From | Definition of `from` clause of the query for external source | &nbsp; |
| Value | Definition of **value** as part of `select` statement. | &nbsp; |
| Value 1 | Definition of **value 1** (range) as part of `select` statement. | &nbsp; |
| Return | Definition of **return** as part of `select` statement. | &nbsp; |
| Filter | Definition of **filter** as part of `select` statement. | &nbsp; |

Based on the above values the select statement is build as: `select , , , from `

**Example 1:**

Easy way to get desired data with a simple Select statement

 ![09](https://canada1.discourse-cdn.com/flex027/uploads/xeelo/original/1X/858d247662a6a9103a1994c7bdb4852aeed69181.png)

This query will fill lookup with **Vendor ID** (Value) and **Title** (Return).

**Example 2:**

Sometimes you have to set a complex Select statement to get desired data. It is possible to do that but you have to put the entire Select statement in the “ **From** ” field.

 ![08](https://canada1.discourse-cdn.com/flex027/uploads/xeelo/original/1X/9412290c0d7ffa821eb4d2cea719d0c14b63ac26.png)

**From:** `(Select V.VendorID as A,T.Title as B From Server1.SQLDB.dbo.Vendor V Left Join Server1.SQLDB.dbo.Transaction T on (V.VendorID = T.VendorID)) as C`

This statement will return again **Vendor ID** (Value) from table _Vendor_ and **Title** (Return) from table _Transaction_.