Hi - new to Knack but not to app development. I'm trying to do something that should be simple, but can't find a way to do it. I have two objects: Provider and Service. They have a many-to-many relationship: A Provider can offer several Services and a Service can be offered by many Providers. In this case, I created the connection in Knack from Provider to Service. What I want to do is create a list of all Providers with the Services each Provider offers. This is fairly easy to do, but the order of the Services as they are listed per Provider comes out somewhat random. I would like the order to follow a meaningful sequence. I added a "Sort Order" field to the Service object and changed the Service object's settings to sort on the "Sort Order" field. That does sort the Service records when looking at the Service object, but it does not sort the Services per Provider on the page with the list in it. There does not seem to be a way to set the order in which connect objects will appear in a list.
Thanks, Tony. Yes, I am talking about the List View, albeit I'm open to any other solution that solves my problem. I have used the Sort feature for the List View but it appears to only sort on fields in the main object, not fields in the connected object. I had set the primary sort to Provider Name and the secondary sort to Services. The result is the list is sorted by Provider Name but the Services per each Provider appear in a random order.
Thanks, Julian. As a former DBA, I too usually resolve my many-to-many relationships into tables and gave that idea a try. The reason I didn't start that way is, as far as I can tell, there isn't a way to build a simple report between a parent and child via a "Join". Trying your idea, I created a "Provider Service" object and connected it to both the Provider and Service objects (both many-to-one). Then I added calculated fields to the "Provider Service" object to copy the values of the Provider Name from the Provider object and the Service Name and Sort Order from the Service Object. I created a few test records easily and then created a new Page with a new List View and sorted it primarily on Provider Name and secondarily on Sort Order. The result did sort as I wanted and expected (hooray); however, the Provider Name repeats for every Service a Provider offers which looks bad. So now I have a new problem in that I need the list to display better. Most report writers have a way to "group by" so you can hide values that repeat from one record to the next, but I can't seem to find a way to do that in Knack. Any ideas?
I think this is an example where you are probably best served to use a join table rather than a many to many relationship (which I generally avoid). In this way you can show tables of related records sorted as you wish.
If you are talking about an actual list view, open the list settings and open the data source section from the top left side and you will see the options for sort order