Forum Discussion

Qrew Trainee

4 years ago

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

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 que...

formula queries

MarkShnier__You

Qrew Legend

4 years ago

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
Qrew Trainee
4 years ago
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.'"&[Name Current User Email Text]&"'}AND{649.BF.'"&Today()&"'}AND{6.EX.'Assigned in Transcription'}","abcdefgh2"))>0, 1,0));

The documentation says to use the OAF, OBF, AF, BF operators for time/date fields, and that worked.

------------------------------
Daniel Johnson
------------------------------