Page 1 of 1

List Page's Filter Option with Distinct/Group By

Posted: Thu May 25, 2023 7:03 pm
by guillerbm7

HI, I'm working on a project and I need to filter a Lookup field without duplicates. I'll explain what I have in the DB:

A table named "planning" with a Primary key planID and 2 columns, "quarter" and "year" (besides another fields).
Another table name "whatever" with a column named planID which use the Lookup and is the filter (besides another fields).

The problem is the second table having multiple fields with the same quarter and year. Because of that when I use a filter in the "whatever" table, it shows multiple times "Q2, 2023".

I know explained like that it's like "Don't duplicate fields in the "planning" table" but it has also other columns and data which is unique. The project's DB is untouchable, I can only add stuff, not remove, nor modify fields.

I was wondering if it's possible to use kind of a "Distinct" or Group By option in the Filter on List Page.


Re: List Page's Filter Option with Distinct/Group By

Posted: Thu May 25, 2023 7:14 pm
by mobhar

guillerbm7 wrote:

The project's DB is untouchable, I can only add stuff, not remove, nor modify fields.

How about to create a Database View? Can you do that? If so, then you may Database View that already transpose the multiple fields into single field, and use it as Lookup Table.


Re: List Page's Filter Option with Distinct/Group By

Posted: Thu May 25, 2023 7:35 pm
by guillerbm7

That's a nice idea, but how do I use lookup table without an id? I mean, I have to connect the tables by the "planID" column. If I add it to the view, it'll duplicate again.


Re: List Page's Filter Option with Distinct/Group By

Posted: Fri May 26, 2023 9:25 am
by arbei

When you create the view to be used as lookup table, you should use DISTINCT.


Re: List Page's Filter Option with Distinct/Group By

Posted: Fri May 26, 2023 4:13 pm
by guillerbm7

I can't use DISTINCT on a view, I've used "GROUP BY year, quarter" instead though. I've selected planID, quarter, year.

It only collects the first planID having that quarter and year so it doesn't work properly.

Am I understanding you wrong or any other idea?


Re: List Page's Filter Option with Distinct/Group By

Posted: Fri May 26, 2023 4:41 pm
by arbei

Then you need to add display field #3 to distinguish different planID with same quarter and year.