How can I sort a column based on it's display value?

Applications Forms
#1

Hi all,

I have a Multiple Records form. The form accesses a table with meaningless data, a SELECT Data Type is used on one of the fields to return a more meaningful value from another table. In this case it returns a name for what is an id.

For the initial sorting of the table (names in alphabetical order) I use ORDER BY in the SQL section;

(SELECT name FROM uk_d_bsc WHERE name_id = uk_rte_daily.today_id) ASC

Where uk_rte_daily is the table used in the application.

My question is this; If I try to sort the column it will only sort by the name id and not the displayed text, how can I click for it to sort by the displayed value and not the table held value?

#2

You need to join your table with the table that contain the name and use the name field instead of the ID

#3

Thanks jlboutin60, how do I do this in a form? Obviously in a grid I can just use a JOIN in the SQL statement.

#4

You can do it in a view or put this code in Order (SQL Settings)

(SELECT UK.name FROM uk_d_bsc UK WHERE UK.name_id = U.name_id) ASC

Put TableName for key and value in Variable for Table in SQL Settings

In the link that call your form set TableName as Fixed value = uk_d_bsc U