Is there a way how to create a lookup which will show only distinct or group by values in drop-down box? e.g. there is an entity with Date column and I want to show the date only once in the drop-down box.
Thanks.
koki
I would try to modify a standard lookup by assigning some aggregation function to a column Id in a datastructure of a lookup - e.g. min, or max. You have to make sure that the column name remains ‘Id’. This way you would group by second column (a Date one).
But generally it’s not a good idea to have a foreign key with target in a parent table with duplicate values.
What do you expect to be stored in child table as a foreign key ? Min Id, max Id, random Id (from a group)? Wouldn’t be better to do some data cleanup first? What’s your actual use case for that?
Hi Vaclav,
I need unique values for Selection dialog drop-down box. So the user can see dates only once. I need the date as a parameter for filter afterwards. If I understand well the Lookup Datastructure should have only 2 columns Id and Date and aggregation max or min set to column Id. The date will be grouped by Date automatically. Is it correct? I had alternate solution in mind - to create a view with select distinct or a SQL function.
koki
Hi Koki,
yes, in general you need 2 columns where one is ideally holding still the same value (colA) and the other is the one with distinct value (colB). Then you aggregate colA then colB will be agregated too and you achieve exactly what you described.
The unwanted thing is you must have both columns in lookup to be seen otherwise it is now working.