Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Give the user a view w...
Microsoft Dynamics AX (Archived)

Give the user a view with an editable query underneath.

(0) ShareShare
ReportReport
Posted on by vv1984 1,085

Hi everyone,

I often receive the request to give the user an organised set of data to be exported to an Excel spreadsheet.

BY NOW:

The quickest way to do this is making a query (with ranges, grouping and whatever..) and use it as datasource in a view. Then I export the data from the view to Excel and i give the spreadsheet to the user. But all this word is always on my desk and I have to work on the AOT each time... 

MY WISH:

I would like to find a way to give the user the facility to open this interrogation straight from a menu, change the query by his needs (even adding tables to the query that populates the result if possible) and then launch it to get a form grid filled with data ready to be exported to Excel.

POSSIBLE SOLUTION:

I would use a RunBaseBatch class with a query object:

-User sets up the query and clicks OK on the class prompt dialog

-The run method of the class populates a table "MyTable" with data (one field of the table identifies the user who has launched the class, so concurrent runs are managed)

-The class eventually opens a form which has "MyTable" table as datasource.

Now my questions are:

Is there some standard class or form which does this job in a similar way? 

The SQL group by clause is managed correctly or there is some exception I should be aware of before starting?

Thank you in advance for the patience to read all this.

I hope you can give me some hints about this.

Regards

*This post is locked for comments

  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    RE: Give the user a view with an editable query underneath.

    Yes. Drag your AOT query to the datasources node of a form.

    To select fields, drop datasource fields or field groups on your grid. Please read some introduction to form development before continuing; you won't learn everything just by asking question in a forum.

  • vv1984 Profile Picture
    vv1984 1,085 on at
    RE: Give the user a view with an editable query underneath.

    Can I create a query in AOT and then use it as datasource on my form? How do I then select the fields which must be displayed on the grid?

    Do i have to compose my query straight on the form's datasource node?

    thank you

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    RE: Give the user a view with an editable query underneath.

    Feel free to add pictures if it helps.

    Yes, AOT queries support grouping and aggregations. You can find answers for such questions in AX documentation (e.g. How to: Create Queries by Using the AOT).

  • vv1984 Profile Picture
    vv1984 1,085 on at
    RE: Give the user a view with an editable query underneath.

    Great, i'm sorry for the long thread but sometimes it's quite difficult to explain thing properly without pictures...

    Last question about this: let's say i would like to give some aggregate result, like the some of the invoiced amounts of groups of customers (e.g. grouped by CustTable.CustGroup field).

    The group by clause works fine in these AOT queries or are there some missing features I should know?

    Thank you very much indeed for your help!

  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    RE: Give the user a view with an editable query underneath.

    This approach was already discussed too. You can create forms and let users use Advanced filter/sort (Ctrl+F3).

  • vv1984 Profile Picture
    vv1984 1,085 on at
    RE: Give the user a view with an editable query underneath.

    No no, didnt mean that. That would probably made me lose my job ;-))

    I would develop a single form/class for each need. But i would like them to be able to edit the query a bit (changing ranges mostly) before launching the data extraction.

    I'm just looking for the best way to achieve this.

  • Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    RE: Give the user a view with an editable query underneath.

    Yes, that's what I addressed in my second reply.

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Give the user a view with an editable query underneath.

    are you looking for something where users can add tables by own need and by joining them as per need, develop a dynamic/run-time kind of inquiry or report or export data, based on developed query at runtime???

  • vv1984 Profile Picture
    vv1984 1,085 on at
    RE: Give the user a view with an editable query underneath.

    I'll try to explain it better.

    The queries I need to give my users aren't already available on standard forms. And there is no form whose query can be edited with Ctrl+F3 to achieve that.

    So I'm trying to build a solution for this. I'm just wondering if the standard Ax has some Classes which display a query to the user (as the one you see when pressing Ctrl+F3 on standard forms) and then give the user the result of that query in a grid form.

    Something I can observe to get some inspiration.. ;-)

    Thank you again

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,488 Most Valuable Professional on at
    RE: Give the user a view with an editable query underneath.

    You can modify queries of (almost) all forms. If you have a list of invoices that shows what you want, press Ctrl+F3 and set any filters you like.

    I thought you wanted something that would generate a form from your query, which is a very different problem.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,791 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,488 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans