MichaelFrishma1
4 years agoQrew Trainee
New Formula Queries Don't Work when Adding Records?
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
------------------------------
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
------------------------------