Value List sort by related field

[Courtney]

I have a value list in my Timesheet table that includes all related values in the last 60 days, so that a user can use this as a quick reference of their recent usage instead of looking up codes every time.

 

What I'm trying to do now is sort the value list by a second field (Timesheet::Client_Project_dropdown), which is in itself a calculation of company name and project name fields from related tables.

As far as I can tell from error messages, I can't sort the value list by this second field because it can't be indexed, because the calculation is based on related fields.

I know I can make these fields text fields with auto-enter calculations, but they then won't be updated if a client name or project name changes.

What can I do to this second field to make it indexable so that the value list can sort by it?

[LaRetta]

Hi Courtney!

It would help if you provide a simple file showing the key relationships, fields and your value list.  This would save us time in assisting you with it.  :-)

[Courtney]

Thank you LaRetta. I created a simple example file that shows the key fields and relationships.

Example.fmp12

[LaRetta]

Hi Courtney,

Try it now.  Notice that I corrected your graph.  Your perspective was TIMESHEET but you had the child side set up with the global date.  That won't work.  I reversed the relationship and reversed your value list.

Does this give you what you want?

ExampleMOD.fmp12

[LaRetta]

8 hours ago, LaRetta said:

Your perspective was TIMESHEET but you had the child side set up with the global date.  That won't work. 

I was wrong.  From perspective of TIMESHEET, a relationship between two stored values (employee) and TIMESHEET::date  ≥ gDateFilter WORKS (global calculation).  It only identifies the first related child record but I wouldn't have thought the relationship valid at all.  I just assumed and changed the relationship and it worked but it wasn't necessary! Maybe I'm getting rusty!  ;-)

Courtney, I just changed value list in your Example to 'sort by second field (your unstored calculation) and it works as you wish (I think).  Did you have something different in your real file than this Example file?   Also please note that, although I was aware of some types of value lists working even with the "This value list will not work because it cannot be indexed." message ... I did not expect it to work in this situation.

I double-smacked myself!  Anyway, let us know if you still need help with it and thanks for opening my eyes a bit.  I sense Comment's influence here. 

Edited October 17, 2017 by LaRetta

[Courtney]

Thank you so much LaRetta! I was able to get this working from your example. I've just been so swamped since then that I didn't get back to say so.

Thanks again!

~Courtney