Hi DAX Community,
I am trying to create a new Custom Sales Order Enquiry form, which must have 2 filters FromDateTime and ToDateTime. This filter must run on the SalesTable.createdDateTime field and retrieve the records from SalesTable.
May I know how can i achieve this?
What steps I took:'
I created the form, create the 2 filters, Created A new table TestTable with 3 fields: SalesId, Sales Name and Created Date Time and this TestTable is being used as a DataSource in the New Sales Order enquiry form.
I can see that the data is being inserted in TestTable but not refecting in the form's grid.
To try fixing this, i tried TestTable_ds.research(true); in the form after the insertion took place - But this didn't fetch me any expected outputs.
The description of your requirement sounds like you want to show filtered data from SalesTable. Then I would expect you'll use SalesTable as the datasource and simply filter CreatedDateTime based on values of your two filter fields. But you talk about a completely different implementation based on another table and I'm not sure whether you have extra requirements that you forgot to mention or you've just used wrong design because you didn't know how to do it. Please clarify your requirements.
That was my mistake of giving incomplete info. What you mentioned about a new requirement is absolutely true.
The New form must have the following:
1) Have 2 filters: FromDateTime and ToDateTime that runs over SalesTable.createdDateTime
2) The Grid must display SalesID, SalesName, createdDateTime and SumOfALLSalesLineQuantity.
First 3 fields are coming from SalesTable and 4th field from SalesLine.
Since the user wants this to be in a new form. Could you suggest on how i must go about this requirement.
Note: There might be few more fields added in the near future to the form.
Also, I have added Search button on the form, on its clicked method, i call a custom new method insertValuesinTestTable where i find values based on the From Date Time and ToDate Time and insert them in TestTable. After this insert I write TestTable_ds.research(true) - But the data is not available on the grid but it is inserted in the table. May i know what am i missing here?
I wouldn't use any extra table; I think it's a mistake.
One option is designing a query returning all data at once. Then you want a query joining SalesTable and SalesLine, filtering by CreatedDateTime, grouping by SalesId, SalesName and CreatedDateTime and using Count() aggregation on a field on SalesLine. This may be nicely wrapped in a view.
Or you could simply show filtered data from SalesTable and add a display method counting related sales lines (select count(RecId) from SalesLine where SalesId = ...). If you decide to use this approach, make sure you cache the display method to avoid performance problems.
If i go with the 1st option, should I be creating a view with these 4 fields.?
It's not strictly necessary, but yes, I think it's better to create a view.
The view will expose CreatedDateTime without any filter - filtering will be still done in the form, but the form won't have to deal with the aggregation.
I have created view. But it is showing multiple values for the Salesorder that have more than one sales line
Above output: *000037 sales order has 4 Sales Line whose line amount sum to 391.08. The sum part is coming correct, but why it is showing 4 records instead of 1. Also, in the created view, I have added SalesTable.Sales Id as in the Group By field of view.
Martin Dráb Kindly let me know if I am missing something here
Didn't you forget to group the data?
I placed a group by on salesid field, but still it is fetching the same result.
Am I missing something here?
If you still get the same result, then the grouping obviously doesn't work at all. Review your changes and make sure you've synchronized the database.