Skip to Content (Press Enter)
2020-11-13 06-20-57
Kane Loony
38Views
10Comments
Solved
How to handle duplicate data on dropdown with souce join condition
Question
Traditional Web
Application Type
Traditional Web

Hi All,


I have a screnn to display form the customer data from table CUSTOMER. In the form there is a dropdown that displays the city of the customer.


Problem

Dropdown will displays customer city data from another table, let's say table CITY. I did join the table CUSTOMER with table CITY. But when I want to update or add customer data, dropdown section displays duplicate data because of the join conditions that I did. How to solve this


Thanks,

Kane

0
0
22 Sep 2022
Copy Link
2021-06-02 20-50-04
Márcio Carvalho
Solution

Replying to Kane Loony's comment on 22 Sep 2022 at 16:08:11

Try it now.

Your Customer table was wrong.; you should have a reference to the city on Customer Entity and not a text data type.


Also, you don't need an extra aggregate. You just need to remove the city from table from the customer aggregate and use it directly on the Combobox as the city table's source entity.


Check the OML, and sorry for the first time, I am not used to traditional :D.

See solution in context
MyStore_v5.oml
0
0
22 Sep 2022
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

Hi All,

i think some more profound flaws are overlooked here :

it looks like you want to indicate what city a customer lives in.  Then your city attribute in customer should not be of type text, but a reference attribute to city.

In the preparation, the problem is that you are attempting to source both your single customer record and your city dropdown with a single aggregate, that will never work.

So you have an aggregate called GetCustomerById, but it is going after cities with or without customer, if you even want to join with cities here, it should probably be customer with or without city.  But there is no need to join, just read the customer by id.  

Also the filter is wrong, there should not be an 'or customer.id=nullidentifier', as that would return an existing record when you are trying to add a new one.  that's why it is already filled when you add new one.

What is completely wrong, is that you are using that aggregate, that is meant to retrieve the current customer record, as a source for your dropdown.  That will never work.  You can either base the dropdown on the city entity, or in your preparation, you will need a completely different aggregate, retrieving the list of cities.  In the fixed oml, I went for the first option.  

I also added a record in special list, so dropdown is empty initially.

What I also had to change was the list of customers, as the name of the city is no longer part of the customer entity, you have to join there with city, change the expression for city column and change the filter.

see attached your oml changed,

Dorine

PS an unrelated remark

it is questionable to categorise your customers by gender, but if you are doing that, it might be advisable to not do this as free form text

See solution in context
MyStore_Changed.oml
0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony

Hi Dorine,

Thank you for your suggestion & detail answer, I apreciate that. Like Marcio answer, it's worked for me to

0
0
22 Sep 2022
Copy Link
2021-06-02 20-50-04
Márcio Carvalho

I would say to use group by or list distinct actions. But always depend on the use case.

Can you replicate your problem in an OML and share it here?

From your example, I don't know if a customer can only have a city or if it has more than one city.

https://www.outsystems.com/forums/discussion/55461/how-to-get-distinct-value-in-a-record-list-using-filter/

Try to follow some of the answers from this post and other similar posts on the forum.

Kind Regards,

Márcio

0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony

Hi Marcio,

Sorry for late reply. This is my oml. And why when I create a new customer, the input is already filled

Thanks

MyStore_v5.oml
0
0
22 Sep 2022
Copy Link
2021-06-02 20-50-04
Márcio Carvalho

Replying to Kane Loony's comment on 22 Sep 2022 15:47:57

Try to do a group by

On a new aggregate that will be used on the dropdown


And then change this on the dropdown

Check the OML and let us know if that helps you

edit: You can also create an aggregate as I did but remove the table customer

Kind Regards,

Márcio

MyStore_v5.oml
0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony

Replying to Márcio Carvalho's comment on 22 Sep 2022 15:58:05

Hi Marcio,

Ok it's worked but why if i want to create new customer , all input is already filled

And if i want to update city on my customer just shown 1 city

Thanks

0
0
22 Sep 2022
Copy Link
2021-06-02 20-50-04
Márcio Carvalho
Solution

Replying to Kane Loony's comment on 22 Sep 2022 at 16:08:11

Try it now.

Your Customer table was wrong.; you should have a reference to the city on Customer Entity and not a text data type.


Also, you don't need an extra aggregate. You just need to remove the city from table from the customer aggregate and use it directly on the Combobox as the city table's source entity.


Check the OML, and sorry for the first time, I am not used to traditional :D.

MyStore_v5.oml
0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony
2025-10-18 11-13-53
Ramesh subramanian

Hey Kane Loony,

use Group by user . But one user have more than one city , correct ?

please share me OML file. I will try to find the answer.

thanks,

Ramesh



0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony

Hi Ramesh,

Sorry for late reply. My oml attached on comments Marcio

0
0
22 Sep 2022
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
Solution

Hi All,

i think some more profound flaws are overlooked here :

it looks like you want to indicate what city a customer lives in.  Then your city attribute in customer should not be of type text, but a reference attribute to city.

In the preparation, the problem is that you are attempting to source both your single customer record and your city dropdown with a single aggregate, that will never work.

So you have an aggregate called GetCustomerById, but it is going after cities with or without customer, if you even want to join with cities here, it should probably be customer with or without city.  But there is no need to join, just read the customer by id.  

Also the filter is wrong, there should not be an 'or customer.id=nullidentifier', as that would return an existing record when you are trying to add a new one.  that's why it is already filled when you add new one.

What is completely wrong, is that you are using that aggregate, that is meant to retrieve the current customer record, as a source for your dropdown.  That will never work.  You can either base the dropdown on the city entity, or in your preparation, you will need a completely different aggregate, retrieving the list of cities.  In the fixed oml, I went for the first option.  

I also added a record in special list, so dropdown is empty initially.

What I also had to change was the list of customers, as the name of the city is no longer part of the customer entity, you have to join there with city, change the expression for city column and change the filter.

see attached your oml changed,

Dorine

PS an unrelated remark

it is questionable to categorise your customers by gender, but if you are doing that, it might be advisable to not do this as free form text

MyStore_Changed.oml
0
0
22 Sep 2022
Copy Link
2020-11-13 06-20-57
Kane Loony

Hi Dorine,

Thank you for your suggestion & detail answer, I apreciate that. Like Marcio answer, it's worked for me to

0
0
22 Sep 2022
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.