I'll try to keep this short, however, I'm fairly new, so please be patient.
We need to display a ‘status’ value from a database document, determined by the latest of 2 dates.
Date field 1 shows when any part of the document was last modified; date field 2 shows the date the status was entered.
Our DB developers have unfortunately broken the ODBC views for the document into 2 sections, with date fields separated.
View one shows most of the document, with the document date. View 2 shows the 'Status question' with the status date.
We then need to bring in a third view to show the last date the document was modified.
Using 1 or 3 of these, using the MAX expression, the table shows the latest date in most cases, but first status in most cases
instead of the latest status.
On the attached sample data, some have displayed correctly, the second group are some which don't.
ID Date Status
1077 4/12/2017 Access request form submitted to NDIS - eligibility tested - outcome pending
1171 28/11/2017 Access request form submitted to NDIS - eligibility tested - outcome pending
1256 14/09/2017 Eligibility not tested - declined to apply
1314 27/10/2017 Eligibility tested - client found not eligible - permanent disability not established
For many the ‘Status’ and/or date are incorrect. Some examples
1648 27/10/2017 Eligibility not tested - declined to apply
Should be 27/10/2017 Access Request Form not yet submitted – gathering evidence
2010 30/10/2017 Access Request Form not yet submitted – gathering evidence
Should be 26/10/2017 Eligibility not tested - declined to apply
2164 25/10/2017 Eligibility not tested - declined to apply
Should be 25/10/2017 Access Request Form not yet submitted – gathering evidence
Is there a way to format the date for the ‘Status’ field to be only from the latest ‘Status' date?
Status test.tde.zip 89.9 KB