Jump to content
Courtney

Value List sort by related field

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.  :-)

Share this post


Link to post
Share on other sites

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

Example.fmp12

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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. :wink3:

Edited by LaRetta

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.