Alphabetical field index

[jessica_webb]

I have a field in my script called 'Name' with following values:

John

Mark

Aaron

Tomas

Benjamin

I cannot change the load order.

I then have a report with a separate section for each name. In my title, I am using the following:

=Name &' - '&FieldIndex('Name', Name)&'/'&FieldValueCount('Name')

The problem is, whilst my report loops through 'Name' alphabetically (which is what I want), the field index is based on sort order, so I get the following titles:

Aaron - 3/5

Benjamin - 5/5

John - 1/5

Mark - 2/5

Tomas - 4/5

What I want is:

Aaron - 1/5

Benjamin - 2/5

John - 3/5

Mark - 4/5

Tomas - 5/5

Any ideas? Needs to be totally dynamic as I will never know what the names are, nor how many different names there will be.

Thanks,
Jess

[sunny_talwar]

What if you try using Rank(Name)? Does that solve your issue?

[sunny_talwar]

Rank seems to be going the opposite

[sunny_talwar]

May be this

=Name &' - '&(FieldValueCount('Name') - Rank(Name) + 1)&'/'&FieldValueCount('Name')

[jessica_webb]

Hi Sunny,

Thanks for the suggestion. Unfortunately, it doesn't work outside of the table.

If you see the attached, I've added a text box with your expression. When I select any name in the list box, it always gives me 5/5

[sunny_talwar · Accepted solution]

Try this

=Aggr(Name &' - '&(FieldValueCount('Name') - Rank(Only({<Name>} Name)) + 1)&'/'&FieldValueCount('Name'), Name)

[jessica_webb]

Perfect! Thank you so much