Issues combining select functions and text

[ybyrnes]

Hi,

I am trying to generate some enum lists for selecting valid meterages based on input data within the table and within another table. I have had success using the following formula in the data validity section:

“SELECT(Build data[Chainage Actual],
AND(
([Chainage Actual] >= [_THISROW].[Start chainage]),
([Chainage Actual] <= [_THISROW].[End chainage]),
([Shift Type]= 3. Supervision)
)
)”

This obviously generates me a dropdown enumlist of valid chainage actual values and does not allow me to add any values that are invalid (even if “allow other values” is enable). However I want to have the ability to add a couple of standard text values N/A and no data used for example.

I have tried a couple of methods to resolve this but haven’t had any luck with the following:

• Using LIST(my formula, “N/A”) to suggested values- this doesn’t separate the formula values into a drop down
• Trying to use LIST in the data validity area

Any ideas with how to combine a select function and a some text into a drop down?

[MultiTech]

If you wish people to be able to add items to the list, move this formula from the valid if to the suggested values - they accomplish a very similar functionality, but with suggested values you can add additional values to the list whereas the valid if restricts values to only those values.

If you’re wanting to add values to the list from your formula, you can actually add and subtract lists together:

SELECT(Build data[Chainage Actual],
AND(
([Chainage Actual] >= [_THISROW].[Start chainage]),
([Chainage Actual] <= [_THISROW].[End chainage]),
([Shift Type]= “3. Supervision”)
)) + list(“N/A”, “No data used”)

So if your SELECT() formula would give you this list:

{1, 2, 3, 4, 5}

by adding the list addition at the end, the options available will be:

{1, 2, 3, 4, 5, “N/A”, “No data used”}

---

The problem you might find here is that if your original column type is a number and you add text, the text values won’t work in the field - you’ll need to change the whole column to text so it can accept the text values.

FYI

[ybyrnes]

Hi I have previously tried both of these options:

When I try and use my formula in the suggested values the following error occurs:

Column Name ‘Records’ in Schema ‘RSS_Schema’ of Column Type ‘Yes/No’ has invalid ‘Suggested Values’ of ‘=SELECT(Build Data[Chainage Actual], AND( ([Chainage Actual] >= [_THISROW].[Start chainage]), ([Chainage Actual] <= [_THISROW].[End chainage]), ([Shift Type]= 3. Inspection) ))’. The type of the Suggested Values does not match the column type.

When I try and add the +List(XXX) on to the data validity as you suggested I don’t get an error message and “N/A”, “No data used” appear in my drop downs but when I select them it returns a data not valid. My column type is Enumlist and my base type is text (would this be causing this issue?).

Thanks

[MultiTech]

No the issue is stated right there in the error report from when you first put in the formula:

ybyrnes:

The type of the Suggested Values does not match the column type.

You’re pulling in values from the [Chainage Actual] column, this means the values need to be of the same type and it appears that you might be importing a number into a text.

This is a common scenario when building apps, people want to include multiple different data types in options, but that means you must change all corresponding data (meaning, all the data that will go into this dropdown) to the same data type - which means you can lose the ability to do math and things on numbers previously used.

In cases where we need to preserve the data type (for math, logic, etc.) I’ll create a separate field to hold these other options.

[ybyrnes]

But the [Chainage Actual] column needs to be input into the select expression as a number/decimal since math operator is included in the select expression. I tried some work arounds by using the TEXT() function but have had no luck.

It isn’t really ideal to move the select function to a different column since I need to do a similar exercise for 10+ columns resulting in a clunky data structure (unless I could do this with virtual columns.

[Steve]

Consider making the N/A and No data used options a separate column.