Sunday, December 20, 2009

Data Set lookups on Enterprise Portal (EP)

I just spent a complete day trying to make a simple lookup for EP. The lookup was intended to be a lookup on EmplTable, with the ability of filtering on the name of the employees, and it is supposed to return the employee id.

The query behind is built on EmplTable with an inner join to DirPartyTable to get the name.

The easy solution would have been to make a lookup based on a new Data Set, but in trying that I came across two odd limitations in this type of lookup:

  1. You’ll only be able to sort the lookup on the field you want return.
  2. The filter will only show tables from the outer table. The joined tables doesn’t show up.

I find these two facts limiting the usability of the lookup in such degree, that I can’t imagine the lookup was designed this way and I’m assuming it is my own limited skills preventing me from getting the lookup to work like I imagined.

I actually found one other data set (WorkflowUserListLookup), aimed at driving a lookup in standard, built after same model, but it doesn’t seem to be used from the EP application...

Do you have any experience with Data Set lookup based on joined tables and do you know if this lookup could be achieved with a Data Set lookup? Please comment.

B.t.w. I solved the lookup by developing a new user control with a temporary table to drive the lookup.


Lotte said...

I had excactly same problem. My lookup had to work for a textbox and not a axdatasource databound field. So I made a new usercontrol just with the emplTable and just the **name as a displayfield. I added department and it is possible to sort on this too.
Does anyone know how to descide which fields is shown in the filter fields?

Skaue said...

Seems the AxLookup control only supports one table and one view. I tried make a new DataSet with two tables joined, and it didn't work. I used .Net Reflector to find out why and in Microsoft.Dynamics.Framework.Portal.UI.WebControls.AxLookup.SetLookupDataSet() the code checks to see if the view count is different from 1, in which case it throws an NotSupportedException with the mesage "Only one view is supported". The entire method body looks like this disassembled:

private void SetLookupDataSet(DataSet newLookupDataSet)
if (this.lookupDataSet != null)
this.lookupDataSet = null;
if (newLookupDataSet.DataSetViews.Count != 1)
throw new NotSupportedException(Resources.GetString(AxLookup_Error.OnlyOneViewSupported));
this.lookupDataSet = newLookupDataSet;
this.RelatedTableId = this.LookupDataSetViewMetadata.RootDataSource.Table.Id;


Actually my requirement is that if user selects any value from Look up 1 ,automatically it should filter the second look up 2 .
And both the look ups are from two different tables .So how can i achieve this ??

Gennadii Cheromukhin said...

Just use AX View instead of tables when constructing lookup query. AX View can contain as many tables as needed.

Gennadii Cheromukhin said...

Put any tables into AX View and use single AX view instead of multiple tables.