List Page's Filter Option with Distinct/Group By

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
guillerbm7
User
Posts: 13

List Page's Filter Option with Distinct/Group By

Post 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.


mobhar
User
Posts: 11660

Post 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.


guillerbm7
User
Posts: 13

Post 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.


arbei
User
Posts: 9284

Post by arbei »

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


guillerbm7
User
Posts: 13

Post 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?


arbei
User
Posts: 9284

Post by arbei »

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


Post Reply