0

Find Latest Value from Subtable

I have a table called Hydrants and I have a subtable for Hydrants called Static Tests. In the Static Tests subtable, I have a numeric field called Static PSI and I have a date field called Test Date. What I want to do is show the latest Static PSI value using a formula on the Hydrants view.

The relationship between Hydrants and Static Tests is called Static Testing and is a 1:N relationship.

I have tried using last('Static Testing').'Static PSI' and it almost works, except it shows me the last entry recorded which is not necessarily the last entry by Test Date. I want the last entry based on Test Date, and I have tried using a select statement but that gives me the last entry of all Static Tests records, which doesn't work.

Any suggestions to get this to work?

Thanks.

2 replies

null
    • Fred
    • 9 days ago
    • Reported - view

    You can use the order by command.

    last('Static Testing' order by 'Test Date').'Static PSI'

    You can use any field in 'Static Testing' to order by. If it is a number (maybe even a date, try it out) then you can put a ( - ) in front of the field name and it will reverse sort.

      • Ian_Farlow
      • 8 days ago
      • Reported - view

       Not long after I posted this I tried again and it worked. So I must have had a typo somewhere and was overlooking it when trying to debug. Thanks for the help, however, and I was unaware of the way to reverse sort, so thanks for the tip.

Login to reply

Content aside

Categories

View all topics
  • 8 days agoLast active
  • 2Replies
  • 26Views
  • 2 Following