Wednesday, May 1, 2019

Resolve Item Saved Search with Custom Field Error When Exporting to CSV

If you encounter an error when exporting an item saved search using a custom field and formula (text) under the Results tab, this may be due to the fact that the custom field is a free-form text but is being treated as a number.

See example below:

Custom Field = custfield (name composed of numbers and characters)
-- Type = Free form text
-- Store Value = F
-- Formula = True

1. Create an item search, by navigating to Lists > Search > Saved searches > New > select Item.
2. At the Results tab, add Formula (text)

CASE WHEN ({itemid} LIKE '%-13-%') AND {custfield}>3 THEN 'Description' ELSE '' end

3. Run the search.
4. Click Export-CSV

This will generate an error since {custfield} is a free from text but uses a condition of >3 and confuses the system since 1) the custom field type is a text but the formula treats it as a number 2) the values of the field is a combination of numbers and characters

To correct the formula, add "TO_NUMBER" so that the expression >3 will work as done below ---


Correct Formula:
CASE WHEN ({itemid} LIKE '%-13-%') AND TO_NUMBER({custfield})>3 THEN 'Description' ELSE '' end

No comments:

Post a Comment