adding new field to data source with custom query

(0) Share

Report

Posted on by lfulk

Hi, I'm new to d365 customizations and trying to add fields to an existing report (WHSPickListShipping) in d365 finance and operations (which forum should I use?!). An integration partner walked me through how to

create the new version of the report
add code to use the new report in print management
create the new query
change the report to use the new query.

However, we got stuck b/c none of the data for the new fields (SalesId, DeliveryPostalAddress) we added to our query seem to be getting populated with anything.

The new query is duplicated from WHSPickListShippingReport and we are trying to add a connection to SalesTable. I see that WHSWorkLine and WHSWorkTable both have relations to SalesTable.

I've tried setting "Use Relations" to "Yes" but the relation doesn't seem to get added by default. Should it?

I've tried to manually create the relation a couple different ways to both WHSWorkLIne and WHSWorkTable, I'd appreciate any help that someone can provide on what I might be missing. TIA!

properties for SalesTable:

1. manually specifying the fields

properties for Relation

2. using the existing relation

existing relation on WHSWorkLine

relation on WHSWorkTable

I have the same question (0)

All responses (13)

Answers (0)

Blue Wang on at

Like (0)

Report

Hi Ifulk,

Your screenshots are too vague and small, can you upload them again.

From your first picture, it seems that you added the salestable data source at the same level as WHSWorkTable, try to move it under WHSWorkTable.

Note:Moved to Finance forum.

Was this reply helpful? Yes No
lfulk 97 on at

Like (0)

Report

I've re-uploaded the screenshots. Please let me know if there are additional types of screenshots that would be more helpful.

I tried adding it at both levels to WHSWorkTable and to WHSWorkLine without any luck. I've even tried adding WHSWorkTable to WHSWorkLine again with the exact same settings as the original as far as I could with the exception of "Use relations" as No b/c the relation didn't automatically populate and it wouldn't print that new field.

original WHSWorkTable in existing query WHSPickListShippingReport

version i added

expression - field WorkTransTypeLabel is from original. WHSWorkTable WorkTransTypeLabel1 is from one I added When expression has WorkTransTypeLabel, it prints 3 lines of text. when I change it to WorkTransTypeLabel1, it prints nothing at all.

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

Like (0)

Report

Hi,

What's the join type on SalesTable? Also, did you check the data and see if you are getting any results running the query in SSMS?

Was this reply helpful? Yes No
lfulk 97 on at

Like (0)

Report

Hi Gunjan Bhattachayya, I have tried both inner and outer join but I think it should be inner join. I'm very new to d365f&o development so I'm not sure how to get the underlying sql query. I'd be very grateful if you could point me towards an article that could tell me how to do this as well as getting what the incoming parameters would be for the form I'm testing with in d365. Thanks!

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

Like (0)

Report

Hi lfulk,

I thought you were trying to show SalesId values in a report. Could you please explain your exact requirement here so that we can help you better?

Was this reply helpful? Yes No
lfulk 97 on at

Like (0)

Report

Hi Gunjan Bhattachayya, we're essentially trying to make the pick list look more like a packing slip so we are trying to add the order's bill to address, ship to address, order date and ship method to it. I was trying to use SalesId from SalesTable to try to simplify it a little bit to make sure the datasource was working correctly before getting the exact fields I need.

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

Like (0)

Report

Hi,

Using the SalesOrder table as you mentioned and joining it to WHSWorkTable, are you still unable to get any values for sales orders?

Was this reply helpful? Yes No
lfulk 97 on at

Like (0)

Report

Hi Gunjan Bhattachayya, I have not had any success getting any sales data to populate from SalesTable but I also haven't been able to successfully link any table. I tried to link WHSWorkTable manually to WHSWorkLine either even though it's already linked by default when I copy the existing query but I'm still unable to get data from it so I think there's some setting I'm missing in general. It seems odd that the relation does not automatically populate when I set "Use relations" to Yes which is how the data sources are configured from the original query I copied.

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Ifulk,

By the way, have you check CBD feature based on ER reporting? Shipping pick list is supported and probably you don't need to develop anything in x++ as relations are supported in ER out of the box. And it's much easier to support CBD version than copy of SSRS report.

docs.microsoft.com/.../configurable-business-documents-reporting-word-excel

Was this reply helpful? Yes No
lfulk 97 on at

Like (0)

Report

Hi Sergei Minozhenko, I had not heard of the CBD feature but will definitely check it out. Thanks!!

Was this reply helpful? Yes No