Microsoft Dynamics AX (Archived)

Validation rules

(0) Share

Report

Posted on by Community Member

Hi,

This is what I am looking at (MS2012).

I am to create a validation rule to the product and create some maintenance form for these rules.

I am using cases for this.

It is ok having like a query where you can "filter" the different table-fields, but how do I create and use this in like a "Validate" button from release products? Maintenance by admin for the "filters".

I have added a new record to table EngChgCaseCategoryRule and connected this to a new

case process and category in validation rules and this works fine. The question is how to use it and create/modify rules for this.

I am thinking something like this.

1. Lookup table for tables (how can I get all tables to select from in a drop-down list?).

2. Lookup table for fields (how can I connect this to my lookup table for tables and get a list of fields for the tables?).

3. Validate table where I connect tables and fields from my 2 lookup tables.

4. Make a query from the Validate table where I can set the conditions (if table1.field1 = "A" and table1.field2 != "4" and table2.field1 = "100" or table1.field1 = "B"...).

5. Use this "query" on buttons from different forms (released product etc.) like a check if the settings for this product is ok.

Have someone done something similar?

/Fredrik.

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (1)

Suggested answer

jasman 1,413 on at

Like (0)

Report

1 & 2) To get the tablenum of a table and fieldnum of a field in a table from a dropdown/lookup you can do:

int table,field;

table = picktable();

field = pcikfield(table);

Though performance is quite terrible the first time you use it.

3)

The table should contain two integer fields where you can store table and field.

4)

A query can then be made in code and a QueryRun object can be used to run it:

int table,field;

Query q;

QueryBuildDataSource qbds;

QueryRun qr;

Common common;

q = new Query();

qbds = SysQuery::findOrCreateDataSource(q,table);

SysQuery::findOrCreateRange(qbds,field).value("!10");

qr = new QueryRun(q);

while (qr.next())

{

common = qr.get(table);

info(strFmt("%1",common.(field)));

}

5) Run the query and decide what to do from results

Try the following little job:

static void Job167(Args _args)

{

int table,field;

Query q;

QueryBuildDataSource qbds;

QueryRun qr;

Common common;

table = pickTable();

field = pickField(table);

info(strFmt("%1 %2",table,field));

q = new Query();

qbds = SysQuery::findOrCreateDataSource(q,table);

SysQuery::findOrCreateRange(qbds,field).value("!10");

qr = new QueryRun(q);

while (qr.next())

{

common = qr.get(table);

info(strFmt("%1",common.(field)));

}

}

Was this reply helpful? Yes No
jasman 1,413 on at

Like (0)

Report

Please note the use of the Common object and the .() notation.

This allows the query to return a generic tablebuffer and then you can access the value of the field via the field number using

common.(field).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I will try the above and get back.

/Fredrik.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

So I will exchange table for my table name?

In this loop how can I compare the choosen

fields in the query with fields from my table?

I am to produce an error if something goes wrong

and ok if this goes ok.

while (qr.next())

{

common = qr.get(table);

info(strFmt("%1",common.(field)));

}

/Fredrik.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

Btw, how do I interpret this?

qbds = SysQuery::findOrCreateDataSource(q,table);

SysQuery::findOrCreateRange(qbds,field).value("!10");

I have a table which contains the content of TableName

and FieldName/FieldNameId from selections and want this

to be displayed, not my tables fields.

What will differ in the call? How will this affect the range?

If I set the range for this table can I save the ranges

so the next time I go into this query I will get my ranges?

Regards,

/Fredrik.

Was this reply helpful? Yes No
jasman 1,413 on at

Like (0)

Report

No.

Table should be the table id, which was why we wanted to use:

table = picktable();

You must of course ensure that the data type of the fields chosen in the query and the fields in your table are the same, when you want to compare them.

Let's do an example:

If you have a table, where the user can set up a reference to e.g. CustTable and the field AccountNum the table should contain two integer fields tableId and fieldId which in this case would contain 77 and 1 respectively.

A third field in the table of type string could the contain a value for comparison.

Let's say the value of that field is "1000".

So you have a record in your table containing:

77,1,"1000"

Now seeing in your table, that you have table no. 77 (CustTable) and field no. 1 you run your query, to find out if

int table,field;

Query q;

QueryBuildDataSource qbds;

QueryRun qr;

Common common;

select firstonly yourtable where <some criteria>;

table = yourtable.tableid;

field = yourtable.fieldid;

q = new Query();

qbds = SysQuery::findOrCreateDataSource(q,table);

SysQuery::findOrCreateRange(qbds,field).value("!10");

qr = new QueryRun(q);

while (qr.next())

{

common = qr.get(table);

if (common.(field) != yourTable.(fieldid))

info(strfmt(" %1 Not a match!",common.(fieldid)));

else

info(strfmt(" %1 is a match!",common.(fieldid)));

}

}

If your table must contain some kind of fixed fields of certain datatypes, you will have to either check that the datatypes of the field you get from the common buffer and the field in your table record is of the same type runtime.

Otherwise you will have to type cast the values to the same type to do the comparison.

Was this reply helpful? Yes No
jasman 1,413 on at

Like (0)

Report

SysQuery::findOrCreateDataSource simply finds a data source with tablenum table in the query. If a datasource with tableid table can not be found, it is created in the query.

Either way a QueryBuildDataSource object is returned for you to work with.

SysQuery::findOrCreateRange simply find a range in the QueryBuildDataSource qbds with fieldnum field. If a range can not be found a range will be created on the QueryBuildDataSource. Either way a QueryBuildRange object is returned for you to work with.

Thus

SysQuery::findOrCreateRange(qbds,field).value("!10");

Finds or creates a range on the querybuilddatasource and assigns the value of !10 to it.

This will make the querybuilddatasource filter out any records matching the value 10.

Was this reply helpful? Yes No
jasman 1,413 on at

Like (0)

Report

common is a generic table buffer that can be assigned to a records from any table.

It only exposes tableid and recid and not the rest of the fields, because that would require a table buffer of the correct type e.g. CustTable.

However the values of the fields can be accessed using the field number of the field with the notation:

common.(fieldNum).

So if I have retrieved a custTable record where the AccountNum is "12345" into my common buffer, I can get at the valye of the AccountNum field by writing:

common.(1)

as AccountNum has fieldno 1 (Id property of the field in the AOT) in the CustTable.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

Maybe I am unclear in my question or I am just stupid cause I don't get it to work at all. There are 2 parts in this. First I need a query to maintain and then I need a job (class/method) to run.

Here is the deal.

1.

Say I have a table called T_TableFields.

Here I have TableId (Integer), FieldId (Integer), Operand (is there an enum for this), Value (String 60) and Code (FreeTxt).

My entry is:

175 (InventTable), 2 (ItemId), EQ, 100, If (InventTable.ItemType == "Item"); info("Ok"); return true; (SQL)

This is the query.

int table,field;

Query q;

QueryBuildDataSource qbds;

QueryRun qr;

Common common;

q = new Query();

qbds = SysQuery::findOrCreateDataSource(q,table);

SysQuery::findOrCreateRange(qbds,field).value("!10");

qr = new QueryRun(q);

while (qr.next())

{

common = qr.get(table);

info(strFmt("%1",common.(field)));

}

(Range needs to come from T_TableFields also.)

If I run this I will get error (Table 0 doesn't exist).

What I want is my entry above displayed.

Maybe this is not the best solution?

2.

In here I will run a class/method (job to test) to check these variable entries.

I would like to compare InventTable with the entries in my T_TableFields.

First I need to check what table from T_TableFields it is related to.

Then I would like to compare InventTable with the T_TableField entry

InventTable.ItemId and if the entry in InventTable is "100" check if InventTable.ItemType is equal to "Item" and if so show the Infolog.

Ok?

/Fredrik.

Was this reply helpful? Yes No
Suggested answer

jasman 1,413 on at

Like (0)

Report

Make one class:

class Traverser

{

ExecutePermission perm;

}

private void doTraverse(T_TableFields _T_TableFields)

{

Query q;

QueryBuildDataSource qbds;

QueryRun qr;

Common common;

XppCompiler xppCompiler;

str code = @"%1";

boolean evalResult;

// Constrcut query

q = new Query();

qbds = SysQuery::findOrCreateDataSource(q,_T_TableFields._TableId);

SysQuery::findOrCreateRange(qbds,_T_TableFields._FieldId).value(this.relationalOperator(_T_TableFields.Operator)+_T_TableFields.Value);

// And run query

qr = new QueryRun(q);

while (qr.next())

{

common = qr.get(_T_TableFields._TableId);

if (_T_TableFields.Code)

{

xppCompiler = new XppCompiler();

code = strFmt(_T_TableFields.Code,common.(fieldNum(InventTable,ItemId)));

if (xppCompiler && xppCompiler.compile(code))

{

evalResult = xppCompiler.executeEx();

if (evalResult)

info(strFmt("Table: %1, field: %2, fieldvalue: %3 OK!",common.TableId,common.RecId,common.(_T_TableFields._FieldId)));

}

else

throw error(strFmt("Code in table T_TableFields can't compile. %1. Code:\n%2",xppCompiler.errorText(),code));

}

}

}

public void run()

{

T_TableFields t_tableFields;

perm = new ExecutePermission();

if (perm != null)

{

while select t_tableFields

{

this.doTraverse(T_TableFields);

}

}

CodeAccessPermission::revertAssert();

}

public static void main(Args _args)

{

Traverser traverser;

traverser = new traverser();

if (traverser)

{

traverser.run();

}

}

private str relationalOperator(str _op)

{

switch(_op)

{

case "EQ" : return "==";

case "NEQ" : return "!=";

case "GT" : return ">";

case "LT" : return "<";

}

return "==";

}

The table

T-TableFields

have the fields you mention where

_FieldId is integer and contains 2

_TableId is integer and contains 175

Code is memo-type

Operator is string and contains "NEQ"

Value is string and contains "10"

Code contains the code:

boolean evaluate()

{

InventTable inventTable = InventTable::find("%1");

If (InventTable.ItemType == ItemType::Item)

{

return true;

}

else

{

return false;

}

}

Or get the code here:

onedrive.live.com/redir;authkey=!AMe53BC6_9FE_KE&ithint=folder%2cxpo

Was this reply helpful? Yes No