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.
'newest' seems to imply a date of some sort. Do you have a way to know for sure the latest? If so, what field(s)?
Beverly
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?
You are correct. I meant foreign key. My brain swapped "primary" with "parent", ie. "parent key". They both start with P.
Edit - Tried phil's self-join and it works great! Thanks guys
I don't have an accurate creation date, unfortunately, as the original source of information began before we started using Filemaker to organize our data. I manually entered a lot of the old data, and we didn't keep as good records before, so the creation dates don't typically exist.
If they did, I would try to use that
Edit - beverly The column of numbers on the right side of my screen shot is the RunWeekID, the unique weekly number (we do orders by the week).
Where they created "in order" or at least is the creation order what you mean when you say that you only want to see "the newest ones"?
If so, you can do this:
Add a field to the table defined to auto-enter a serial number.
put that field on a layout based on the same table.
Show all records
Use Replace field contents with the serial number option to assign a serial number and be sure to click the option to update auto-enter options so that future records are number in same sequence.
PS this must be done when no one is actively editing the records in this table or records locks will lock you out of some records during this batch update.
By default a record has a "creation order", though we don't always know what that is. I suppose I would make sure to UNSORT, add a number (serial) to all existing records (with replace) and then make the field as auto-enter from then on to increment the serial. That would relatively let you find the latest for each product (highest number for each product).
Beverly
Show all records will list them in "unsorted" order....
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?