Value List Sort

[mmcconnell]

From what I am reading, this may be impossible or at least improbable.

I have a table of status made up of the following: status_ID;status_active;status_sort;status_text

It is related to a contacts table via the status_ID in a 1 to 1 relationship (i.e. each contact can have 1 status).

I am storing the status_ID value in the Contact table.

My question:

I would like to be able to keep a user from selecting an inactive status from a popup list and I would like the values to be sorted by the status_sort values.

I have previously programmed in MS Access, and creating a list this way is somewhat trivial using a query to sort values.

FMP doesn't seem to allow for a Value List to be created by a script or calculation.

I tried using a filtered portal sorting by status_sort and filtering by status_active, but it did not function correctly. Once an inactive selection is made, the user can no longer select anything from the value list. Additionally, the values are sorted by the text, not the status_sort (see attached file).

Is there any way to pull this off?

Thank you.

status_sort.zip

[IT_User]

From what I am reading, this may be impossible or at least improbable.

I have a table of status made up of the following: status_ID;status_active;status_sort;status_text

It is related to a contacts table via the status_ID in a 1 to 1 relationship (i.e. each contact can have 1 status).

I am storing the status_ID value in the Contact table.

My question:

I would like to be able to keep a user from selecting an inactive status from a popup list and I would like the values to be sorted by the status_sort values.

I have previously programmed in MS Access, and creating a list this way is somewhat trivial using a query to sort values.

FMP doesn't seem to allow for a Value List to be created by a script or calculation.

I tried using a filtered portal sorting by status_sort and filtering by status_active, but it did not function correctly. Once an inactive selection is made, the user can no longer select anything from the value list. Additionally, the values are sorted by the text, not the status_sort (see attached file).

Is there any way to pull this off?

Thank you.

I'm not sure exactly how to sort the value list based on the sort field. I did however filter out inactive statuses. The database is attached. I added another field to the status table and another table occurrence. Also, an opening script.

status_sort.fp7.zip

[mmcconnell]

I did however filter out inactive statuses. The database is attached. I added another field to the status table and another table occurrence. Also, an opening script.

Certainly a creative solution.

I'm not sure I see the purpose of the opening script. The global field value will not change and will not be displayed to the user at any point. Also, I have more than one value list that needs this functionality in the real database, so it would be nice to make this a truly global solution.

[IT_User]

Certainly a creative solution.

I'm not sure I see the purpose of the opening script. The global field value will not change and will not be displayed to the user at any point.

The data in global fields gets deleted whenever the file is closed, hence it needs reset every time the database opens.

Also, I have more than one value list that needs this functionality in the real database, so it would be nice to make this a truly global solution.

Hopefully this solution helps out some things.

[comment]

The data in global fields gets deleted whenever the file is closed

Not really. A global field is reset to its default value - i.e. the value entered when the file was last opened in single-user mode. You could have also used an unstored calculation field = 1.

Here's yet another way to do the same thing:

status_sort2.zip