Forum Discussion

MikeTamoush's avatar
Qrew Commander
3 years ago

GetFieldValues query, but only get unique values?

Is there a way to use the GetFieldValues query, but have it only return a string of unique values? Eliminating duplicates?

Mike Tamoush

1 Reply

  • Mike, I think that this requires two Formula Query fields.  The first one would identify if the record was the one with the lowest [Record ID#] for the records which match the unique field.

    In my example the "unique field" is [NPI].

    When querying for fid 3, they are returned in Record ID# Sequence so the leftmost part of the returned string is the rid of the first record with that unique value.  This would be a field called [Record is Lowest Record ID# for this NPI?] and would be a Formula checkbox field type.

    var textlist RecordIDsForThisNPI= 
    GetRecords("{73.EX." & [NPI] & "}"),3);

    var number LowestRID = ToNumber(Trim(Left(ToText([RecordIDsForThisNPI]),";")));

    [Record ID#] = $LowestRID

    Then a second formula query would bring in the textlist of unique values by filtering on just where the  [Record is Lowest Record ID# for this NPI?] is true.

    Mark Shnier (YQC)