Skip to main content
Question

Formula Query get prior value only

  • November 5, 2021
  • 0 replies
  • 148 views

Forum|alt.badge.img+8
I'm trying to capture the record ID of the previous activity in an activity chain that is just below the most recent for a specific unit ID. Here's my query in a formula multi-select text field:

// 58 Completed DateTime,    24 Unit ID,    149 Task Rank
var text Query = "{58.BF.'" & [Completed DateTime] & "'} AND {58.BF.'" & [Inventory Unit - Latest Completed DateTime] & "'} AND {24.EX.'" & [Inventory Unit - Unit ID] & "'} AND {149.LTE.'" & [Task Rank For Unit] & "'}";

GetFieldValues(GetRecords($Query), 3) // P1

You'll see above, that I'm looking for the before datetime of the current record, and before datetime of a lookup field for the latest record. I'm also constraining the search to the unit ID and the previous rank (I added a formula query to rank all activities in descending order). My question is, how do I get just the one previous value and exclude anything prior to that?

I've attached an image of two Units and their activities. You'll see that the top activity for each unit (the latest activity) displays all activity record IDs prior, but I only want to show the previous activity record ID and nothing before that.

------------------------------
AR
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
One thing I can tell you is that it is currently not possible to control the short order of the returned values. In my experience they return sorted by Record ID with the lowest Record ID first. If the lowest record ID happens to meet your needs then you can get the string of all the Record ID's and parse out the very first one on the list and that will be the lowest Record ID.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------