Table Tag Builder

The <* TABLE (…) *> tag is used to fetch data from customer uploaded data tables. It allows users to use SQL aggregate functions: MIN, MAX, AVG, SUM, COUNT, as well as match upper and match lower. It also has the ability to retrieve records greater than some entered value, and so on. The TABLE tag supports the following functions:

  • EXACT
  • MIN
  • MAX
  • COUNT
  • AVERAGE

These functions are described in detail below:

EXACT
This is a standard and default function. It returns anything that is being selected, without any data filtering. It's very similar to current AUX tag behavior, the difference is that AUX must use ”=” operator for conditions. Typical TABLE tag based on EXACT function will look like this:

<* TABLE (SELECT price FROM priceBook WHERE productId = 5 ) *>


As can be seen, EXACT function reveals an classic SQL select statement. If that statement returns more than one record from DB, only the first one returned will be the result of <* TABLE(…) *> tag. If you need the last one, you can change sorting direction. (Described later in this doc). Since there is no guarantee on order SQL server returns records, if you don't use sorting, it's best practice to prepare data and use queries in such way that they return unique records.

MIN
This is a function that returns minimal value from all the results that are meeting given criteria. Typical TABLE tag based on MIN function will look like this:

<* TABLE (SELECT MIN (price) FROM priceBook WHERE productId = 5 ) *>


MIN function returns one record, the minimal one among all the records that satisfy given conditions.

MAX
This is a function that returns maximal value from all the results that are meeting given criteria. Typical TABLE tag based on MAX function will look like this:

<* TABLE (SELECT MAX (price) FROM priceBook WHERE productId = 5 ) *>


MAX function returns one record, the greatest one among all the records that satisfy given conditions.

COUNT
This is a function that counts all records that meet given conditions. Typical TABLE tag based on COUNT function will look like this:

<* TABLE (SELECT COUNT (price) FROM priceBook WHERE productId = 5 ) *>


COUNT function returns one record.

AVERAGE
This is a function that selects all records that meet given conditions, and then calculates average value from the results. Typical TABLE tag based on AVERAGE function will look like this:

<* TABLE (SELECT AVG (price) FROM priceBook WHERE productId = 5 ) *>


AVERAGE function returns one record.

Sorting Results


Results that are being returned can be sorted Ascending, Descending or not sorted at all. Only the column that contains results can be sorted. When match upper or match lower condition operator is used, then sorting is first performed by the column that is used in condition, and then by resulting column.

Conditions


Fetching data from SQL tables would be quite useless if you couldn't filter it by some criteria. <* TABLE(…) *> tag supports adding multiple conditions to your data query. Typical condition is defined by the name of the column being tested, and by the matching criteria. Typical example would be: “give me price of the product which has a catalog code 'ABC'”. Price would be defined in one column, and every price has its catalog code in the “catalog_code” column. So our criteria will tell that catalog_code should be “ABC”. This is done in sql by following query:
SELECT price FROM table WHERE catalog_code='abc'
Now, we can use multiple conditions: I.e. Maybe quantity affects the price. Then we should treat quantity column in the same table, the sql will now look like this:
SELECT price FROM table WHERE catalog_code='abc' AND quantity>5
This way, we can add as many conditions as we like. They will all be concatenated with AND operator, meaning that ALL the conditions must be met in order to fetch the data.

User Interface


Table tag builder is invoked from the code builder. On the list of tags, there is a TABLE tag listed. When it is selected, a new button appears, that says “Construct Table tag”. This button is visible only when the TABLE tag is selected in the list of tags.

Construct Table Tag


When you select TABLE tag from the list of available tags, a new button appears next to the Insert variable button – Construct Table Tag. Clicking Construct Table Tag opens the Table Tag Builder window.

Figure A - Table Tag Builder Window


In table tag builder, you first select table from which you will fetch your data. It's the dropdown labeled From Table. Next to the dropdown is the Preview Table Data button, which enables you to preview first 25 rows of your uploaded table. It is meant to allow administrators constructing TABLE tags by showing them data sample of the selected table. On the picture below, you can see how data preview looks like:

Preview Table Screen


The table shows only first 25 records. It is meant to preview table structure rather than check table data. Since custom tables can be huge, it is not wise to fetch large amounts of data since it would most probably result in timeouts and stress to server.
Now look at Figure A. After selecting table, we need to select function that will return data. The dropdown for this is labeled Return. The default is EXACT function. On the screenshot in Figure A, Exact is selected. Next, the user needs to decide what data is needed from the table. This is done by selecting a column from which you wish to fetch the data. This is done via the third dropdown on Figure A, which is labeled From column.
The data can be sorted by that column, but it is optional. If you like, you may choose sort type to be ascending, descending, or to leave ti to “none” which is a default option. If you want sorting to be precise, you need to define the data type in that column: Is it numbers, text, or dates ? This need to be set correctly, or else you can get incorrect sorting. I.e. If you sort numbers as if they were text, you will get 1, 10, 2, 3, 4, 5,…
Now that you defined the column you desire, you need to set the conditions on that column,in order to fetch only rows that you need. You can add as many conditions as you wish. New conditions are added by clicking on “add new condition” link below the last condition. That will add another set of fields that you will populate and in this way, define another condition. Figure A shows a screen where one condition is defined. A typical condition is being built by selecting column name from source table, then by choosing an operator used for data comparison, and then defining a reference value. I.e. “I want all records where part number is 'abcd' ” - just as it is defined in condition on Figure A. The reference value can be a constant – like “abcd” in sample above, or can be a dynamic formula, like

<* CATCODE(attribute) *>

You have a syntax builder button there so you can easily construct formulas for your conditions. Notice that every condition has a “column type” - It is needed because the sql that fetches your data is different when doing comparison on numeric values / text values / date values. While testing formula, if you get “Error converting data type…” messages, please re-check your column types, because there is a possibility that you are fetching a column whose values cannot be converted to data type you desire. I.e. Cannot convert “abc” to numeric type – what's the number that would correspond to that string?
After defining the data and the conditions, you can click on “build formula” link, and you can preview the resulting TABLE tag constructed, as shown on Figure A. The area that displays your generated table tag is editable, so you may alter the SQL yourself. But be aware: if you altered SQL manually, and then click on “build formula” link again, your complete formula will be lost and replaced by newly generated table tag formula, based on what you selected from dropdowns. So make sure that you do alter SQL manually only once you defined it enough with table tag builder. You can use the “test formula” link when you finish editing the TABLE tag. When you click it, it will execute your SQL and present you the data it returned as shown in the figure below.

Formula test OK, with result


If you had an error in your SQL, it will not return any data. Instead of that, you would be given the error message, which might give you a clue what you did wrong.

Formula test failed – displaying error message


One note on testing your TABLE tags: while testing, if you used any formulas in your conditions, you MUST replace them temporarily with values you expect formulas to evaluate to. I.e. If you had a condition that said “product_color = ';#;<* CATCODE(selected_color_attribute) *>;#;'” than you must alter your condition to say: “product_color = 'blue' “ This is a must since the CATCODE formula (and any other) is context dependent, and cannot be parsed in table tag builder. In the sample above, it simply does not know about “selected_color_attribute” attribute, so it can't return its catalog code. Once you are satisfied with results, you can click on “use this formula' button, and your TABLE tag will be checked for errors, and if it's OK, transferred to syntax checker that invoked the table tag builder.

More on Conditions


Now that we understand conditions and how are they built, we can describe more precisely the operators used in conditions. Please note that, depending on data types you expect in a condition column, not all condition operators are available. Supported operators are: (column types are shown in italic)

  • = ( data types: text; number; date)
  • > ( data types: number; date)
  • < ( data types: number; date)
  • >= ( data types: number; date)
  • ⇐ ( data types: number; date)
  • begins with ( data types: text;)
  • ends with ( data types: text;)
  • matches (“like” in sql – use for strings) ( data types: text;)
  • match lower (inclusive) ( data types: number; date)
  • match lower (non-inclusive) ( data types: number; date)
  • match upper (inclusive) ( data types: number; date)
  • match lower (non-inclusive) ( data types: number; date)


Standard operators like ”=”, or ”<” or ”>=” are pretty self-explanatory.

Begins with
This operator is typically used to search string columns. It is used to search if there is data in given column that begins with string provided as a parameter. This only work on text column types. It will be translated to sql LIKE clause, with ”%” on the end – searching for strings that are beginning with preset parameter and can end with anything. Example: SELECT price FROM products WHERE product_name like 'ab%'

Ends with
This operator is typically used to search string columns. It is used to search if there is data in given column that ends with string provided as a parameter. This only work on text column types. It will be translated to sql LIKE clause, with ”%” on the beginning – searching for strings that are ending with preset parameter and can begin with anything. Example: SELECT price FROM products WHERE product_name like '%ab'

Matches
This operator is typically used to search string columns. It is used to search if there is data in given column that contains string provided as a parameter. This only work on text column types. It will be translated to sql LIKE clause, with ”%” on the beginning and with ”%” on the end– searching for strings that are beginning with anything and ending with anything, but must contain provided string. Example: SELECT price FROM products WHERE product_name like '%ab%'

Match lower
This only work on Numeric and Date column types. This operator is typically used to do some sort of rounding of the input parameter in condition. This will be best described on an example: Let's say we have a table that contains prices for products. It will typically have 3 columns: product_catalog_code, order_quantity, and price. For a given product, price may vary depending on quantity. But in prices table, we have prices defined for quantities 1, 5, 10, 50. So, if user pieces, since he is not eligible to use price for 10 pcs. So, since we don't have “7” as a value for quantity in prices table, we need to set up a condition that will use nearest lower value for quantity column. We will in this case select column “order_quantity”, operator to “match lower” and we will enter a formula that reads “quantity” attribute. (that will be the source of “7” in the example above). If we click on “rebuild” button, we will get SQL that looks like: SELECT TOP 1 price FROM table WHERE quantity<7 ORDER BY quantity DESC This way we have set up the condition to form a price based on quantity. We still need additional condition that will identify product by part number. (that condition will use typical ”=” operator). When this condition is also added, we will have a complete query:
SELECT TOP 1 price FROM table WHERE quantity<7 AND product_catalog_code='abc' ORDER BY quantity DESC
The query will finally fetch the price for given catalog code and quantity “5”. (first one less than input val (7)).

Inclusive and non-inclusive options define if you like to use ”<” operator or ”< =” operator. Whether you like the formula to include boundary from condition, or not.

Match Upper
This only works on Numeric and Date column types. This operator is very similar to “match lower”. The only difference is that it gets the first record that has greater value than the given reference. If we look at the example above, it would select the row with quantity value of 10. (first one greater than 7 ). The sql will look like this: SELECT TOP 1 price FROM table WHERE quantity>7 ORDER BY quantity ASC

You are here: CallidusCloud CPQ Online HelpAdmin Page HelpAdministrationCode BuilderTable Tag Builder