How to use order by with constraints

Ninox Developer
vermau81
2 days ago
9replies
Answered

Hi,

Say, I have two tables (T1 and T2) table T1 has a reference field Ref1 (from table T2). I have successfully been able to implement constraints in the reference field to only show selective records, however I am not able to make it out how to use "order by" inside the constraint. I want the filtered records (from table T2) to be sorted by Name and shown on the reference field on the T1 form.

Database Architecture

- Sviluppatore Ninox
- Fabio
- 2 days ago
- Reported - view
Hello Vermaji,

The order by clause cannot be used directly in a constraint for a reference field for the purpose you want to achieve because the constraint is only used to filter the records that should be displayed in the reference field's selection list.

So, order by does not allow defining a display order. Sorting must be done after applying the constraint!

Once the desired records are displayed in the reference field selection window, simply click on the column you want to sort by (e.g., Name) and choose ascending or descending order directly from the interface. This way, the constraint filters the records, and the user has full control over the sorting without the need for additional code.

Just make sure that when setting up the sorting, you are in admin mode (wrench icon enabled).

Fabio
- Ninox Developer
  
  vermau81
  
  2 days ago
  
  Reported - view
  Thank you for the advice. That worked, however I am thinking that this way the control is in the hands of the end user to sort the way they want. This is perfectly okay, but is there a way that we can use the sort function in the script with the constraint to sort on a particular field initially (say Date of Purchase etc) and then later on the end user can sort however he feels like..
- Alan_Cooke
  
  yesterday
  
  Reported - view
  Unless I am mistaken and that could eaily be the case, if you switch edit mode on when you sort the desired field you want sorted and thenclose the list switch off edit mode it saves the change. I did this a few times to pick colums that I wanted next time I needed to selct a linked record. The only way I could force the chosen columns was to switch to edit, open the list to link, arrange colums etc, select the record, switch off edit.
  
  Was a while back so maybe later versions don't have this anymore.
- Sviluppatore Ninox
- Fabio
- yesterday
- Reported - view
Hi.

is correct! In edit mode, you can set the default sorting and column arrangement for the reference field selection window, and it should be saved for future use.

After that, when the user opens the selection window, they will see the default sorting (the one set while in admin mode). They can temporarily change it in that view, but upon the next access, the sorting will reset to the one set by the admin.
- Alan_Cooke
  
  yesterday
  
  Reported - view
  Discovered this by accident because I could not figure out that when I revisited the table when selecting a record sometimes the layout I chose did stick and other times not. Ping! I was in Edit Mode.
- Sviluppatore Ninox
  
  Fabio
  
  yesterday
  
  Reported - view
- Ninox Developer
- vermau81
- yesterday
- Reported - view
and I understand what you mean and yes it did save the changes I made in admin mode. But I want to know that is there a way we can sort the records through script dynamically instead of doing it manually using the admin mode ??
- John_Halls
  
  yesterday
  
  Reported - view
  No, unfortunately, the records can't be sorted through a script. If you incluse an "order by " in the script it is ignored. Relationships, Views and the record picker all have to be sorted via the show / hide columns dialogue box.
- Ninox Developer
- vermau81
- yesterday
- Reported - view
Thank you all for helping me out.

Welcome to the Ninox community

How to use order by with constraints

9 replies

Content aside

Categories

Overview