Add a view to a query object in X++

(0) Share

Report

Posted on by brianmaric

Hi, I'm trying to filter the Customer Statement based on the Financial Dimension. One simple way is to create a view like tihs:

https://www.axazure.com/en/show-dimensions-in-a-form-without-adding-table-fields

Well, I have this view and I want to use it to filter the records returned on the customer statement.

I'm trying to modify the query in the report classes.

One simple way would be if I could add the view to the query and then filter on it, but this does seem possible as the query.addDataSource() method wants a tableID.

Can anyone help?

Thanks

I have the same question (0)

All responses (19)

Answers (2)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Brian,

You can add the view as a data source using tableNum(<viewName>).

Was this reply helpful? Yes No
brianmaric on at

Like (0)

Report
Here's my code:

query = next createBaseCustTransQuery(_party,_statementStartDate,_statementEndDate,_includeReversed); qbdsCustTrans = query.dataSourceTable(tableNum(CustTrans)); qbdsDimensionAttributeValueSet = qbdsCustTrans.addDataSource(tableNum(DimensionSetView)); qbdsDimensionAttributeValueSet.addlink(fieldNum(custtrans, DefaultDimension), fieldNum(DimensionSetView, DIMENSIONATTRIBUTEVALUESETRECID));

The second line wipes out the SQL in the query.

What am I missing?

Also, I can't seem to find the preRunModifyContract. The only place to do it is in the controller class.

Thanks

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

This seems to be a good place to place your code. You could also try doing a CoC on createCustTransQueryForStatementCust method as all the ranges are being set here.

Could you please explain your statement - "The second line wipes out the SQL in the query."?

Was this reply helpful? Yes No
brianmaric on at

Like (0)

Report
When this line runs:

query = next createBaseCustTransQuery(_party,_statementStartDate,_statementEndDate,_includeReversed);

and I check the SQL statement on the query object, it shows the select statement of the CustTable and the CustTrans with the range values for both tables.

But when this line runs:

qbdsDimensionAttributeValueSet = qbdsCustTrans.addDataSource(tableNum(DimensionSetView));

and I then check the SQL of the query object, there is no CustTable and the CustTrans SQL range values in the query object disappear and is replaced by just the CustTrans Table and the view but without any range values. It is as though my code overwrote the SQL of the returned query object and just replaced it with a new query.

Was this reply helpful? Yes No
brianmaric on at

Like (0)

Report

Here's the first SQL value:

Here's the Second:

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

I tried similar code using DefaultDimensionView and looks like I am getting correct value for variable q.

Was this reply helpful? Yes No
brianmaric on at

Like (0)

Report

not for me...

not sure why

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Could you please try a full build of your development model, just to make sure there are no build issues?

Was this reply helpful? Yes No
brianmaric on at

Like (0)

Report

I did a full build with 50 warnings but no errors. I still got the same result:

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

That's odd.

Could you try the same code in CoC of createCustTransQueryForStatementCust method and see if you see similar behavior?

Was this reply helpful? Yes No