www.hkvforums.com

Hello good morning

I have a table which has a column that uses a foreign key, and I have it set up so it displays the text value from the foreign table.

I want the records to appear sorted from A to Z, but if I go to the table setup and sort the table using the column, it works but the sort works based on the ID number and not the text value.

Is there any way I can make this sort work with the text value of the foreign table?

thanks!

You can set up the [Order by] with the display field.
[Edit Tag] -> [Lookup Table] -> [Order By] = your display field.

Hello and thanks for your answer

I have been using this and works perfectly for ordering a dropdown menu, but what I want to sort is the main table view. This is done on the TABLE tab, SORT option

the problem is that in this part, there is no way to order the table using the display and not the actual value.

Enable the option "Allow sort/search" in [Edit Tag] -> [Lookup Table] of the lookup field then try again.

Hello,

I have a table 'inventory' with a foreign key 'id_product' and lookup table enabled in 'edit' tag (lookup to 'products' table, field 'product' - varchar - product name)

in 'Tables', i've selected 'id_product' for SORT this table (Ascending)

The sort order in list page is always based on 'id_product' value, not label value (product name).

I've enabled 'allow sort/search' in lookup table option with no avail.

Same as this post and post: http://www.hkvforums.com/viewtopic.php?f=4&t=42851

thanks,
Enrique

This is the built in Allow/Sort search feature and should work. Enable debug mode (Tools -> Advanced Settings -> Debug) and show the SQL. See if it gives any hints.

This should work as I did it all the time.
Moreover, if you examine debug SQL, they use EV__fieldname for alias, which then you can set sort order conditionally, such as:
Page_Render()
if (yourCondition) {
$this->setSessionOrderByList("EV__user_id ASC"); // will sort user_id field (using it's label) conditionally.
}

Hello,

Thanks, i've placed your code on 'Page_Load" event of list page and it works:

$this->setSessionOrderByList("EV__id_product ASC");

If i place the code on page render, i must refresh (F5) the page after load to see the order works.

The field 'id_product' (inventory table) is looked up to field 'id' of table 'products' in edit tag.

Allow sort / search is enabled.

In tables list, the sort field for the table 'Inventory' is 'id_product' ASC

Please see the Mysql Debug below.

Thanks,
Enrique

0.967494: (mysqli): SET NAMES 'utf8'. Error: (0)

1.159961: (mysqli): SELECT COUNT(*) FROM products ORDER BY product ASC. Error: (0)

1.367336: (mysqli): SELECT COUNT(*) FROM inventory. Error: (0)

1.552883: (mysqli): SELECT * FROM inventory ORDER BY quantity_downey ASC LIMIT 0, 20. Error: (0)

1.745743: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 49 ORDER BY product ASC. Error: (0)

1.930284: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 295 ORDER BY product ASC. Error: (0)

2.116236: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 293 ORDER BY product ASC. Error: (0)

2.299793: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 292 ORDER BY product ASC. Error: (0)

2.485180: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 286 ORDER BY product ASC. Error: (0)

2.669993: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 37 ORDER BY product ASC. Error: (0)

2.854577: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 35 ORDER BY product ASC. Error: (0)

3.039051: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 72 ORDER BY product ASC. Error: (0)

3.223175: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 46 ORDER BY product ASC. Error: (0)

3.414119: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 73 ORDER BY product ASC. Error: (0)

3.600094: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 294 ORDER BY product ASC. Error: (0)

3.786520: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 34 ORDER BY product ASC. Error: (0)

3.971751: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 47 ORDER BY product ASC. Error: (0)

4.155434: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 45 ORDER BY product ASC. Error: (0)

4.346919: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 48 ORDER BY product ASC. Error: (0)

4.535632: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 36 ORDER BY product ASC. Error: (0)

4.735929: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 71 ORDER BY product ASC. Error: (0)

4.921047: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 41 ORDER BY product ASC. Error: (0)

5.104573: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 289 ORDER BY product ASC. Error: (0)

5.288761: (mysqli): SELECT id AS lf, product AS df, '' AS df2, '' AS df3, '' AS df4 FROM products WHERE id = 285 ORDER BY product ASC. Error: (0)

So, did your solve your problem or not?
Because judging from this line,
1.552883: (mysqli): SELECT * FROM inventory ORDER BY quantity_downey ASC LIMIT 0, 20. Error: (0)
Your ListSQL() doesn't have the EV table it needs.

Hello,

As i says on my previous post, adding your line to 'page_load" solves the issue (It works) thanks for your help:

$this->setSessionOrderByList("EV__user_id ASC");

I will debug the table again. Notice that i've been using phpmaker for years now, and on every project i was having this issue. It is the first time i see the table list ordered with a lookup field on list page without clicking on column header.

Thanks,
Enrique

Its work in v2023 ?