AnsweredAssumed Answered

ExecuteSQL in Repeating Field

Question asked by Jason Wood on Mar 31, 2017
Latest reply on Apr 3, 2017 by beverly

Unexpected result when using ExecuteSQL with "OR" keyword on multiple repetitions of repeating field...

 

ExecuteSQL ( "SELECT COUNT(*) FROM PROFILES WHERE phone[1] = '123456789'" ; "" ; "" ; "" )

 

Result is 1 when the first repetition of "phone" field contains '123456789'

 

ExecuteSQL ( "SELECT COUNT(*) FROM PROFILES WHERE phone[1] = '123456789' OR phone[2] = '123456789'" ; "" ; "" ; "" )

 

Result is 1 only if the second repetition of "phone" field contains '123456789'. Otherwise the result is 0. I expect it to be 1 when the first repetition is '123456789' but it doesn't work.

 

I tried replacing "phone[1] = '123456789'" with "nameFirst = 'Name'" and in this case I do get a result of 1 if EITHER test is true, but no such luck when both sides are the repeating field.

 

Bug?

 

FMA 14.0.6

 

Basically I just want a select statement to see if a specified phone number exists in any of the 3 repeating phone number fields. Yes I realize that a portal would be better but this is what I have!;-)

Outcomes