Group By Entity And Show the List In Drop Down

[Sanjit_Pal]

Hello Everyone,

I have more than 300 rows in an entity having a year column, that column having the same value like 2020 will be repeated n number of times.

I am required to do the Grouping of the same value and show them one time only instead of showing the same values multiple times. How that can be done?

Attaching the screenshot for reference.

[ppr (Peter Preuss)]

had you tried to append a DistinctBy and formulate the Property checked for Deduplications

[Sanjit_Pal]

Not sure how to do it, if you have any idea could you please guide

[ppr (Peter Preuss)]

Fetch(................)(..........).data.DistinctBy(Function (x) x.YourPropertyName).ToListSource

(…)(…) your code

[Sanjit_Pal]

It’s not working, for test purpose, I have created a source and for that, I can get the distinct values.

Not Getting Distinct:- Fetch(of PraticeEntity)(Nothing, Nothing, Nothing, Nothing, New ExpansionFieldOption(){addExpansionFieldOption("CreatedBy", New String(){"Id","Name"}), addExpansionFieldOption("UpdatedBy", New String(){"Id","Name"})}).data.GroupBy(Function(x) x.Year).Select(Function(group) group.First()).ToListSource()

Putting the column name as Year in the Column field as the values are coming from an entity

Column field is empty since a customized source
Getting Distinct:- New ListSource(Of String) ({"Name","Class","Name"}).data.Distinct.ToListSource

[ppr (Peter Preuss)]

let us not mismatch GroupBy and Distinct
For Distinct we also suggested the DistinctBy as there would no Custom Comparer in Place for the used Entity Type

It looks to us that your case can be reformulated to:

• display all distinct Years in the Dropdown

Is our understanding correct?

[ppr (Peter Preuss)]

We used experience from:

VB.Apps: Function: Fetch - Distinct values

@PeCour We can do / had done within the session Fetching DataService Entities to a variable - tmpList Using tmpList along with a LINQ, selecting the needed Property/Field and Distinct it With your input and using the ToListSource at the end we transformed it to the expected Result Type for the dropdown

And did this Quick Prototype:

DS Samples:

Variables:

DistinctYearList - Array of Text
DSFiltered / Prefetch1 = ListSource(Of OURDSEntity)

We configured onPageLoad

Prefetch1 - the normal DS Fetch
DistinctYearList:
Prefetch1.data.Select(Function (x) x.Col2).Distinct().OrderBy(Function(s) CInt(s)).ToArray
Demonstrating Control we reordered

DropDown Event:

DSFiltered =

Prefetch1.data.Where(Function (x) x.Col2.Equals(MainPage.Dropdown.SelectedItem)).ToListSource

MainPage.Textarea.Value =

String.Join(vbCrLf, DSFiltered.data.Select(Function (x) x.Col1 + " - " +  x.Col2))

we display the filterresult in a shorten format

And did run the App:
Start:

Selection1:

Another Selection:

[srinivasmarneni (Srinivas M)]

To group by the year column and show each year only once instead of multiple times, you can use the following method :

1. Read Range*: Use the ‘Read Range’ activity to read the data into a DataTable.
2. Use LINQ to Group By*: Use a LINQ query to group the data by the year column and select distinct years

uniqueYears = (From row In yourDataTable.AsEnumerable()
Select row.Field(Of String)(“YearColumnName”)).Distinct().ToList()

1. Write to Excel*: Use a ‘Write Range’ activity or a loop with ‘Write Cell’ to write the distinct years to a new sheet or range in Excel.

This process will give you a list or DataTable with unique year values, which you can then display or process further as required.

[ppr (Peter Preuss)]

@srinivasmarneni

@srinivasmarneni

Write to Excel*: Use a ‘Write Range’ activity or a loop with ‘Write Cell’ to write the distinct years to a new sheet or range in Excel.

May we ask you to elaborate more on the above-given hint and the relation to UiPath Apps. Thanks for supporting us

[Sanjit_Pal]

@ppr Thanks brother, let me try this once.