Page 1 of 1

Sorting a relationship field

PostPosted: Mon Nov 25, 2013 6:45 pm
by VelvetLounger
I have a todo table that is connected to a project table using a relationship based on the project name. When I try to sort my todo list by project name in list view it does not sort. I thought it may be sorting based on the foreign key due to the relationship but its not even doing that. Any suggestions?

Re: Sorting a relationship field

PostPosted: Mon Nov 25, 2013 7:49 pm
by mjhanna
I am just a user like yourself, but I have found what you observe to be true. The value stored in the relationship field is not what you see displayed, it is the link code. But there are "workarounds". You can sort on the value retrieved by a Relationship. The trick is to define an extra field in the retrieving database whose value is based on the related source database value, but which is a type of field that can be sorted. Assuming the relationship value retrieved is text (e.g. a project name) this only requires defining one extra field in the retrieving database.

Define a Conditional field which performs a "do nothing" condition on the text from the Relationship field value. An example condition is:
IF FIELD (Relationship field) IS EQUAL TO
FIELD (Relationship field)
OUTPUT IS (Relationship field)
ELSE OUTPUT IS (Relationship field)

Now define the sort in the View based on this Conditional field instead of the Relationship field. Usually you would display the text Relationship field value but keep hidden the Conditional field output.

Hope this gives you ideas,

Re: Sorting a relationship field

PostPosted: Mon Nov 25, 2013 7:56 pm
by mjhanna
As a further note, since you are using calculations based on Relationship fields, you will want to be sure to have the option "Recalculate on Relationship Lookup" turned on. I "believe" this option currently is only available on the iOS and Android versions, and not the Desktop. You also want to have the hidden Conditional field come in field order after the Relationship field since it depends upon that field.

Re: Sorting a relationship field

PostPosted: Tue Nov 26, 2013 12:55 am
by VelvetLounger
I ended up using the External Field - Combine Text and just took the Project field and put it into a text field. Now I can sort based on the text field. Thanks for feedback

Re: Sorting a relationship field

PostPosted: Tue Nov 26, 2013 6:46 am
by Brian_Houghton
Thanks for sharing your workaround.

To clarify, due to performance reasons, sorting on relationship fields is not supported at this time.

Sent from my Nexus 7 using Tapatalk HD

Re: Sorting a relationship field

PostPosted: Tue Nov 26, 2013 10:09 am
by mjhanna
VelvetLounger wrote:I ended up using the External Field - Combine Text and just took the Project field and put it into a text field. Now I can sort based on the text field. Thanks for feedback
Yes, that will work as well. Anything, like my Conditional action, or your Combine action, that "uses" the Relationship field to produce text will now allow that resultant text to be sorted.

Re: Sorting a relationship field

PostPosted: Sat Dec 12, 2015 4:50 am
by Rinho
I should have search for this before!
I work in technical support and wanted to build a tool to manage what I do.
the most ergonomic way I found (always on the road) was to split my datas in many databases (customers, contacts, units, planning, week number, visits, support, following...)
using relationship to browse from a page to another, starting the day opening only one DB.
I'm now at the step "creating report" and I'm stuck because of the sort issue.
A bit more complex to use (especially when you want to fill DB using import function and get the link working) but will Link/Linked field have the same issue?

Re: Sorting a relationship field

PostPosted: Sat Dec 12, 2015 11:04 am
by Brian_Houghton
Hi,

Yes, you would encounter the same issue with link/linked fields.

Re: Sorting a relationship field

PostPosted: Sat Dec 12, 2015 4:21 pm
by Rinho
Well, I'll go with the mjhanna solution for now but looks like It needs to have all the records opened and closed to work

Luckily I don't need (so far) to do it in all the DB... looks like new entries has an ID making them sorted the way I want (chronologically)
Makes sens ?