Conditional Value list Second field trouble

[mpau]

I have a conditional value list set up to show only values for active accounts. The value list is defined by the calculation field cActiveAccountID = If (CustomerStatus = "Active" ; CustomerID)

 

The value list shows values both from the CustomerID field and from a second field: CustomerName 

 

The value list works perfectly if I sort by the first field.

 

However, I would like both to sort by and display only the CustomerName. Meaning, I would like to sort by the second field and display only the second field. If I select either of these options the value list now returns all customer records, for both active and inactive accounts. 

 

Has anyone else run into this issue? Any solutions? I feel certain I'm missing something small.

 

I've attached a demo file of the problem here.

 

Thanks for your help

ActiveAccount.fmp12.zip

[Lee Smith]

Are your example files all the same thing?

[Rick Whitelaw]

I believe if you choose to display only the second field you have no option but to sort by the second field. As long as your value list is still based on the Customer ID I fail to see that any of this would change which records are returned/displayed.

RW.

[mpau]

Apologies for the duplicate files. I've edited out the redundancies. 

 

Responding to RW, I am happy to sort by the second field. In fact, I'd prefer it. However, by selecting that option the value list populates with all of the values from the second field and not the filtered values established by the condition. So I get all customer records, rather than only the "active" records.

[Rick Whitelaw]

I' not at a computer at the moment. I assume your field "cActiveAccountID" is unstored. This may be your problem. If it can't be indexed (and I can't view your file now) I don't think this can work. So, the first field on which your value list is based is cActiveAccountID, correct?

[mpau]

I' not at a computer at the moment. I assume your field "cActiveAccountID" is unstored. This may be your problem. If it can't be indexed (and I can't view your file now) I don't think this can work. So, the first field on which your value list is based is cActiveAccountID, correct?

 

 

The calculation field is stored. And the value list works as long as it is sorting by the first field. The problem is that if I choose to sort by the second field, the value list no longer filters but rather now contains all values for the 2nd field.

 

If you have a moment to check out my file, there's a working model there of the issue.

 

The question is can a conditional value list work if it is sorting by or displaying only the 2nd field? If so, what am I doing wrong? Or by their nature, do conditional value lists only function with first field values? 

[Vaughan]

Something about displaying the second value in the value this: this second value needs to be unique. So if you're displaying a list of contacts and there are some people with the same name (but they may have unique contact ids) the value list will only display the name once, and the first contact with that name will be selected.

 

I solve this by using a calculated field in contacts that appends the contact id to the persons full name to ensure it is unique: for example "Smith, John (23)" where 23 is the contact id.

[mpau]

Something about displaying the second value in the value this: this second value needs to be unique. So if you're displaying a list of contacts and there are some people with the same name (but they may have unique contact ids) the value list will only display the name once, and the first contact with that name will be selected.

 

I solve this by using a calculated field in contacts that appends the contact id to the persons full name to ensure it is unique: for example "Smith, John (23)" where 23 is the contact id.

 

The second value (i.e. names) are unique. Any other thoughts? Perhaps you can have a look at the file and let me know your proposed solution.

[Rick Whitelaw]

cActiveAccountID does not refer to any field in a related table then? And it IS the first value? The value list is based on records containing this field?

[Vaughan]

I have a conditional value list set up to show only values for active accounts. The value list is defined by the calculation field cActiveAccountID = If (CustomerStatus = "Active" ; CustomerID)

 

 

You also need to make a cActiveCustomerName field using a similar calculation, so it is empty is not active. Use this as the second field in the value list.

[mpau]

You also need to make a cActiveCustomerName field using a similar calculation, so it is empty is not active. Use this as the second field in the value list.

 

 

I tried that solution and the value list field is now returning a "?"

 

See the attached file

ActiveAccount.fmp12.zip

[mpau]

Vaughan's solution worked. My error was that I set the cActiveCustomerName to return a number and not text. And that I continued to use the cActiveAccountID for the first field of the value list. For anyone who would like it, I have attached the working demo. 

 

It's important to note that with Vaughan's solution, the cActiveAccountID calculation is no longer needed. If you try to run the value list using this in the first field it won't work. For it to work, you need to have the first field set to CustomerID and the second field set to cActiveCustomerName. Sort by 2nd field and display 2nd field.

 

Thank you, Vaughan!

ActiveAccountWorking.fmp12.zip