Table Constraint Definitions


In this blog I’ll explain the concept of table constraints as it is implemented in Constraint-based product configuration in Microsoft Dynamics AX 2012.

 

Definition

A table constraint lets you specify the valid combinations of attribute values that can be assigned when configuring a product configuration model. The structure of a table constraint consists of one or more columns and one or more rows. Each column is assigned to an attribute type that specifies the attribute values that can be combined in the rows. Each row defines a valid combination of the attribute values for the attribute type.

 

Constraint based product configuration for Microsoft Dynamics AX 2012 supports two types of table constraints:

 

  • User-defined – You can manually specify the combinations of attribute values that are valid for the product configuration model.
  • System-defined – You can specify the table in the Application Object Tree (AOT) that will define the combinations of attribute values that are valid for the product configuration model.

 

Example

The following example shows a table constraint that could be used for a television. The table constraint is described as an expression and as a table. The expression describes the attribute types and values for Color, Size, and IsLCD. The table constraint contains three columns with the valid combinations of the attribute values for each attribute type.

 

Attributes types and values written as an expression

 

IsLCD : { True, False }      

 

Color : { “Red”, “Green”, “Blue”, “Black” }     

 

Size : [ 42 .. 55 ]   

 

Table constraint

 

The Color, Size and IsLCD attribute types serve as the column headings in the table constraint definition. The rows specify the valid combinations of attribute values.

 

Column_Color = Color

 

Column_Size = Size

 

Column_IsLCD = IsLCD

 

 Column_Color

 Column_Size

 Column_IsLCD

 Red

 42

 True

 Red

 55

 True

 Green

 42

 True

 Blue

 55

 False

Written as a Boolean expression, the table would look as follows: 

 

(Color == “Red” And Size == 42 And IsLCD == True )

Or

(Color == “Red” And Size == 55 And IsLCD == True )

Or

(Color == “Green” And Size == 42 And IsLCD == True )

Or

(Color == “Blue” And Size == 55 And IsLCD == False )

 

There are some important things to note about the attribute types that are used in the example:

 

  • Color:  This attribute type includes the attribute value “Black.” However, because Black is not specified in a row it cannot be assigned. In a case like this, consider whether the table constraint or attribute type are incorrect. The two rows for the attribute value “Red” implies that if Red is assigned then Size can either be 42 or 55. This also means that the IsLCD attribute is always True because the values for Red and IsLCD is True are not combined in a row.
  • Size:  Although the attribute type is defined as an integer with an interval from 42 to 55, only the values 42 and 55 are valid attribute values because these are the only values within the interval for which a row is defined.
  • IsLCD:  If False is assigned to an attribute value that is included in the IsLCD column, then only “Blue” and 55 are valid assignments for the respective attribute values that are included in the Color and Size columns.

 

The difference between System and User defined table constraints

Although the same rules apply to both system-defined and user-defined table constraints, there are some differences in behavior. In this section I’ll elaborate on the key differences, but focus primarily on system defined table constraints.

 

User defined

User defined table constraints let you manually define the combinations of attribute values that you want to allow for a product configuration model. In Figure 1, the Table fields and Allowed combinations FastTabs of the Edit table constraint form show an example how attribute types and values are combined in a user defined table constraint.

 

Figure 1

System defined

For system defined table constraints, you select a table or view which is defined in the AOT. You then specify the relation between the columns of the table constraint and the fields that are included in the table or view in the AOT. This table or view provides the value combinations for the table constraint. You can also add a filter on the AOT table or view by adding a database query that selects rows by adding ranges to query.

 

One important thing to know about system defined table constraint is that at runtime the system changes the type definition of any attribute that is associated with a column. For example, the value for an enumerated text attribute will be replaced with the set of distinct values for the column in the AOT table. For an integer-based attribute, the ranges will be replaced with the maximum and minimum values for the column.

 

 

Figure 2

Example

The InventTable table in Microsoft Dynamics AX 2012 contains the following data in the AltItemId and BOMLevel fields.

 

 AltItemId

 BOMLevel

 LCD_42

 3

 Plasma_55

 1

 LCD_55

 7

 

In order to use the InventTable as the source of a system defined table constraint for a component in a product configuration model, the following must be set up:

 

  • Two attribute types, one of the type Text and another of the type Integer. The Text attribute type must have at least one value. The Integer attribute type must have a value range.
  • A table constraint definition, as illustrated in Figure 2. The table constraint that will reference the definition must be created for a component. The component must have attributes that match the attribute types that are referenced by the columns of the table constraint definition.

 

For a component that uses a system defined table constraint, the system replaces the following values:

  • For the attribute type of the attribute that is matched to the AltItemId column, the values are replaced with { “LCD_42”, “Plasma_55”, “LCD_55” }.
  • For the attribute type of the attribute that is matched to the BOMLevel, the range is replaced with [ 1 .. 7 ].

Comments (13)

  1. Evert J Bos says:

    I do exactly this same thing. My attribute types of my attribute types are both TEXT (item id and item name)

    Yet I am not able to map the attributes to the Inventtable field names ItemID and Itemname.

    The picklist remains always empty.When I type it in, it says "the attribute types must be identical'. I think it must mean "text has to correspond with text".. and that is the case. Maybe it means something else…

    This situation demonstrates that there is confusion in the terminology in the configurator.

    I create attributes for a model. everybody gets that. If I hear the world "attribute type"one would intuitively think that we mean "text, binary, integer" etc but no, the attribute type is also the variable itself. So the attribute type has a certain attribute type.

  2. Evert J Bos says:

    Turns out it does mean the "attribute type" in the second sense.

    The attributes that you use in the table constraint have to be the same ones that you want to map.

    In hindsight this is obvious.

    Yet a strange fact remains. When I map my inventtable fields to my attributes, the atributes that are defined for the component do not all appear in the pick list.

  3. SverreThune says:

    We do a type mapping and only show the attributes which have the same attribute type as the one used when defining the system defined table constraint.

  4. jedski says:

    Can you set a default for a system defined table constraint? For Example: If I set an attribute type as text for a Unit of Measure system defined Table constraint. Can I set a default in that field? Would I put at least one entry in the attribute UOM and set that as default? Will the system defined values still be filled in to present the user with actual values if needed?

  5. jedski says:

    Answered my own question. After rereading this blog. I added one value to the Attribute and then set that as default. Presents the user with the default value, but when the drop down is selected all system defined values are present.

  6. SverreThune says:

    Hi Jedski

    Good that you are able to figure it out youself 🙂

    If I should add anything then it might be that as the actual "replacement" of the attribute values for an attribute type happens at the configuration time, we do not have the possibility to select a default value from the column in the table used for the table constraint.

    If I choose a default value from the attribute type and launch the configurator I get a warning that the value I selected is not available and no default value is displayed.

    This in on AX 2012 R3

    Thanks,

    Sverre

  7. Jconerp says:

    Would like input on the following:  In my model I have a BOM component that could be one of about 50 items.  The attributes of those items determine which one gets picked during configuration.   In order to make that work I have created an Attribute Type for this option and added all of the items as values to that list.  At that point I can use a system constraint to get to the inventable and lookup the correct item/attribute combination.  My question is this – is there a way to do this without having to add all of the items as values to the Attribute type – and if so how can you do that?

  8. SverreThune says:

    Hi

    It should be possible to use a system defined table constraint to pick the right item, it all depends on the attributes required to identify said item. If the attributes represent fields in the InventTable that can be referenced as columns in the table constraint then it can be done. Please observe that decimal numbers can't be used in neither table constraints nor expression constraints.

    Thanks,

    Sverre

  9. Tippawan Kreangkriwanich says:

    Hi,

    I would like to get your guide on how I can achieve below requirement.

    May i know whether this requirement can be achieve or not by creating a new table and use it as 'System define table constraint'.

    Example;

    1. 5 Product attributes: Mat-1, Mat-2, Mat-3, Time-1, Time-2
    2. When user select value in Mat-1, Mat-2, Mat-3, then system should default value in Time-1, Time-2 with related values of Mat-123.

    If i use 'User define table constraint', it able to do as requirement above.

    But I would want to use'System define' as it easier to maintain and upload data.

    When I change to use 'System define table constraint', I am not even able to run 'Test' screen to test my setup. Even 'Validate' button also not working. But if I delete that Table constraint, then 'Test' and 'Validate' button is working.

    • Table had been created with field type that match to attribute type.
  10. Mapped 5 fields correctly to each attribute.

  11. Kindly guide me on how i can make it happen.

    Thanks,

    Tippawan

  • Hi Tippawan,

    First we need to find out where your error is located. If you can indeed get it to work with a user defined table constraint, then it should be possible to do the same thing with a system defined one.

    Try adding the attributes one at a time and then test when you are hitting the error.

    Thanks

    Dennis

  • anra says:

    hy,

    have the following requirements. my customer has to show prices from items at configuratoin time at the Interface. loading item table works, but no decimal values can be loaded because only enum, text and int are permitted by design. any workarounds how to load decimal, currency values via table constraint typ system?

    mfg anra

  • Hemant Kumthekar says:

    Hi Dennis,

    I tried to build a custom table with 6 fields that were defined as String and then defined 6 attributes to match the table fields. Defined the attributes as text  with one value underneath each of them. Later in the PC model,  I added the attributes and also created a table constraint as system define and used the custom table created and matched the fields to the attributes. Later on the Constraints fast tab when I click validate I am getting the following info log..

    Using AX2012R3.

    Thanks in advance.

    ================================================================================

    system.NullReferenceException: Object reference not set to an instance of an object.

      at Microsoft.Dynamics.Ax.Xpp.ReflectionCallHelper.MakeNewObjIntPtr(String typeName, IntPtr intPtr)

      at Microsoft.Dynamics.Ax.Xpp.XppObjectBase.callReturn(KernelCallReturnVal returnVal)

      at Microsoft.Dynamics.Ax.Xpp.XppObjectBase.Call(String methodName, Object[] parameters, Type[] types, Object[] varargs)

      at Dynamics.Ax.Application.QueryRun.Get(Int32 table)

      at Dynamics.Ax.Application.PCXmlSessionWriterDatabaseRelationType.Writeattributetypedomainil(String _attributeTypeName, PCTableConstraintColumnCategoryAttribute _tableConstraintColumnCategoryAttribute, PCXmlSessionDatabaseRelationTypes _parsedSystemTableConstraintTypes, PCXmlWriter _componentTreeWriter) in PCXmlSessionWriterDatabaseRelationType.writeAttributeTypeDomainIL.xpp:line 31

      at Dynamics.Ax.Application.PCXmlSessionWriterDatabaseRelationType.Writefornewtypeil(String _attributeTypeName, PCTableConstraintColumnCategoryAttribute _tableConstraintColumnCategoryAttribute, PCXmlSessionDatabaseRelationTypes _parsedSystemTableConstraintTypes, PCXmlWriter _componentTreeWriter) in PCXmlSessionWriterDatabaseRelationType.writeForNewTypeIL.xpp:line 26

      at Dynamics.Ax.Application.PCXmlSessionWriterDatabaseRelationType.Writeil(String _attributeTypeName, PCTableConstraintColumnCategoryAttribute _tableConstraintColumnCategoryAttribute, PCXmlSessionDatabaseRelationTypes _parsedSystemTableConstraintTypes, PCXmlWriter _componentTreeWriter) in PCXmlSessionWriterDatabaseRelationType.writeIL.xpp:line 37

    ================================================================================

  • Hemant Kumthekar says:

    Resolved the issue by performing Incremental CIL.