AnsweredAssumed Answered

Sorting a portal based on a related field

Question asked by AlastairMcInnes on Mar 28, 2018
Latest reply on Mar 28, 2018 by AlastairMcInnes


I have a database of book details. Each book can exist in up to 6 formats (hardback, paperback, epub, pdf, prc, audiobook. Each format has its own record in the database and I have a portal on the layout showing what formats are available.



Each field on the portal has a button setup to take you to the relevant book record.

The portal is based on a second table occurrence of the Books table, called Books_Related. There is a field in the table called TitleID which is the same for all the related editions. The relationship is then defined as Books::TitleID = Books_Related::TitleID.


To make it more obvious which is the current book, I added conditional formatting to the portal. Using the Formula:


Books::ItemID = Books_Related::ItemID


The ISBN and format fields are set to Bold. Each book record has an ItemID as well as its TitleID. The ItemID is unique and is assigned as a serial number. As you can see, the formatting works fine.


I was then asked to sort the portal so that the active format is at the top. To do this, I defined a new field in the Books table called RelatedEditionsSort and made it a calculation field with the exact same calculation as the conditional formatting. I.e.:


Books::ItemID = Books_Related::ItemID


The calculation result is a number and, for testing, I added it to the portal, as shown above. Also for testing, I added the ItemIDs from the related records to the portal rows.


The problem is that this doesn't seem to work. In the picture, you can see that it's highlighted the prc edition so presumably the Books::ItemID is the same as Books_Related::ItemID, but the calculation field result is still 1 for the hardback and 0 for the prc (and the others). In turn, the sorting is putting the hardback at the top.


I also tried sorting the Books -> Books_Related relationship but that didn't make any difference.


Could someone point out what I'm doing wrong here? I'm sure it's probably really obvious and I'm just not seeing it.


Or, alternatively, I'm open to other suggestions as to how to sort the portal - to be clear, what I want in the example above would be to have the prc edition at the top of the list and the others in any order.


Thanks in advance