Want to identify the Unique Invoice numbers assigned to a sales rep and the count how many Unique Invoice numbers.

May 13, 201312 yr

Hi All,

I have not been on the forum for several years - hopefully I'm doing this correctly.

I want to identify the Unique Invoice numbers assigned to a sales rep and then count how many Unique Invoice numbers there are. I need the count so a can generate the average value of each sale. Example (first 30 records):

Rec - Sales Person Rec Num (Invoice) Summary Adam Cravens 2040122 Adam Cravens Adam Cravens 2040122 2040122 Adam Cravens 2040122 2040129 Adam Cravens 2040122 Total of two unique invoices Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040122 Adam Cravens 2040129 Brian Aufdenberg 2040116 Brian Aufdenberg Brian Aufdenberg 2040116 2040116 Brian Aufdenberg 2040116 Total of one unique invoice Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116 Brian Aufdenberg 2040116

Thanks and Take Care

Buck McCue

May 13, 201312 yr

8.5 Advanced? Hm...from the Sales Rep table, you have a relationship based on Sales Rep ID? You could use List ( SalesRec::Invoice ) to grab all the Invoices for that Sales Rep. Then use a custom function to grab the unique values.

Something like this:

UniqueValues( values )

Let([
this.value = GetValue ( values ; 1) ;
value.count = ValueCount ( values ) ;
new.list = RightValues ( values ; value.count - 1) ;

this.value = If ( IsEmpty ( FilterValues ( new.list ; this.value ) ) ; this.value ) ;
pilcrow = If ( not IsEmpty ( this.value ) ; ¶ ) ;
result = this.value & Case ( value.count > 1 ; pilcrow & UniqueValues ( new.list ) )
] ;
result
)