Pick list sort order (2nd)

[Mathieu Masseboeuf]

Hi,

Not sure why – I couldn’t comment on the previous topic on that matter :

Pick List sort order

To sup um :
– I have a long pick list, with ordered items according to their value/importance
– That order doesn’t match alphabetical ordering
– I’d like to have that order used for sorting in the list view

Anyway, the proposed solution is … not a solution – I have a very long list of sorted items (about 60 items) – putting numbers in front of them is not an option.
That feature would really be appreciated (I just migrated from Bento, where I was using it)

Thanks !

[Brendan]

Hi Mathieu,

I’ve been trying to figure out how Bento sorts their records by the order of the values in the pick list, but I cannot see how to do that. You can sort ascending or descending alphabetically. That’s all I can find.

Thanks,

Brendan

[Mathieu Masseboeuf]

Hi,

I suppose they are using the internal key from the key/value pair used to index/sort the display of the items from the list (no sure how this works internally though) – or a key/value pair from an external list to build the menu (and then use the key for sorting)

I did a quick search, maybe this helps : http://stackoverflow.com/questions/2405965/uipickerview-with-nsdictionary
(if not, tell me I’ll ask my developers how they would do it)

Thanks,
Mathieu

[Brendan]

But how do I tell Bento to sort records using the order of the values in the Pick List instead of alphabetically? I just don’t see an option for that.

[Mathieu Masseboeuf]

Well, you don’t tell him to sort that way instead of alphabetically : it only uses the pick list order for sorting.
Which would be OK with Tap Forms, as unlike in Bento, you have a button to sort items upward and downard inside the pick list (if you want to do so).

[Ron]

Is this still the case that field values cannot be sorted based on pick list order if the list is not alphabetized?

[Brendan]

Hi Ron,

That’s right. Tap Forms sorts records based on the First Sort Field values, not based on the order of values inside a Pick List.

Thanks,

Brendan

[Ron]

Brendan,

But if that First Sort Field is a Pick List field that is not in alpha order how would the field be sorted?

Sort Field is ‘Status’ = In Process; Not Started; Completed.

I would want all records that are ‘In Process’ listed ahead of the other Status’ and when ‘Completed’ that record would move to the bottom of the listing. I guess the only solution would be to preface each status with a number.

Ron

[Brendan]

Hi Ron,

That’s correct, prefixing the values with a number would work. Tap Forms just uses the database engine sorting routines which just sorting things in ascending and descending order either alphabetically, numerically, or chronologically (which is really just numerically).

Thanks,

Brendan

[Mathieu Masseboeuf]

Just in case, bumping that question here :)
There could be an option in the List of choice configuration.

Sort order : Alphabetically / Manual (and in that case you either sort alphabetically or using the sort key used to display them in the proper order)

[Brendan]

Well there is a button to sort the values right now on the pick list edit screen.

[Mathieu Masseboeuf]

That button lets you sort items in the sort list.
What I’m looking for is to sort that list manually, and then use that sort order when sorting ascending or desceding (instead of the alphabetical one)

[Brendan]

You can’t have sorting manual and ascending or descending at the same time. Just drag the values into the order you want them to appear in.

[Mathieu Masseboeuf]

I’m not sure I was clear in what I wanted.
I’ll post a screenshot :D

I have a list of values, which is sorted the way I want : the opened menu on the left. It’s used in the field “Dénomination”.
When sorting by “Dénomination” in my form, I’d like it to follow that order I chose, instead of an alphabetic order. Just as it is in the menu, or in the list configuration.

Hope it’s clearer that way.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

This is a bit of a hack but you can use a script field to do the ordering. Create a new script field and then you’ll need to put in something that looks like this:

var keyfield_id = 'fld-id';
var picklist_name = 'Picklist name';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

In the script above, edit the first two lines and change the field ID (the fld-id text) and the pick list name (the Picklist name) to be the ID of your Dénomination field and the name of the pick list it uses.

Here is an example I used where I put in the field ID and the pick list was called “Marketplace”:

var keyfield_id = 'fld-fa37906add2942c88bce3b500561c42d';
var picklist_name = 'Marketplace';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

That will give you an ID starting from zero I believe in order of the pick list that you can then use as a sort field. It’s not as elegant as having the ability for the text field to use the pick list ordering but it’s something you can do right now.

[Brendan]

Oh, you want your records to be sorted by the manually ordered list of values you put in to a Pick List. Sorry for my misunderstanding.

When databases perform sort operations, they can only sort based upon the values in a column in the database. Either ascending or descending by whatever the value type is. If it’s a date value, then it’s chronologically. If it’s a number value then it’s numerically and if it’s text, then it’s alphabetically sorted.

Sam’s solution is great because it allows the database to sort in an orderly fashion, in this case, numerically.

I guess a built-in solution would probably work the same way that Sam’s does based on some configuration of the Sort Settings.

[Mathieu Masseboeuf]

I don’t know how the internals work, but I suppose you already have an order key stored with those lists in order to have that list displayed in the desired order (and that would “just” mean an additional join – well, the “just” is not that easy I suppose it’s not stored the same way …).

That’s why I thought you had the information internally – even more now that you can link pick lists to another table (but now that I think about it, in that second case the order can be manual, or sorted according to the form sorting rules… well… )

As I said in my initial post, putting numbers in front of those field is not an option – it wouldn’ t be readeable at all.

Hum, but you gave me an idea, maybe I can play with invisible space characters :D -> https://stackoverflow.com/questions/17978720/invisible-characters-ascii

I’ll try that out and post the result here :)

Thanks,
Mathieu

[Brendan]

Did you try Sam’s solution yet? I’d give it a try.

[Mathieu Masseboeuf]

Oh, indeed I didn’t noticed that answer !
(just tried with the ascii stuff, but I would have to refill everything, or script a replacement)

Indeed, last time I checked TF didn’t had scripting, never used it … will have to check that out :P

[Mathieu Masseboeuf]

Well, that’s a perfect solution, thanks a lot @Sam Moffatt … & Brendan :D \o/

[Sam Moffatt]

I added this to my Script Manager so that if you want to set this up for a new script field it can look like this (once Script Manager is installed and update run):

document.getFormNamed("Script Manager").runScriptNamed("PicklistID");

PicklistID('fld-fa37906add2942c88bce3b500561c42d', 'Marketplace');

One minor change was to fix an issue where the pick list was returned at the end if nothing was found and that now is returning a null that should show up as an empty value for the field (it was showing up was something like [TFPicklist object] I think).

[Mathieu Masseboeuf]

Thanks again Sam ! :)

[Lane Robinson]

Just thought I’d provide some feedback on my finding this useful to sort records by month. A saved search shows me everything in a year. Being able to use the pick list I use to enter the month on the record is nice, and I’m sure I’ll use this other places too.

I modified the script from the Script Manager a little bit. I wrap the offset with Number() to convert it to a number. It’s text apparently. (I’ll learn this javascript yet.)

Thanks for the useful resource Sam.

(I’m not sure how to preserve the tabs in the code. I thought the code tag would do that.)

// NAME: PicklistID
// VERSION: 1.0

/**
 * PicklistID returns the offset index of a value from a pick list.
 *
 * This is useful for creating a surrogate field for sorting a field
 * that uses a single value pick list by the ordering of the pick list.
 *
 * Reference:
 * https://www.tapforms.com/forums/topic/pick-list-sort-order-2nd/#post-46660
 *
 * @param {string} keyfield_id - The internals Tap Forms field ID for the source field.
 * @param {string} picklist_name - The name of the pick list to use for ordering.
 *
 * @return {(number|null)}  The offset matching the value from the field or null if not matched.
 */
function PicklistID(keyfield_id, picklist_name) {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			// just because when I see it I want to see 1 for january
			return Number(offset) + 1;
		}
	}
	return null;
}

var month_id = 'fld-370f51968e3146dca058b82ef34b1592';
var list_from_picklists = 'Months';

PicklistID(month_id,list_from_picklists);

• This reply was modified 2 years ago by Lane Robinson.
• This reply was modified 2 years ago by Lane Robinson.

[Author]

Posts