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

[Jimmy Buck]

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

 

 

 

[David Jondreau]

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
)

[Lee Smith]

If you have updated your version of FileMaker, please update your Profile to reflect  this information. Here is a quick link to aide you MY PROFILE.

Automatic message

This topic has been moved from "Member Lounge" to "Calculation Engine (Define Fields)".