Forum Discussion
MarkShnier__You
3 years agoQrew Legend
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
------------------------------
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
------------------------------