Answered

Filtering for the latest version

(0) Share

Report

Posted on by simasima

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

ID	DocNumber	DocVersion	DocTitle
1	ABC-00123	00	Quick Brown Fox
2	ABC-00456	00	Lorem Ipsum
3	ABC-00456	01	Lorem Ipsum
4	ABC-00789	00	Foobar
5	ABC-00456	02	Lorem 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:

ID	DocNumber	DocVersion	DocTitle
1	ABC-00123	00	Quick Brown Fox
4	ABC-00789	00	Foobar
5	ABC-00456	02	Lorem Ipsum

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

Thanks in advance,

simasima

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

v-siky-msft Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report
Copy link

Link copied!

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 !

Was this reply helpful? Yes No
simasima 7 on at

Like (0)

Report
Copy link

Link copied!
@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!

Was this reply helpful? Yes No
simasima 7 on at

Like (0)

Report
Copy link

Link copied!

@v-siky-msft,
Thank you. Your code works just I expected!

Was this reply helpful? Yes No
v-siky-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @simasima ,

My pleasure.

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

Sik

Was this reply helpful? Yes No