2 datasources, 1 grid, outerjoin

Hi Follwowing task: make a form that displays Inventtable and the name of the Itemgroup, name of itemgroup must be filterable. What did I do: Create a form with datasources InventTable and InventItemGroup, set InventItemGroup to be Outerjoined on Inventtable. Create a grid on the form and put the fields from the 2 tables there. Starting the form: everything seems to be okay, the grid displays the name of the item group correctly. But when I filter on the name of the itemgroup, the following happens: All records from Inventtable are still shown but the field for itemgroup name is only shown for records that match the filter criteria. What do I want to see? Of course only inventtable records that have the itemgroup name as in the filter. It seems to me that Axapta puts the (B.ITEMGROUPID = ‘something’) not in the reight place. It puts it into the “left join …” criteria and not outside. Anybody an idea???

Hi, Why don’t you simply use instead the InnerJoin. This will allow the correct filtering. Then, to also see the items with no existiong item group, you can add another grid having the InventTable and InventItemGroup as DS, but with NotExistJoin as source type. Hope this helps, Ciprian

Hi, using InnerJoin helps for the Filter, but the problem I get is when I create a new entry. Since I use the second table only to display/filter information, I set the AllowEdit on it to NO. Now, when I try to create a new entry on my “main” table, Axapta doesn’t like it because the other table does not allow edit and new. The error comes when I try to save the new record. If I allow Edit and New on the second table and keep the InnerJoin, what happens on the second table - will Axapta try to insert a new entry in the second table at any point? Thanks.

I have exactly the same problem. I only want to join a second datasource for the purpose of filtering… If a new record is created, it seems that also a new record will be created for the joined source… Any ideas?

You have to use innerjoin instead of outer join.

Thanks,

Vikas Mehta

No, that was not the solution. There are the same problems when using an InnerJoin. You have to overwrite the following methods of the “filter”-Datasource:

don’t call super();

.) write → empty

.) validateWrite → simply returns true

This works for me.

You can use inner join. Also comment ths super() call in the inner datasource’s (ItemGroup) write method. This will prevent saving of the record of joined datasource.

Same thing is done in SalesLine datasource of SalesTable form.

How we can done validity in that code?