table list only most recent for each related record

Archived from the Xataface Users forum.

cookie720 — Tue Aug 07, 2012 4:37 pm

I have a table called status, which is a lookup of whats going on with a matter.
I have set my database up so that it keeps the previous status and when you create a new status, it gets the old data as default. This allows a sort of “history” of whats going on with a particular matter.
In my main view, I am listing only the most recent status record by

Code: Select all: function __sql__(){ return "SELECT * FROM `status` ORDER BY `StatusID` DESC"; }

then using

Code: Select all: $related_records =& $record->getRelatedRecordObjects('status'); $content = 'To Do: '.$related_records[0]->htmlValue('ToDo') .' Awaiting: '.$related_records[0]->htmlValue('Awaiting');

to list only those two fields from the first (“most recent”) record.

The problem im facing is obviously when i click onto this status tab (up the top) it lists ALL statuses via that SQL query, So I get a table full of unwanted data. I only ened the most recent for each related record (matter).

doing LIMIT 1 just breaks everything…

Thanks in advance for all the smart people that can help me

shannah — Tue Aug 07, 2012 5:25 pm

What does the relationship definition for the status relationship look like?

cookie720 — Mon Aug 13, 2012 4:50 am

my “matters” table is the table everything links to, all my other tables have the same relationship.ini, as follows for the status relationship.ini:

Code: Select all: [matters] section:visible=0 matters.MatterID = "$MatterID"

and my status definition in the matters relationship.ini:

Code: Select all: [status] section:visible=0 status.MatterID = "$MatterID"

cookie720 — Wed Aug 15, 2012 4:53 pm

i still cant get it working my i know a bit about sql but with the dataface records stuff im kind of clueless , anyone?