New Formula Queries Don't Work when Adding Records?

Question

I'm trying to use the new formula queries to prevent the addition of a new, duplicate record in my "Tours" table. There are reasons why I cannot just use the Unique flag...

The Tour ID is the key field and is in the format "102521HL1" (MMDDYY and "HL1").

I have the Tour ID being created automatically based on the departure date of the tour, concatenated with "HL1". I want the formula field to query all the existing Tour ID's in the same table and return either a multi-text list of existing records or the number of records that are the same.

I've tried both:
GetFieldValues(GetRecords("{6.EX.'"&[Tour ID (Paste)]&"'}"),6)
and
Size(GetRecords("{6.EX.'"&[Tour ID (Paste)]&"'}")
where [Tour ID (Paste)] is a static text field that is populated from a formula using a form rule.

I've also tried where [Tour ID (Paste)] is replaced directly with the formula field, but that doesn't work either.

The result of my formula query is always "???"

When I replace the field name in the formula with a static value of "090821HL1" it works fine. Why is my reference not working?

------------------------------
Michael Frishman
------------------------------

MarkShnierYou · Answer

Try this.  I know the quotes are tricky.Size(GetRecords("{6.EX." & [Tour ID (Paste)] & "}"),3))If that is 1 (or higher) when you are in add mode and not yet saved, that means it will case a duplicate.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Sign up

Login to the community