Date range in query in D365FSC

Hello Developers,

I have an AOT query that gives me the quantity of items on sales lines by item Id and inventory dimension. It is a simple query that just returns the ItemId, InventDimId and SalesQty for open sales The query is incorporated into a view in order to populate a grid in D365FSC. The query looks like the screen shot below:

image

In the view, I have the SalesQty field set with an Aggregation of Sum. This gives me each item/inventory dimension combination with the quantity of the item being sold.
My problem is I need to be able to filter the query by date. If I add the ShippingDateRequested field to the view, my sum then becomes item id/inventory dim/ShippingDateRequested, which I don’t want. If I don’t have the ShippingDateRequested field, I don’t have a way of setting a date range.
I added a ShippingDateRequested range to the AOT query and left the ShippingDateRequested off the view, but when I try to set the value of the query range, I get an error that the field doesn’t exist in the view.
Is there a way I can set a date range for these sales orders lines and not have them show up on the view?

Thanks
JT

1 Like

Your view contains aggregated data. If you want to filter the view by ShippingDateRequested, the view needs to provide an aggregated value of ShippingDateRequested, e.g. the highest ShippingDateRequested for each group.
But if your intention is changing at runtime what data should be aggregated by the view, then your design is wrong. The view is defined at design time and you can’t change it at runtime. Instead of using the query to populate the view and using the view in the form, you should use the query itself as the source of data. Then you can decide at runtime which records will be included before aggregation.

1 Like

Thank you Goshoom. By using the Query I was able to set the range on the ShippingDateRequested field.

1 Like