View Issue Details

IDProjectCategoryView StatusLast Update
0018111MediaMonkey 5Otherpublic2022-11-21 13:08
ReporterLudek Assigned To 
PriorityhighSeverityminorReproducibilityhave not tried
Status newResolutionopen 
Product Version5.0 
Target Version5.2 
Summary0018111: Custom fields numeric support
DescriptionCurrently 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
TagsNo tags attached.
Fixed in build

Relationships

related to 0014365 closedmichal Support more Custom fields 
related to 0019250 feedbackpeke Custom Fields: Add Setting to treat Custom Fields as numerical 

Activities

Ludek

2022-11-15 13:38

developer   ~0070312

For me custom fields are sorted correctly (1 -- 10) on 5.0.4.2684:
image.png (21,448 bytes)   
image.png (21,448 bytes)   

michal

2022-11-15 18:53

developer   ~0070320

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.

barrym

2022-11-16 00:43

reporter   ~0070338

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

barrym

2022-11-21 13:08

reporter   ~0070409

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