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:
Have 2 filters: FromDateTime and ToDateTime that runs over SalesTable.createdDateTime
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.
[mention:64fb33d4ab384f5ebca3050a0d9ca94f:e9ed411860ed4f2ba0265705b8793d05] 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.
Hi Martin, [mention:64fb33d4ab384f5ebca3050a0d9ca94f:e9ed411860ed4f2ba0265705b8793d05]
I was able to achieve desirect non-duplicates in a view.
Now I would like to add a calculated column to this view which Sums all the Line amounts for the Item ID = ‘Service’.
I tried the same for line amount(Service + non-service), the calculated column is fetching right values. Query for this is
private static server str calcLineAmount2()
expression = 'select sum(SalesQty) from salesLine where salesLine.SalesId = '+
Now i try adding a filter on ItemId, it gives an error: (where am i going wrong here)
private static server str calcLineAmount2()
expression = 'select sum(SalesQty) from salesLine where salesLine.ItemId = ‘+“Service”+’ AND salesLine.SalesId = '+
Now I have all the data with required fields in View and those I am able to see in the Form.
Fields i can view in form:
Now i have created 2 new filter fields Date from Time and Date To Time. How can i utilize these fields when entered by user to filter records in my form. I mean where i should write the code for this filtering
Using a computed column would be possible, but why? The design I suggested doesn’t require all such work. Aggregated functions are already supported in AX queries.
If you want to develop a computed column anyway, you’ll have to fix your code, because you don’t generate it correctly (as you can see from the error). One bug is that you’re trying to compare salesLine.SalesId with SalesId without specifying the table.
Another problem may be in that you use AX names instead of SQL names. For example, the table my be referred by alias T5 instead of salesLine. Also, some AX names aren’t supported in T-SQL and they have a special prefix, or the names in database are shortened.
When developing computed columns, a good idea is designing and testing the T-SQL code directly in the view in database and only then writing X++ code generating this T-SQL code.
Computed columns are powerful, but writing them is quite complicated and error-prone. Don’t do it if it isn’t necessary.
In executeQuery(). Just search for examples, it has been discussed here many times and AX is also full of examples.