in(“adkufhghgsakhskdgh”,[related trainees][current status])
[current status] is an enum column in the trainees table.
The above code produces “TRUE” results. Any garbage string I put in results in “TRUE”. The only time I get a “FALSE” for this expression is if the string contains exactly one of the permitted selections from the “ENUM” column setup that is not in the list produced.
This is problematic because some trainee records are imported from another system and they have various pre-filled values in the column, but I limit my users to adding only certain pre-defined strings. I don’t think this is intentional IN() functionality and is a bug. Help?
Hi @Patrick_Paul
Can you try:
IN(
“adkufhghgsakhskdgh”,
SPLIT(
CONCATENATE([related trainees][current status]),
" , "
)
)
Also, make sure of this settings that may impact results with “false trues”:
I have found that the usage of “[related trainees][current status]” does not always provide the expected results. I have not investigated to understand why but I think it has to do with contextual ambiguity not being resolved (e.g. cannot use [_THISROW] with the above context).
When I encounter issues, I usually just replace the above expression with something like:
SELECT(trainees[current status], [parent ref column] = [_THISROW].[key column])
NOTE: replace [parent ref column] with the correct name of the ref column to the parent row
replace [key column] with the name of column that is the table key
I hope this helps!
@WillowMobileSys I believe you are correct. I was planning on trying to replace it with a SELECT() when I have time to try to resolve the issue. A hint that “context” is the problem is that if I put the code into a virtual column, it works correctly until I hit Save, at which point it fails. I have encountered that same kind of behavior with [related XXXX][Some Column] elsewhere, but the weird thing with this situation is that this code functioned correctly up until a few weeks ago (the referenced report has existed literally for years), when it suddenly went off the rails as though Appsheet had updated or broken something on the ‘back end’.
This being the case, a ticket should be opened to AppSheet Support directly. Using “Related” columns is preferred due to performance efficiencies it provides.
Hi @Patrick_Paul
Did you see the results in your table for that Enum Ref column? I’ve run into this before—the list looks fine, but the table shows the Key instead of the actual value. If the formula hasn’t changed, maybe the Key itself was modified. It’s worth double-checking. Thanks!"
in the enum list it matches but the results in the table are different (produce key values)
Hi @Patrick_Paul
Can you try:
IN(
“adkufhghgsakhskdgh”,
SPLIT(
CONCATENATE([related trainees][current status]),
" , "
)
)
Just for everyone’s reference, this suggestion results in the expression evaluating to “No” in all instances, even if the search text is present, the opposite problem from what I was first experiencing. Testing this in the virtual column, it appears that CONCATENATE() strips out any values from the referenced enum column that are not permitted selectons, similar to what happens using IN(). Which means that one of the values I’m looking for literally disappears!
Update, Correction: When simply putting [Related XXXX][Ref column] the value in question that is not a permitted enum selection disappears after hitting SAVE. Up until that point, one can see it in the preview pane.
There appears to be some kind of bug here… One change we made just prior to this starting to fail is that we removed one of the possible choices from the enum column since it is now updated externally… that was not the case in the past. It appears that any function that “pulls” the values out of the list of refs fails for any values that are not a part of the referenced enum column valid selections. Which is nuts.
As an experiment, I added the removed value back to the permitted list of enum values, and viola, it re-appears in the virtual test column results. So it appears that enum columns get filtered for only expected/permitted data when referenced…?!
I also attempted switching my “test” virtual column to using SELECT(). This also appears to strip out any values from the enum column that are not expected entries.
SELECT(Trainees[Current Status],[Mentor]=[_THISROW].[Employee ID])
It works in the preview pane as expected until I hit SAVE, just like the other formulas, then any “excluded” enum values disappear! Has this always worked this way? I can’t be certain, but I don’t think so.
Interestingly, I have other SELECT() statements that check the value of the same enum column from within the Trainees table. So long as this occurs “within” the trainees table (not “jumping” there from another related table in any way), it appears to work fine, even when evaluating for “not-permitted” enum values…?
Got with support, they basically don’t see this as an issue and expect me to essentially “code around” the problem. Which is what I will do.
In final summary, the issue is that any filtering/selection of records from a table that depends on an enum column will behave in an unpredictable fashion if some values in the enum column are not “expected” by Appsheet. This issue only manifests if the filtering/selection involves a foreign key.
