How to ask if GetFieldValues output contains values less than a particular value?

Question

Hi All,I've got a formula query like so:GetFieldValues(GetRecords("{865.EX.'"&amp;[Name Current User Email Text]&amp;"'}","abcdefgh9"),649))First, I couldn't figure out how to get the formula query to work with user fields, so I got it working with a text field. I think it has to do with the expression operator, i.e. "EX", but I couldn't get it to work with any others.That query, if ToText is put in front, will produce results like this:11-25-21 ; 12-06-21 ; 12-07-21 ; 12-08-21FID 649 is just a date field. What I want to know is if any of those values are less than ToText(Today()). How do I do that?Thanks,------------------------------Daniel Johnson------------------------------

markshnier__you · Answer

I will give it a try but I do not claim to be a formula query expert! I struggle with the single quotes

Make a field called [Today] with a formula of Today()

Then try this as a formula numeric field

var text Query = "{865.EX.'"&[Name Current User Email Text]&"'} AND {649.LT.'" & [Today] & "'}";

Size(
GetFieldValues(GetRecords($Query,"abcdefgh9"),649))

It should return the number of records with an additional filter of less than today.

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

danieljohnson2 · Answer

Hey Mark,Thank you as always for the suggestion. I came to that same conclusion after staring at the formula for an hour+ yesterday. It's being used in a boolean variable, so here's what I've got:ToBoolean(If(Size(GetRecords("{865.EX.'"&amp;[Name Current User Email Text]&amp;"'}AND{649.BF.'"&amp;Today()&amp;"'}AND{6.EX.'Assigned in Transcription'}","abcdefgh2"))&gt;0, 1,0));The documentation says to use the OAF, OBF, AF, BF operators for time/date fields, and that worked.------------------------------Daniel Johnson------------------------------

Forum Discussion

How to ask if GetFieldValues output contains values less than a particular value?

2 Replies