Smart Fields

Smart Fields

Smart Fields are technically virtual fields in a table that performs a function when the query against the data base is executed.

Smart Fields can for instance be a calculation or formatting of fields in the same table. It can also perform operations on fields in imported tables. - All specified in the actual table, and thereby enables functionality without any programming.

Defining Smart Fields

A Smart Field is defined as any other field in a table with it's data format via the short name. Though the table field has an Overriding Type that is a function, like Substring, Calculation or Expression. Then in the Extended Properties, the logic of the field is defined as a formula. See the examples below:

Examples of Smart Fields

To simplify the visibility and understanding, the examples are specified in XML.

Field defined as imported field

This field "SupplierLanguage " is defined according the shortname="LNCD" in a table, but is overridden as an "20006 - Expression" to pinpoint the formula of the imported field "IDLNCD" via the import "CIDMASIISUNO". This means that the import must also exist in the table as well be imported in the Data model, for this to work.

<Column alias="WLN01" align="" autokey="false" copycolumn="false" defaultvalue="" description="Language" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="79" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="LNCD" type="20006" value="SupplierLanguage">

            <Properties>

                <Property name="formula" value="(${CIDMASIISUNO}.IDLNCD)"/>

            </Properties>

        </Column>

Calculation of fields

This field "RemainingQty " is defined as it's own short name, with an Overriding Type of "20001-Calculation". The short column with the same name is of the type "Mvx Quantity", which the requires properties to define the number of decimals, why both the field and constraint for that are specified. The formula then specifies the value of the field to be a subtraction of OSDLQT-OSRCQT, which are fields in the same table. 

<Column alias="WOR06" align="" autokey="false" copycolumn="true" defaultvalue="" description="Ordered quantity - alternate U/M" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="37" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="ORQA" type="20001" value="RemainingQty">

            <Properties>

                <Property name="column" value="MMDCCD"/>

                <Property name="constraint" value="OSITNO"/>

                <Property name="formula" value="(${OSDLQT} - ${OSRCQT})"/>

            </Properties>

        </Column>

Calculation of imported fields

This field "TotalScrap " is defined as it's own short name, with an Overriding Type of "20001-Calculation". The short column with the same name is of the type "Mvx Price", which the requires properties to define the number of decimals, why both the field and constraint for that are specified. The formula then specifies the value of the field to be a sum of three fields (KOSSU1-3) in in the the field "TotalScrap" via the ${alias} expression. 

  <Column alias="" align="" autokey="false" copycolumn="true" defaultvalue="" description="Total scrap" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="61" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="TotalScrap" type="20001" value="TotalScrap">

            <Properties>

                <Property name="column" value="M9CPDC"/>

                <Property name="constraint" value="MITFACFK"/>

                <Property name="formula" value="(${alias}.KOSSU1 + ${alias}.KOSSU2 + ${alias}.KOSSU3)"/>

            </Properties>

        </Column>

Case condition of field value

The field "LocalCurrency" gets it value from different other fields in the table, based on a case condition. In this case the field is defined with an overriding type of  "20001-Calculation" and a formula that compares if ESARAT (exchange rate) is larger than 0, then the fields value shall be equal to the ESARAT times ESCUAM (currency amount) value, otherwise it shall just be equal to the ESCUAM value.

  <Column alias="WACA1" align="" autokey="false" copycolumn="true" defaultvalue="" description="Recorded amount" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="69" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="ACAM" type="20001" value="LocalCurrency">

            <Properties>

                <Property name="formula" value="(CASE WHEN ${ESARAT} &gt; 0 THEN ${ESARAT} * ${ESCUAM} ELSE ${ESCUAM} END)"/>

            </Properties>

        </Column>

Case condition where field name the same as import

The field "OrderLineNumberAlpha" is in this case also defined as an import in the table and has an Overriding Type of "20006 - Expression" . The formula specifies that the value of the field shall be 1000000-the imported field KORORL formatted as 7 chars as long as the KORORL-field is larger than zero, otherwise it shall be '000'.

 <Column alias="" align="" autokey="false" copycolumn="true" defaultvalue="" description="Order line number alpha" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="59" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="OrderLineNumberAlpha" type="20006" value="OrderLineNumberAlpha">

            <Properties>

                <Property name="formula" value="(CASE WHEN ${alias}.KORORL &gt; 0 THEN SUBSTR(CAST ((1000000 + ${alias}.KORORL) AS CHAR (7)), 3, 3) ELSE '000' END)"/>

            </Properties>

        </Column>

Substring of field

The field "MHSUNO" is here defined according to shortname "SUNO", with an Overriding Type of "200202- Substring". By setting the properties of the what field (HMITO) with length, offset and 

 <Column alias="WSU01" align="" autokey="false" copycolumn="true" defaultvalue="" description="Supplier" displaylength="" maintenance="" maxlength="" overridingcolumn="" position="41" promptentrypoint="" promptentrytab="" promptindex="" prompttask="" promptvisibility="" ruleset="" shortname="SUNO" type="200202" value="MHSUNO">

            <Properties>

                <Property name="column" value="HMITNO"/>

                <Property name="formula" value="substr(${HMITNO},1,10)"/>

                <Property name="length" value="10"/>

                <Property name="offset" value="1"/>

                <Property name="storedas" value="char"/>

                <Property name="trimtrailing" value="true"/>

            </Properties>

        </Column>