Grouping and sorting datas

[Korbib]

Hi there,

I have a collection with musicians and I want to group them by Instrument in alphabetical order and sort each group by name in alphabetical order like this :

Bass

• A
• B
• C

Drums

• A
• B
• C

Guitare

• A
• B
• C

Keyboards

• A
• B
• C

Vocals

• A
• B
• C

I can do this with each instrument in a different collection but I can’t use the search bar option.

I know there are some tricks to do this but I didn’t understand the process

[ThinhDinh]

Let’s say sample data is like this.

I have a sort column, which is a JS function.

function calculateValue(name, instrument) {
  return 10 * (instrument[0].toUpperCase().charCodeAt(0) - 64) + (name[0].toUpperCase().charCodeAt(0) - 64);
}

return calculateValue(p1, p2)

The function takes the first letter of the instrument and the name, converts them to their position in the alphabet (A=1, B=2, etc.), and then calculates a number that lets you sort your list first by instrument (alphabetically), and then by name within each instrument group. This way, when you sort by the result, your musicians are grouped and ordered just as you described.

[Korbib]

Thanx for the reply

What am I doing wrong ?

[ThinhDinh]

Here’s an alternative approach.

function calculateSortValue(instrumentsJoined, namesJoined, currentRow) {
  const [instrument, name] = currentRow.split('||').map(s => s.trim().toUpperCase());
  const instrumentList = Array.from(new Set(instrumentsJoined.split('||').map(s => s.trim().toUpperCase()))).sort();
  const nameList = Array.from(new Set(namesJoined.split('||').map(s => s.trim().toUpperCase()))).sort();
  const instrumentIndex = instrumentList.indexOf(instrument) + 1;
  const nameIndex = nameList.indexOf(name) + 1;
  return instrumentIndex * 1000 + nameIndex;
}

return calculateSortValue(p1, p2, p3)

[Korbib]

Ho yeah, it works !

Thanx a lot @ThinhDinh

[ThinhDinh]

It wasn’t working correctly for your “Batterie” and “Basse” groupings as well, so I want to make sure it sorts instruments A-Z first, then names inside each group A-Z.

Happy to help!