cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simasima
New Member

Filtering for the latest version

I have a table named docList that holds the DocNumber, DocNumber, and DocTitle.

IDDocNumberDocVersionDocTitle
1ABC-0012300Quick Brown Fox
2ABC-0045600Lorem Ipsum
3ABC-0045601Lorem Ipsum
4ABC-0078900Foobar
5ABC-0045602Lorem Ipsum

 

How I can apply Filter / Select to have a new table of the documents with the latest version? Namely, I'd like to get:

IDDocNumberDocVersionDocTitle
1ABC-0012300Quick Brown Fox
4ABC-0078900Foobar
5ABC-0045602Lorem Ipsum

 

I tried to use Distinct(), but it returns only DocNumber column.

 

Thanks in advance,

simasima

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @simasima ,

 

Yes, the Distinct Function only return the result of the distinct column.

Please try this workaround:

ClearCollect(GroupedCol,GroupBy(DocList,"DocNumber","Grouped")); Clear(NewCol);
ForAll(GroupedCol,Collect(NewCol,{DocNumber:DocNumber,ID:Last(Grouped).ID,DocVersion:Last(Grouped).DocVersion,DocTitle:Last(Grouped).DocTitle}))

 Hope this helps.

Sik

View solution in original post

5 REPLIES 5
v-siky-msft
Community Support
Community Support

Hi @simasima ,

 

Yes, the Distinct Function only return the result of the distinct column.

Please try this workaround:

ClearCollect(GroupedCol,GroupBy(DocList,"DocNumber","Grouped")); Clear(NewCol);
ForAll(GroupedCol,Collect(NewCol,{DocNumber:DocNumber,ID:Last(Grouped).ID,DocVersion:Last(Grouped).DocVersion,DocTitle:Last(Grouped).DocTitle}))

 Hope this helps.

Sik

gabibalaban
Dual Super User
Dual Super User

As I can see to a document number is corresponding a document title.

I guess the simpliest method is this.

Try to use:

ClearCollect(YourNewCollection,

     AddColumns(

                GroupBy(

                         YourCollection,"DocNumber","DocTitle","Result"

                 ),

                "DocVersion",Max(Result,Version),

                "ID",Result.ID

     )

)

 

Hope it helps !

@gabibalaban,

Apology for my bad example. DocTitle is not always same for the same DocNumber.

I needed SortByColumns() and First() not to have [object Object] in the output. The code below works for me.

ClearCollect(
  newCollection,
  AddColumns(
    GroupBy(
      SortByColumns(docLedger, "DocVer", Descending, "DocNumber", Ascending),
      "DocNumber", "Result"
    ),
    "DocVersion", First(Result.DocVer).DocVer, 
    "ID", First(Result.ID).ID,
    "Title", First(Result.Title).Title
  )
)

 

Thanks for the hint! 

@v-siky-msft,

Thank you. Your code works just I expected!

Hi @simasima ,

 

My pleasure.

Please mark my post as the answer to help other members find it readily.

Sik

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,258)