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.'"&[Name Current User Email Text]&"'}","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------------------------------

MarkShnierYou · 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
------------------------------

Sign up

Login to the community