Certain InventTable field values not showing in form extension

(0) Share

Report

Posted on by Cody M

Hi All,

I'm working on a form extension for the InventOnHandItem form. I am trying to add two string fields to the grid from InventTable. The issue is that when I set the data field to certain fields, I am not getting any values in the new columns. I can see in SQL and Table Bowser that there are values in those columns, they just are not showing in the grid. The column and column header are there, but all the values are blank.

I tried this first with two custom fields in InventTable, and when those didn't work I tried with other fields on the table. Some show values, others don't. In SQL and table browser they all have values. Some example fields:

DataAreaId - A value is shown.
NameAlias - A value is shown.
ItemBuyerGroupID - No value is shown.
BOMCalcGroupId - No value is shown.
ModifiedBy - No value is shown.

Again, these fields should all show values based on what I see in SQL, so I must be missing something.

The Data Source properties:

The properties from one of the fields added to the grid, using 'ModifiedBy' as an example data field:

I'm not sure what I am missing in the setup. I followed the same steps I have followed when joining other tables and adding fields to grids, except that the data source screenshot above shows as an inner join type. I tried both an outer join and inner join. I received no values for any fields using outer, even though outer is what I have used in other instances and got the results I expected.

Any help would be greatly appreciated. Thank you!

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Martin Dráb 237,803 Most Valuable Professional on at

Like (0)

Report

Your description and screenshots ignore the key thing - the query that the form uses to fetch the data from database.

If you look at it, you'll likely see that the form uses groupping, but you your new fields are neither groupped by nor aggregated, therefore no value is can be obtained for them. You'll likely want to add your fields to the 'group by' clause.

Was this reply helpful? Yes No
Cody M 55 on at

Like (0)

Report
[quote
Your description and screenshots ignore the key thing - the query that the form uses to fetch the data from database.

If you look at it, you'll likely see that the form uses groupping, but you your new fields are neither groupped by nor aggregated, therefore no value is can be obtained for them. You'll likely want to add your fields to the 'group by' clause.
[/quote

Hi Martin,

Thank you for the response. Based on this I looked into adding the fields I wanted by adding them to the 'group by' clause. I extended 'InventDimCtrl_Frm_OnhandItem' and added the two fields I wanted from the InventTable to the 'group by'. In this case, we have two custom fields on InventTable for 'Manufacturer' and 'ManufacturerItemId'.

InventTable is already a data source on the form using an inner join. Using that as the source for the fields works, and we can see the correct manufacturer values shown. However, Since some records will be blank for these custom fields, we needed to outer join to InventTable, so I added it as a data source with outer join:

Using the setup in the screenshots, when loading the form the 'Manufacturer' values are shown, but any changes to the grid filters or ordering by certain columns, any of those actions causes the entire column to show blank value. Hitting the 'Apply' button in the Filters section brings back the values, and 'Reset' also sets them to blank.

I have looked around for other posts about this and in all the other cases the suggestion is to add the fields to the group by in 'InventDimCtrl_Frm_OnhandItem'. That's what I've done below:

public void modifyQuery( FormDataSource _inventSum_DS, FormDataSource _inventDim_DS) { next modifyQuery(_inventSum_DS, _inventDim_DS); Query inventSumQuery = _inventSum_DS.query(); //QueryBuildDataSource inventTable_qbds = inventSumQuery.dataSourceTable(tableNum(InventTable)); QueryBuildDataSource inventTable_qbds = inventSumQuery.dataSourceName("InventTable"); if (inventTable_qbds) { inventTable_qbds.addGroupByField(fieldNum(InventTable, CS_Manufacturer)); inventTable_qbds.addGroupByField(fieldNum(InventTable, CS_ManufacturerItemId)); } }

I was assuming that the above part was right, since it works with the standard InventTable data source already on the form, and it works on load of the form. So either the form is behaving strangely, or I'm missing something. Way more likely that I'm missing something obvious or misunderstanding.

Thank you again for the response. I appreciate your help on this.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,803 Most Valuable Professional on at

Like (1)

Report

I didn't get this part: "Since some records will be blank for these custom fields, we needed to outer join to InventTable". Can you elaborate it, please?

What is the problem with having blank values in those fields?

You could use an outer join if some InventSum records wouldn't have to have a relation InventTable record, which doesn't make sense to me.

Was this reply helpful? Yes No
Cody M 55 on at

Like (1)

Report

I was misunderstanding the joins. After reading your response, I went through it again. Using the InventTable data source that is already on the form, which uses inner join, does get the desired results. I just needed to add the additional fields from InventTable to the 'group by' clause like you mentioned.

Thank you for your help!

Was this reply helpful? Yes No
Somasekhar 5 on at

Like (0)

Report

Hi Cody,

You were saying the two custom fields were showing values in the grid, right. But it didn't work for me. I have some custom fields added to InventTable based on which we do Filter of records. I have followed above logic added to Ext of modifyQuery method.

Can you please confirm if you have seen it working - custom fields showing values in the gird?

Thanks in advance,

Soma

Was this reply helpful? Yes No
Cody M 55 on at

Like (0)

Report
Hi Soma,

I was able to get it to work following Martin's suggestions. I created an extension to ''InventDimCtrl_Frm_OnhandItem'' and added the custom fields to the group by clause there.

final class InventDimCtrl_Frm_OnhandItem_CS_Extension { public void modifyQueryBasedOnDatasourceName( Query _inventSum_DS_Query, str _inventSum_DS_Name, FormDataSource _inventDim_DS) { next modifyQueryBasedOnDatasourceName(_inventSum_DS_Query, _inventSum_DS_Name, _inventDim_DS); Query inventSumQuery = _inventSum_DS_Query; if (groupByNameAlias) { QueryBuildDataSource inventTable_qbds = inventSumQuery.dataSourceTable(tableNum(InventTable)); if (inventTable_qbds) { inventTable_qbds.addGroupByField(fieldNum(InventTable, CS_Manufacturer)); inventTable_qbds.addGroupByField(fieldNum(InventTable, CS_ManufacturerItemId)); } } } }

Was this reply helpful? Yes No