I have a Dropdown Search pattern widget. Its OptionsList is an aggregate based on multiple entities: "Project" and "Molecule". The aggregate is called "GetPQOTMasterProjectsMolecule". The aggregate is returning the desired rows when tested.
There are 1-to-many projects-to-molecule, and so the aggregate returns multiple rows of Projects with the same name.
Here is my join:
I want my Dropdown Search list to show a project name just once and I'd like to eventually record the value of the selected project ID.
If I understand correctly from other posts, when you group by an attribute, you can only expose that attribute on the corresponding interface element. I've found this configuration challenging.
In my application's current state, the same first record shows repeatedly:
I think that it's because in the mapping to Dropdown Option, I seem to only have the option to select the Current record for Value and Label, and I think that may have something to do why the dropdown just shows "Record A" over and over.:
What is the correct way to get the list in Dropdown Search to show each unique Project Name just once? Also, in the mapping to DropdownOption, do I have to map the Value as the ProjectName? I would much rather set the Value to ProjectID, which is the unique identifier in the Project Entity, but I no longer have that option after setting the GroupBy.
Thank you!
Thank you, everyone, for your replies. I'm just coming back with an update for someone who might need help with something similar in the future.
In the final solution, I did not need to create a 2nd aggregate to have the dropdown list show unique project values in a filtered list properly.
On the advice of a colleague, I grouped the aggregate by *both* the project name and the project ID:
This exposed both columns for mapping, which allowed me to map the Value to the Project ID and the Label to the Project Name.
My 2nd issue - the repeating of the same value over and over again was caused by my choosing the wrong values to map. I thought I should be mapping to the aggregate. Instead, I should have been choosing the column names from the "Scope" section:
Thanks again, Forum!
I am glad that you are able to find a solution, but I just need to mention one thing here.
On an attribute on which you have applied group by and it has the same value with 2 Ids, now if you apply group by On Id as well so it will give you a duplicate value because you are applying group by to your Id which is already Unique for each record, So that doesn't make sense it will return you all the results with duplicate records also.
Thanks
That's a good point. In my case, there was only a 1:1 relationship between ID and Name, and so there were no duplicates.
Alright then, there is no issue !
I think you should use another aggregate to get the projects list, and use it inside the drop-down property, since the screenshot is quite limited, this is what I guess you’re trying to do:
You wanted to use a single aggregate to query the data with 1-to-many relation data’s, and wanted to get the molecule by selecting one project.
Which is not the correct way to do in OutSystems, as far as I know.
You should use two aggregate, one for querying the projects only, and the other used to query the project-molecules with group by function, and then refresh the second aggregate when the drop-down on change, hope this could give you a better idea.
Thank you. I saw other past posts suggesting a 2nd (Projects-only in this case) aggregate to build the dropdown list of Project names. However, the dropdown list I want to present is a filtered list of only some projects. The filter needs the join the projects table with the molecule table to bring back only projects related to molecules that meet certain criteria.
If I use a 2nd aggregate of projects only to build what is displayed in the dropdown list, wouldn't that mean that I am presenting the entire list of projects instead of the filtered list?
You could still using the join table with the filter criteria for the second aggregate, cuz I have done some research and asked some experienced people, which they say, there're 2 options to do this.
1. 2 aggregate (which sounds stupid, cuz both of them are querying the same set of data, but you need one for the groupby aggregate function)
2. like @Tousif Khan said, using an advance SQL to build your own query
but the people I asked had told me that, they're actually almost the same, even using an advance SQL, you still have to query the data twice, which means, the difference between their performance is not noticeable.
So I end up using the method 1 in my own project, cuz I think the optimization is doing great when compile to SQL query, and also it feels more easy to maintain in the future, hope this could give you more ideas about what's you're trying to do.
Cheers
:D
Hello
Just a question here, Do you want both Ids or anyone of them?
If you want to fetch both the Ids Then you need to call an extra aggregate where you have to apply the name filter based on the name you will get the Ids.
If Id doesn't matter and you want the name + any of the Id belonging to that record. you can use Advance SQL and define a Custom Structure With Row Number attr in it, and write a query
with ROW_NUMBER() function and partition.
And it will return you a list of records with a row number if the row number is greater then 1 that means it is a duplicate record.
then you can use List filter to get only those records that have Row number = 1, so now you will have the Id as well as name and other attr of your aggregate.
Thanks Tousif Khan