Remove duplicates in Data Pages
Hi,
I have a data table and a corressponding Data page (list) for it. The data table has 6 records out of which one column say "Dept" has same value in 5 records and 1 different value in 6th record. In a section I am trying to display the column "Dept" as a drop down.
In section the source is given as Data pages and the corressponding column name. Is there any way in which I can show only unique records for that particular column ?
Kindly help me solve this issue.
Thanks,
Bargavi
***Moderator Edit: Vidyaranjan | Converted thread from discussion to question***
***Updated by moderator: Marissa to close post***
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Ness Technologies
US
There can be multiple approach -
Assuming you are using Pega 7
1. One option is obviously to use RD with remove duplicate rows. However here you need to create seperate rules and as # of rules increases, system becomes more complex to mainatin
2. Use a Parametric data page. The data page will call parametric SQL-Connect rule to execute a query. In the UI, for a field where you need a dropdown of distinct values - refer the datapage and pass the Field name as parameter (Say "City" for populating city dropdoen).The data page will pass this parameter value to the SQL-Connect rule. Write the sql-connect in such a way, that it can accept the parameter and build appropriate query (select distinct {param.valuetofetch}....).. This way everytime the data page (say you named it as D_MyListOfValues) is called for differnet fileds, it will strating building up the page like below.
D_MyListOfValues[City]
pxResults(1).Value=NewYork
pxResults(2).Value=London
D_MyListOfValues[State]
There can be multiple approach -
Assuming you are using Pega 7
1. One option is obviously to use RD with remove duplicate rows. However here you need to create seperate rules and as # of rules increases, system becomes more complex to mainatin
2. Use a Parametric data page. The data page will call parametric SQL-Connect rule to execute a query. In the UI, for a field where you need a dropdown of distinct values - refer the datapage and pass the Field name as parameter (Say "City" for populating city dropdoen).The data page will pass this parameter value to the SQL-Connect rule. Write the sql-connect in such a way, that it can accept the parameter and build appropriate query (select distinct {param.valuetofetch}....).. This way everytime the data page (say you named it as D_MyListOfValues) is called for differnet fileds, it will strating building up the page like below.
D_MyListOfValues[City]
pxResults(1).Value=NewYork
pxResults(2).Value=London
D_MyListOfValues[State]
pxResults(1).Value=NewYork
pxResults(2).Value=NewJersey
You might decide whether to make the Data page as node level or requestor/thread level depending how frequently you data is going to change. The benefit is that you use leaas number of rules, you can take benefit of data page caching for better performance, and the solution is expandable for other dropdowns - code once use for any dropdown.
Note - The above explanation is high level abstraction of actual solution. Consult Pega help for actual code syntax.
Pegasystems Inc.
US
Source the data page from a report definition. The report should retrieve one column: dept. Make sure the "distinct checkbox" is selected in the report definition.
Wipro Technologies
IN
Thank you for your response.
Say I have more than one column eg dept, name, id, designation. And my requirement is to display all of those columns in a section, where each of the coulmns can have duplicate values. In that case for each and every column should I create a Report definition with remove duplicate rows checked ?
Please suggest if I could approach this problem is a different way ?
Accepted Solution
Ness Technologies
US
There can be multiple approach -
Assuming you are using Pega 7
1. One option is obviously to use RD with remove duplicate rows. However here you need to create seperate rules and as # of rules increases, system becomes more complex to mainatin
2. Use a Parametric data page. The data page will call parametric SQL-Connect rule to execute a query. In the UI, for a field where you need a dropdown of distinct values - refer the datapage and pass the Field name as parameter (Say "City" for populating city dropdoen).The data page will pass this parameter value to the SQL-Connect rule. Write the sql-connect in such a way, that it can accept the parameter and build appropriate query (select distinct {param.valuetofetch}....).. This way everytime the data page (say you named it as D_MyListOfValues) is called for differnet fileds, it will strating building up the page like below.
D_MyListOfValues[City]
pxResults(1).Value=NewYork
pxResults(2).Value=London
D_MyListOfValues[State]
There can be multiple approach -
Assuming you are using Pega 7
1. One option is obviously to use RD with remove duplicate rows. However here you need to create seperate rules and as # of rules increases, system becomes more complex to mainatin
2. Use a Parametric data page. The data page will call parametric SQL-Connect rule to execute a query. In the UI, for a field where you need a dropdown of distinct values - refer the datapage and pass the Field name as parameter (Say "City" for populating city dropdoen).The data page will pass this parameter value to the SQL-Connect rule. Write the sql-connect in such a way, that it can accept the parameter and build appropriate query (select distinct {param.valuetofetch}....).. This way everytime the data page (say you named it as D_MyListOfValues) is called for differnet fileds, it will strating building up the page like below.
D_MyListOfValues[City]
pxResults(1).Value=NewYork
pxResults(2).Value=London
D_MyListOfValues[State]
pxResults(1).Value=NewYork
pxResults(2).Value=NewJersey
You might decide whether to make the Data page as node level or requestor/thread level depending how frequently you data is going to change. The benefit is that you use leaas number of rules, you can take benefit of data page caching for better performance, and the solution is expandable for other dropdowns - code once use for any dropdown.
Note - The above explanation is high level abstraction of actual solution. Consult Pega help for actual code syntax.
Aaseya IT Services Pvt Ltd
GB
Hi Bargavi,
Did you try @(Pega-RULES:Utilities).pyRemoveDuplicatesFromPagelist(ListPage, "PagelistName", "propertytocheckForDuplicate")