When Will Job Presets Get Fixed?

OL Connect Professional Designer
1

Hi OL,

In 21 June 2017 I raised this issue with a support ticket and this issue still persists. If I have a field with Number Of Copies like below…

1
2
3
5
7
10
15
20
50

and make a job preset to filter records with the following rule…

Number Of Copies >= 5, this ignores values 10, 15 and 20 because if I recall correctly it is SQL? Those values are seen as 1 and 2 instead.

When will this be fixed?

Regards,
S

2

This is a long standing issue, reported back in 2016, actually. It’s still open pending resolution.

As you say, the filter uses an SQL operation to compare the values and does so as a string, rather than a numerical value. Unfortunately, there’s currently no ETA on when it will be resolved.

In the meantime, for you or anyone else that may encounter this, the trick is to format your data as a string as well, such that the comparison works the way you desire it to.

So yes, a string based comparison will place 1, 2 , and 12 in this order [1,12,2]. However if the strings are padded with zeros, it will compare them as you’d expect. So 01,02,12 are properly ordered [01,02,12]

Therefore, to work around this, simply zero pad the field in the data mapper. This can be done via a JavaScript based data extraction like so:

('0000000000' + data.extract('COPIES',0)).slice(-4);

The above applies specifically to a CSV data file with a column name of ‘COPIES’. Just replace the data.extract with an appropriate extract for your data type.

Finally, in your Job Preset, just remember to compare against the padded value: COPIES >= “0005”

3

If you have control over the database, you can add a computed field to the table, or expression to the view. If it’s a simple true/false, you can have a case resolving to a boolean. Sample TSQL code for both:

WITH MyCte
AS (
SELECT Copies = 1

UNION ALL

SELECT Copies + 1
FROM MyCte
WHERE Copies < 40
)
SELECT Copies,
RIGHT(‘0000’ + CONVERT(VARCHAR(4), Copies), 4) AS CopiesPadded
FROM MyCte
OPTION (MAXRECURSION 0);

WITH MyCte
AS (
SELECT Copies = 1

UNION ALL

SELECT Copies + 1
FROM MyCte
WHERE Copies < 40
)
SELECT Copies,
CONVERT(BIT, CASE
WHEN Copies < 6
THEN 1
ELSE 0
END) AS LessThanSixCopies
FROM MyCte
OPTION (MAXRECURSION 0);

But yeah, ultimately OL should fix this.

4

Actually, in the upcoming 2019.1, we intend to update the functionality and user interface for Job Creation. It will now do a typed comparison on numeric and date fields, along with some other improvements.

1 Like
5

Hi Manuel,

Thanks for the update. Looking forward to this fix and what 2019.1 will bring!!

Regards,
S