Discussions

 View Only
Expand all | Collapse all

GetFieldValues query, but only get unique values?

  • 1.  GetFieldValues query, but only get unique values?

    Posted 06-17-2022 15:24
    Is there a way to use the GetFieldValues query, but have it only return a string of unique values? Eliminating duplicates?

    ------------------------------
    Mike Tamoush
    ------------------------------


  • 2.  RE: GetFieldValues query, but only get unique values?

    Posted 06-18-2022 14:30
    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= 
    GetFieldValues(
    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)
    mark.shnier@gmail.com
    ------------------------------