24
Views
8
Comments
Summarize by first letter

Hi Community,

It is possible to have a list of data summarized by the first letter of a particular field.

See my screenshot

Rank: #183

Hi Sylvain, yes its possible, but not out of the box, you need to code what you need

one possibility:   you need to query by the first letter, sort, repeating by all alphabet, and append your list in the way you want to present.

other way, I would start by this one: making a loop appending all letters of the alphabet one by one to your list, and sorting the Name attribute ASC

in this last option you can also add css to get that style to the First letter, eg: A, B, C... and remove the added letter that doesn't have  results under it, eg: if I added "C" but I dont find a word starting with "C" so I remove this letter from the results.


Hope I could give you some idea to solve your problem

cheers,

Carlos Lessa

Rank: #510

Hi Sylvain, 

I have tried to find something that matches your requirements, but I didn't find It. However, I saw a solution that might could help you.

  1. Create an Aggregate that retrieves only the first letters of the specific column using substr(str, 0, 1);
  2. Create a Web Block with an input parameter (letter) and a table record inside It.
  3. Create a List with the Aggregate created in Item 01.
  4. Put an Accordion inside the list and an Accordion Item in it.
  5. The title of the item is the letter and the body is the web block created in Item 02

Hope it helps,

Best Regards,

Kadu

Rank: #510

If you needed, here is the oml file.
The test is in the WebScreen1 screen.

OSMDbCB.oml

Rank: #258

While that is certainly a very simple and easy to understand solution, it does mean executing 27 queries to the database (unless you add some paging). Depending on the amount of data, you might take a performance hit. 

Another way could be to use 1 aggregate, and loop over that list. Then insert extra records into the results based on every letter you need. Just an empty record with the letter in the name column. Then, add some css into the table to give those letter rows a different look. (adding extra records, you might need to keep an eye on the index and paging) I think this is similar to what Carlos suggested.

mvp_badge
MVP
Rank: #2

That would probably be my solution as well, but Sylvain must be aware that you cannot insert items into the list you are looping over. Therefore I think the best solution is this:

  • Loop over the (sorted) list, determine which indexes new letters start, and add the letter and the index (using CurrentRowNumber) to a temporary list;
  • Next, sort the temporary list from highest index to lowest;
  • Loop over the temporary list, and use ListInsert to insert the right letter in the right position.

The reason to sort the temp list is that if you loop over it from lowest to highest, you have to take index shift into account (if something needs to be inserted in positions 2 and 5, after inserting at position 2 the next one is now 6). Possible by e.g. adding the CurrentRowNumber of the temp list to the index in the temp list, but reverse sorting is easier I think (though slightly less performant).

Paging is non-trivial in this situation, you're basically stuck with retrieving the entire list at once, unless you want to do some advanced trickery.

Rank: #258

You're right Kilian. I just wanted to point out the basic idea. Your solution is what I meant, but you described it way better ;-)


Rank: #183

Hi Sylvain, as I'm not soo good to explain my ideas ;) here is my demo for this solution

obs: the code attached is not dealing with search, pagination, etc... just a way to present as you asked

edit: although trying to help to present the asked list, when trying to do a real scenario, Killian approach will fit better

List.oml

Rank: #7599

Thank you guys. I had been thinking about the same solution, but wanted to make sure there wasn't some other magic solution. 

Thank you for your precious help.