this is a bit hard to explain, but I'm experiencing a strange situation when trying to get data from a related record that is multiple hops away (multiple table occurrence's away)
I have a relationship setup like this ... table 1 -> table 2 -> table 3 where table 1 relates to table 2 via a "name" field. In this relationship, there will almost always be multiple matches.
table 2 -> table 3 is a simple primary key match ... fktable3 -> pktable3
My goal is to get the most recently added value in table 3 using this relationship, so I added to the table 1 -> table 2 relationship a reverse sort by date created, so it returns the most recent related value in table 2 at the top and older ones at the bottom.
When I on table 1 and setup a portal to show table 2 using the relationship, the portal looks correct. most recently created records are at the top and it shows the correct table 3 data in the portal.
But, if I don't use a portal and from table 1 just script a "set variable" to be pktable 3 it returns the earliest created record in table 3 and not the correct related table.
I have attached a simple database example of the problem.
my question is how do I get the table 3 value of the most recently created record in table 2
JoeSmith (created 3/10/2018, pktable2 = 1, fktable3 = 3)
JoeSmith (created 3/15/2018, pktable2 = 2, fktable3 = 1)
JoeSmith (created 3/19/2018, pktable2 = 3, fktable3 = 2)
Since the most recently created related record in table 2 is pktable2 = 3 ... then I want to set the variable with that table3 related value OR ... fktable3 = 2.
But instead it is returned the 1st created record in table 3 ... fktable3 = 1. as if it is ignoring the relationships sort values.
The sort is setup in the TOG and not in the portal.
Hopefully the above with the attached file will help this makes sense. really hard to explain the issue.
what am I doing wrong??