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:
Any ideas with how to combine a select function and a some text into a drop down?
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
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
No the issue is stated right there in the error report from when you first put in the formula:
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.
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.
Consider making the N/A and No data used options a separate column.
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |