I have a list of companies that have a number of stores located in different countries. My database structure looks like this:
Data Type: Company
Company 1:
Name: Nike
Stores_Location: USA60, Canada5, China30
Company 2:
Name: Puma
Stores_Location: Italy40, USA30, Canada50
For example, Company 2 has 30 stores located in the USA.
What I want to do is display the companies in a repeating group sorted by a specific country with the number of stores. For example:
The user selects “USA” from the dropdown.
The user clicks the “Sort” button.
The companies are sorted by the number of stores in the USA, displaying:
Nike with 60 stores in the USA
Puma with 30 stores in the USA
How can I make that happen? The dropdown only contains the names of the countries, so I can’t just sort like Sort By: This repeating group's list of companies: each item's stores_location is dropdown's value. It won’t work that way.
I don’t want to change my data base structure either.
I know this is not what you want to ear, but you db structure is not very convenient to sort your companies by number of store location.
It’s doable but you will have to put text as repeating group datasource, transform your companies as a list of text in a custom format with regex, split and sort this list of text.
However, when done, you will have to do again a search in each item of repeating group if you want to retrieve other informations about company. This is not optimal at all.
search for companies
Format companies as text and put with regex in each item the number extracted from stores_location filtered by your dropdown value (put also the company name for future search) you will have a list like that:
30,Puma;60,Nike
Split this text by “;” to have a list of text
Sort this list descending, you will now have a list of text sorted for your repeating group like that:
60,Nike
30,Puma
-In your repeating group, you can reuse this text or do a search for company with the text of the cell by getting again the company name with a regex.
Again, it is not at all a good practice.
If you need to make complex sorting again, you should think to reorganize you db structure.
Hi Anais, Assuming that keeping your DB the way it is, is the priority. You can constrain the search (location contains dropdown’s value) and set the sort by to location. If the country name part of that string ‘USA30’ is always the same, bubble will sort them by the number part of the string. The only catch is that the numbers have to be the same length ie 500,050,004 etc. I tested this out quickly just now and it seemed to work
Hi Adam, It doesn’t let me ‘group by company’’ .
Company is a data type, not a field. And Store_Location is a list of things, and it also not possible to group by Store_Location
It shouldn’t be: 30,Puma;60,Nike.
Puma is the name of the company and that company has a number of stores located in different countries.
Name: PUMA
Stores_Location: 30,USA;60,CANADA
the list 30,Puma:60,Nike is only a temporary list. I put the number first to be able to sort.
Once sorted, you can retrieve you company information by doing a search company with name constrain, and display all company information as you want.
But actually, it’s the same problem with my method: Sorting is alphabetical and not by number.
I think you should really consider a change on db structure
