vrijdag 9 oktober 2009

Joining Data Sources on Forms

Source: http://msdn.microsoft.com/en-us/library/aa608858.aspx

Joining Data Sources to Function as a Single Data Source:
Set the LinkType property on the secondary data source to InnerJoin, OuterJoin, ExistJoin, or NotExistJoin.

Joining Data Sources that have a Parent/Child Relationship:
Set the LinkType property on the child data source to Active, Passive, or Delayed.

  1. Passive
    Linked child data sources are not updated automatically.
    Updates of the child data source must be programmed on the active method of the master data source.

  2. Delayed
    A pause is inserted before linked child data sources are updated.
    This enables faster navigation in the parent data source because the records from child data sources are not updated immediately. For example, the user could be scrolling past several orders without immediately seeing each order lines.

  3. Active
    The child data source is updated immediately when a new record in the parent data source is selected.
    Continuous updates consume lots of resources.

  4. InnerJoin
    Selects records from the main table that have matching records in the joined table and vice versa.
    There is one record for each match.
    Records without related records in the other data source are eliminated from the result.

  5. OuterJoin
    Selects records from the main table whether they have matching records in the joined table.

  6. ExistJoin
    Selects a record from the main table for each matching record in the joined table.
    The differences between InnerJoin and ExistJoin are as follows:
    - When the join type is ExistJoin, the search ends after the first match has been found.
    - When the join type is InnerJoin, all matching records are searched for.

  7. NotExistJoin
    Select records from the main table that do not have a match in the joined table.

donderdag 8 oktober 2009

DialogField.fieldControl - the hidden code...

In http://axaptapedia.com/Mandatory_DialogField I came across a method "mandatory" that is not shown by MorphX IntelliSense.
It seems that there is more "hidden" stuff to the fieldControl of a DialogField.

For example, I needed to get the label from a DialogField that I've added.
Setting the label is done with myDialogField.label(myLabelText)
This method is defined as void label(FieldLabel label)
And it is shown by IntelliSense when we type myDialogField.

But getting the label is a different story.
There is no getLabel, and the label method is implemented as void, so there's no return value.
The trick is to go through the fieldControl.
BUT! The method we need, which is label(), is not shown by IntelliSense when we type myDialogField.fieldControl().
That doesn't mean it not there though.
So, getting the label is done like this: myDialogField.fieldControl().label()

It seems that most of the properties of an object can be retrieved this way, even if they don't show up in IntelliSense.

I've discovered a plausible reason as to why the methods/properties don't show up in IntelliSense.
The fieldControl() method returns an Object.
Of course, Object doesn't have a label property, mandatory method, etc...
That's probably why it doesn't show up in IntelliSense.

I suppose that when you execute fieldControl().label() against an object that doesn't have a label() method, there will be a runtime error.

Mandatory DialogField


Excerpt: (important!)
Mandatory property starts working only after a value is entered into the control.
What this means is that if you simply press OK right after the dialog is opened, you won't receive an error (regular forms works this way too)

dinsdag 6 oktober 2009

Connect AX User to Employee

Go to Administration > Forms > User
Form SysUserInfo opens.
Select the AX user that you want linked to an employee.
Click button User Relations.
Form SysCompanyUserInfo opens.
Select tab General.
Select radio button Internal User.
Select the employee that you want to link to the AX user from the Employee dropdown.

EmplTable contains a static method to convert an AX user to an Employee.
This method uses the data defined in the above description.
curuserid() retrieves the current AX User.

Table relation or Extended Data Type relation: which one is used?

Extended Data Type (EDT) definition:

Table Definition:

Field ADUEmplInventBuyerGroup.ItemBuyerGroupId  has ETD=ItemBuyerGroupId.

Table Browser for ADUEmplInventBuyerGroup: (in dutch)

When we right-click on field ItemBuyerGroupId > Go To Main Table Form.
Which table will be openened?
Will AX follow the EDT defined on the field and open table InventBuyerGroup?
Or will AX choose a table defined in the relations on ADUEmplInventBuyerGroup (InventBuyerGroup or VendTable) ?

The answer is:
AX always first looks at the relations defined on the table, and then on the EDT.
If however there is an ambiguous definition (= multiple relations are defined on the field), then AX follows the relation on the EDT.

In a flowchart:

donderdag 1 oktober 2009

Extracting X++ SQL Statement from AOT Query

When you hover the mouse over a datasource in an AOT query, the select statement is shown in a tooltip.
This select statement represents the query of the datasource above which you hover, and everything below it.
That means that if you hover over the InventDim datasource, the tooltip would show:
SELECT * FROM InventDim WHERE ProdJournalProd.InventDimId = InventDim.inventDimId AND ((NOT (inventBatchId = ' ')))
Often the length of the query-text does not fit the screen.
So, we need a way to extract this sql statement.

I had the following AOT query (inside a report):

Now, how to retrieve the sql statement from the top datasource (in our case, ProdTable) ?

My first attempt was this:
and yielded:
Query Query object 701eef8

The correct way is:
element.query().dataSourceName(literalstr(ProdTable)).toString()and yields:
SELECT * FROM ProdTable GROUP BY ProdTable.ProdId, ProdTable.InventRefType, ProdTable.InventRefId, ProdTable.Name, ProdTable.ItemId, ProdJournalTable.ProdId, InventDim.inventBatchId, InventDim.configId, InventDim.InventSizeId, InventDim.InventColorId WHERE ((ProdId = N'PO0800000403')) JOIN * FROM ProdJournalTable WHERE ProdTable.ProdId = ProdJournalTable.ProdId AND ((Posted = 1)) AND ((JournalType = 1)) JOIN JournalId, SUM(QtyGood) FROM ProdJournalProd WHERE ProdJournalTable.JournalId = ProdJournalProd.JournalId AND ProdJournalTable.JournalType = 1 AND ((NOT (QtyGood = 0))) JOIN * FROM InventDim WHERE ProdJournalProd.InventDimId = InventDim.inventDimId AND ((NOT (inventBatchId = ' ')))

In a form: