The problem is I have a value list that is based on 2 fields from another table, the first field is Order and the second is the Description, but it only populates with the first field which is a number and doesn't make sense. We need the description. I've had this problem before and worked around it, and I wish there was a simple way to solve this. So I made a calculation that concatenates the 2 fields for the value list, and the script trigger sets the field with the description without the number included (the script trigger does other things as well). But I realize not all of the value list is sorted correctly because it's a text field. Maybe a new calculation?

Sorry for being long winded,  any ideas on how to resolve this issue would be appreciated.

Caroline

9 minutes ago, Answers said:

I have a value list that is based on 2 fields from another table, the first field is Order and the second is the Description, but it only populates with the first field which is a number and doesn't make sense. We need the description

I don't think you do (need the description). The description is available from the original record via the relationship - assuming the purpose of selecting the Order is to establish a relationship. More details would be useful but as it stands, it seems you are only creating problems by trying to solve a non-existing one.

 

5 minutes ago, comment said:

I don't think you do (need the description). The description is available from the original record via the relationship - assuming the purpose of selecting the Order is to establish a relationship. More details would be useful but as it stands, it seems you are only creating problems by trying to solve a non-existing one.

Maybe I didn't explain that correctly. The purpose of the value list is to choose a project type for the estimate, which they want displayed in specific order (not alphabetical) based on numerical order they have determined. A simple value list.

10 minutes ago, Answers said:

The purpose of the value list is to choose a project type for the estimate, which they want displayed in specific order (not alphabetical) based on numerical order they have determined.

Okay, so why not simply select the order (number) and populate a field in the estimates table (also a number) with the selected value? If they want to select by the description, then show them the description as the second field in the value list. If they want to continue to see the description, then show them the description from the related record. Or format the target field as a pop-up menu. Either way, the target field will be a Number field, will contain only the order number and will sort as intended.

 

34 minutes ago, comment said:

Okay, so why not simply select the order (number) and populate a field in the estimates table (also a number) with the selected value? If they want to select by the description, then show them the description as the second field in the value list. If they want to continue to see the description, then show them the description from the related record. Or format the target field as a pop-up menu. Either way, the target field will be a Number field, will contain only the order number and will sort as intended.

That would be the easy way and elsewhere on the layout I use the method you suggested. But they are short on space on the layout.So I was trying to accomplish this with one field.

If they are short on space, you can stack the two fields so that the related description field covers the local order field (make sure the related field is non-enterable). Or - as I already suggested - use a pop-up menu.

 

In that case why not use a second window or a card window to show an actual list of records to choose from.  That way you can show all the data you want without having to construct a semi-complex value list...

13 minutes ago, comment said:

If they are short on space, you can stack the two fields so that the related description field covers the local order field (make sure the related field is non-enterable). Or - as I already suggested - use a pop-up menu.

 

That is a good idea!

26 minutes ago, Wim Decorte said:

In that case why not use a second window or a card window to show an actual list of records to choose from.  That way you can show all the data you want without having to construct a semi-complex value list...

That is a good idea as well and I use that method on another "list". I Filemaker (Claris) would change the way the value list work with the sorting function.