View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0018111||MediaMonkey 5||Other||public||2021-07-05 11:46||2022-11-21 13:08|
|Priority||high||Severity||minor||Reproducibility||have not tried|
|Summary||0018111: Custom fields numeric support|
|Description||Currently Custom fields supports any text value, but if user wants to use the CustomX field for a numeric value and use a query like 'Custom1 < 8' then it is not possible.|
Also using a custom or grouping field for a Sinfonie results in sorting like:
Sinfonie Nr. 1
Sinfonie Nr. 10
Sinfonie Nr. 2
Sinfonie Nr. 3 and so on
Reported at: https://www.mediamonkey.com/forum/viewtopic.php?f=30&t=99058
|Tags||No tags attached.|
|Fixed in build|
||For me custom fields are sorted correctly (1 -- 10) on 22.214.171.12484:|
Numeric-aware sorting of custom fields was added few years ago, issue 0014365.
This issue is about filtering criteria, where we have only string related operators. I am not sure, whether this is fixable, custom fields are always strings, by their nature, media formats store them always as strings.
Assigning to Ludek as author of filtering, please check, if this is even possible. Note that customX fields always can contain non-numeric value.
a few observations regarding michal's issues:
1. Operators like > , <, <=, >= are allowable with strings
2. They would be useful in the Customx columns, for Collection and Autoplay criteria, even with the issue created by "9" being less than "10" ... the caveat could be that string "numbers" need to be 0 padded when used with numeric operators
3. External applications can store numeric proper values in MM customx columns .. MM5 displays these OK, so there is no caveat for this Use Case
4. Alternatively MM could use a sql clause like "(customx as interger)" when filtering customx columns using a numeric operator ... this would be ok because sqlite does not raise an exception where customx contains a non-numeric value ... it seems to return the value 0 in these cases
Thinking more about Michal's questions:
I think the most functional fix for this issue would be to add some new customIntegerx columns to the database.
ie. columns with integer affinity ... with validation in the File Properties panel, to accept only numbers, maybe validated with a spinner widget.
These columns could be then be filtered with arithmetic operators without caveat, and would sort naturally without workaround.
There should also be some customdatex columns.
They could have a REAL affinity, and a calendar validation widget in the File Properties panel.
They would display as string dates in the File Properties panel.
That would enable you to offer the "days ago" and "hours ago" operators when filtering for auto playlists or in Collection criteria.
There should be instructions warning that any external application should use (cast x as integer) or (cast y as real) in their INSERT or Update queries to these columns.
When the tags are written to Media Files they could be converted to strings.
When they are read from Media Files they would be cast as per their column's affinity.
The other way is what I think Peke was suggesting in another issue here. ie. a configuration option for each of the custom columns, which effectively would give the choice of text|numeric|date validation for each of the customx columns. If that idea is be considered, it would be good if each customx column also had the option to toggle off FTS off coverage. Some data is just bloat in the FTS index, and FTS coverage makes it difficult to update these columns from external application, since you don't publish a 64 bit version of your tokeniser.
I hope that you do *something* about this issue ... even the approach discussed in my post above would be better than the current situation. .. ... ie. needing to create a SQL update trigger to cast an int or real version of selected customx columns, to some "spare" numeric column that is available when setting playlist or collection criteria, eg EpisodeNumber or SeasonNumber or BPM