I'm trying to filter records out of a portal, and the general consensus is to use SQL. Unfortunately, I don't understand it very well.
I have a bunch of product orders in a customer portal with a database ID that repeats itself (_pkProductID), What I'm trying to do is filter out every instance of duplicate product EXCEPT the newest ones.
Any help is appreciated.
A nonSQL approach:
Insert your Table occurrence names in place of mine
Say that you have this relationship:
LayoutTO ----< PortalTO
To that add a "self join": POrtalTOSameProduct is a new occurrence of PortalTO (click button with two plus signs)
PortalTO--<POrtalTOSameProduct
PortalTO::ProductID = POrtalTOSameProduct::ProductID
Sort this relationship so that the most recently created related record is "first". This can be a descending order sort on a creation timestamp field or a serial number field.
If your POrtalTO table does not have a primary key, you will need to add a field and populate it with a primary key for this to work.
Set up this portal filter on your portal:
POrtalTO::primaryKey = PortalTOSameProduct::primaryKey
and all older dups will be omitted.
How it works, the above reference in the filter expression will return the same value for PortalTOSameProduct::primaryKey for all records with the same product ID
PS
_pkProductID appears to be misnamed. _pk is normally used to identify a primary key. but in this table, you've indicated that this value is not unique. If it's not unique, it's not a primary key. Perhaps you meant to name it _fkProductID? where the "f' stands for foreign key?