Setting Single Select Type by Formula

I have a table with a number field between 1-100, I want to auto fill a single select field between Low/Medium/High using a formula when the number is 1-50/51-75/76-100.

Using a formula to set a single select field requires a special type that doesn’t seem to be available. I have tried to use field(‘my select’)=‘Medium’ or field(‘my select’)=5030 based on the select id from the API docs. Both says the text or number type is not accepted. The API is capable of matching number and text but the is there a way to do this in a formula?

A similar topic did not have an answer, it converted single select to text but not the other way around: Changing Single select value


You need to convert the single select field to a text so that you can compare it with another text. For example:

if(totext(field('Single select')) = 'My value','Yes','No')

Thanks for the reply, I understand converting single select to text for comparison. I want my formula to do this:

if(field('Score')<51, field('Single select')='Low', if(field('Score')>75, field('Single select')='High', field('Single select')='Medium'))

But I am finding that trying to ‘set’ the single select field with text will fail with a type error. Same if I try a number. It is looking for a single select type that I can’t enumerate or provide back.

It is not possible to set the the value of a field using a formula. A formula can only calculate the it’s own value.

So actually, you do not need a single select field to indicate if the score is low, medium or high. It is the result of the formula.

if(field('Score')<51, 'Low', if(field('Score')>75, 'High', 'Medium'))

If you want to have the colours as a immediate visual indication of the score, you can set the color of the left border of background of each row: