Really hoping someone can help with this - I've spent all day and can't quite get there. The scenario is this:
I'm building an app to manage all of the BI reporting we do. I have a big list of reports, and some reports are dependent on others (e.g. a report for senior managers may take data from several operational reports). I have managed to create a collection (Table 1) which is basically a stripped down list of dependencies a report has with other reports. What I am trying to produce is table 2, which is 1 record per report (instead of 1 row per dependency), but with the dependencies stored as text with a ";" as a separator.
Here is table 1:
What I am trying to achieve is table 2:
I can get the collection to have one row per report and then have the dependency IDs stored in a table within that collection. I can also do this for just 1 report record and get a text output. What I can't figure out is a way of applying some code that looks at table 1 (the length of which will vary) and create table 2. I'm sure it has something to do with ForAll and concat, but just can't figure it out. I'm expecting both table 1 and table 2 to be collections by the way as I'll only need them temporarily in the app whilst the user is editing the dependencies recorded.
Really hoping someone can help - I'd be really grateful if someone can help crack this for me. If you could, it would unlock a whole new set of functionality for my users.
Many thanks in advance
Thank you for looking into this. I did try GroupBy - I think that was the solution that gave me the results as a table within the collection. I then tried to ungroup and concat that but got tied up in knots and couldn't get it to work at all.
If you can figure it out, I'd be really grateful (and impressed!).
I've got this working. There may be some ways to tweak it. There are a couple weird workarounds..
GroupBy( AddColumns(AddColumns(testReportData,"LookupReportID","LOOK"&ReportID),"DependencyIDs", Concat(Filter(testReportData,"LOOK"&ReportID=LookupReportID),Text(DependencyID),";")), "DependencyIDs","ReportID","ReportGroup")
Let me know if you have any questions!
Okay...bare with me if I didn't explain this well. And this is another way to achieve a similar result but it might not work for what you need. It was just something I had already used so I had quick notes on it.
For the first collection use group by so it is just listing the first ReportID once:
Then you can have a gallery that the source is CollName
Then you can have a second gallery that is either off to the side or a subgallery (u might use autoheight for first gallery if using this one)
Second Gallery would be - Filter(DataSource, ReportID=Gallery1.Selected.ReportID)
So when they select an item from the first gallery they see a second open with a list showing all the DependencyID that are related.
If you do the subgallery (which will show the items without making a selection so might be the better option) ….the gallery will need to be inside the first gallery and then the items could be =Filter(DataSource, ReportID=ThisItem.ReportID)
This is going to have a list inside each row of your gallery that shows all the related DependencyID for each ReportID
Either would work. It will list them out on top of each other instead of the ";" as a separator but you could do a vertical gallery to get the same look.
The added benefit is that you could allow them to select either ReportID to see more details or DependencyID to allow for viewing of more information (if you have more you would want to show).
I'll look at a few ideas more along the lines of what you were asking but this was something I have already used a few times I listed above so figured I would mention it.
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications