I'm not sure I'm decoding the SQL correctly.
Can you describe in general English what you are trying to accomplish with this?
(Some relationships that are possible with SQL require scripting in order to accomplish equivalent results in FileMaker.)
Basically its going to show me only the max date and sequence (multiple records with the same date) record that is less than the current date.
I know that I could show all records less than the system date and sort them but I dont want to do that I only want to see the currently effective row/record (I plan to use a custom security features to bring selected users to a layout where they may be able to view and/or edit history)
Part of my blank may be not having FMP handy.
FMP 11 Adv
Yes, but if you use that sort, there are several ways to keep all but that most recent record from being visible and accessible in a portal.
You can use a one row portal. You can also do this with a filtered portal.
Relationship for a one row portal:
Table::cToday > TableSelfJoin::effDate
(and you can use additional pairs of fields if you need to be more specific.)
Then you can either sort the portal by effective date and sequence in descending order or you can sort the relationship by effective date and sequence. With a one row portal, you get identical results either way, but the sorted relationship may be the better option if you then need to perform calculations that refer to or alter this related record.
For that matter, with the sorted relationship, you don't even need the portal with a layout based on Table, you can just add the fields you need from TableSelfJoin directly to the layout.