Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / View, query or ??? to ...
Microsoft Dynamics AX (Archived)

View, query or ??? to concatenate across table rows

(0) ShareShare
ReportReport
Posted on by DaveCline 85

DAX Tribe,

I have a need to build a view for a form which concatenates across rows.

InventJournalTable has multiple InventJournalTrans.

We want to show the InventDimId and ToInventDimId (From warehouse & To warehouse) for the header row: InventJournalTable.

If there are two InventJournalTrans records, each with different warehouses specified (as dimensions) we want to produce a single record which has the mentioned fields assembled.

JournalID = 1

Trans record #1
InventDimId = "AA"
ToInventDimId = "BB"

Trans record #2
InventDimId = "CC"
ToInventDimId = "DD"

Resulting View record:
1, "AA,BB", "CC,DD"

I realize that I'll have to write code to do this. But I'd really like to have a VIEW at the end of this, containing the records, so I can join it to a Form's datasources for presentation.

Where or how to add the code is basically my question.

Any clues would be appreciated.

Thanks, Dave Cline

*This post is locked for comments

  • DaveCline Profile Picture
    DaveCline 85 on at
    RE: View, query or ??? to concatenate across table rows

    Satish,

    Oh, I agree, we should simply show the unique JournalID + WHfrom + WHto records.

    But this is not what the business desires. They only want single header records, and all the warehouse FROMs and all the warehouse TOs.

    This is rather like a Heads-Up style view, I'm guessing, where they want to quickly assess an inventory transfer master record with WH details stuffed inside.

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,645 on at
    RE: View, query or ??? to concatenate across table rows

    Agree. Why do you want data in a single row? it's difficult to read. Something like below is more readable and make sense. If uniqueness is not an issue, just personalize the form and you have all data in row format right there on journal lines. This can help avoid customization.

    Journal 1:

    WHS1 - WHS2

    WHS1 - WHS3

    and so on....

    Thanks,

    Satish Panwar

  • DaveCline Profile Picture
    DaveCline 85 on at
    RE: View, query or ??? to concatenate across table rows

    Thanks for your thoughts Satish.

    (I don't know what is in or out of everyone's DAX implementation, but you're right, From/To warehouse would have (and IS) what we're looking to work with.)

    That said, I was hoping to understand if there is some means by which a block of code could produce a dataset that could be exposed via a View or Query.  

    I suppose I could add code to the InventJournalTable form and when each record is populated, perform a manual fetch of the N InventJournalTrans records that I'd then concatenate. I've done this for reports (basically) but reports seem like a much different beast than doing this in a form.

    I've seen the slew of methods to override on a View. I was thinking that is there one of these that I could intercept, fetch and build the data and then send the data on to be exposed as "the view"?

    It's like I'm performing a crosstab or pivot query but shoving all the data into a single field using code.

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,645 on at
    RE: View, query or ??? to concatenate across table rows

    Hi Dave,

    InventDimId may not be appropriate field as it doesn't mean anything to user, if the request would have been something like warehouse / location / site, that makes more sense. Now let's go back to what you asked for:

    1. You can create a button on the journal header which when clicked will open a form that can show the data users are looking form.

    2. Your option would be to loop through all the journal lines and get this information.

    OR you can create a view in AOT using group by fields.

    Thanks,

    Satish Panwar

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans