Search bar not searching by label

[Austin]

I have a drop down that is a reference to codes. When I search in the list, in some locations, it will
search by the label and in other places it is searching by the actual KEY from the referenced table. The places where it is searchable by the key I am using a combined list of references from a table with references to generate a list based on users and their departments.
Below I have listed the valid if formula for the drop down that is searching by key instead of label. I am assuming since it’s not a direct reference to that table it is the cause of the issue.
Would using an IN formula to pull my reference straight from the code table instead of splitting out the keys be a solution to this issue?

IF(
USERSETTINGS(“Current_User_ACCT_GROUP”)=4,
SELECT(GL_ACCOUNTS[GL_ACCT_KEY],True),
SORT(
SPLIT(
TEXT(
SELECT(
USER_GL_ACCT_LINK[GL_ACCT_KEYS],
[USER_ID]=USERSETTINGS(“Current_User_ID”)
)
),
“,”
)
+
SPLIT(
TEXT(
SELECT(
USER_GL_ACCT_LINK[GL_ACCT_KEYS],
[USER_ID]=USERSETTINGS(“Current_User_ACCT_GROUP”)
)
),
“,”
)
+
SPLIT(
SELECT(GL_ACCOUNTS[GL_ACCT_KEY], [GROUP]=4),
“,”
)
)
)

[Steve]

Before even looking at your question, I can see your expression has some potential problems:

1. If you want to convert a non-Text value to Text for use within an expression, the TEXT() function may not be the best choice: TEXT() may reformat its argument, such as reformatting dates and times, adding thousands separators to numbers, and changing the decimal indicator. To simply make a non-Text value into a Text value without reformatting, use CONCATENATE() instead of TEXT().

2. The result of a SELECT() statement is a list. In several places within your expression, you wrap SELECT() in SPLIT(TEXT(...), ","), which forces the list produced by SELECT() to a single Text value, then splits that value into parts. This seems redundant and unnecessary unless the column values gathered by the SELECT() expressions (GL_ACCT_KEYS and GL_ACCT_KEY) themselves contain lists (are of type EnumList or List). In which case, the conversion to Text (preferably using CONCATENATE()) and re-splitting is correct and proper.

3. Unless you’ve specifically changed the list element separator in the column configuration, the separator is space-comma-space. Your SPLIT() expressions uses just comma, which may result in extraneous extra spaces in the split items. Consider changing the separator used with SPLIT().

4. SELECT(GL_ACCOUNTS[GL_ACCT_KEY],True) could be more simply written as just GL_ACCOUNTS[GL_ACCT_KEY], without the SELECT() wrapper. The two expressions are equivalent.

5. SPLIT(SELECT(GL_ACCOUNTS[GL_ACCT_KEY], [GROUP]=4), “,”) shouldn’t need the SPLIT() wrapper at all if the expression in (4) doesn’t need it.

6. If the entire list produced by adding the three SELECT() expressions together may contain duplicates you want to remove, you can wrap the entire expression in UNIQUE() or subtract an empty list (which removes duplicates as a side-effect).

Below is your expression with my suggestions from above (with SPLIT() expressions intact on the assumption the columns returned themselves contain lists):

IF(
  (USERSETTINGS("Current_User_ACCT_GROUP") = 4),
  GL_ACCOUNTS[GL_ACCT_KEY],
  SORT(
    SPLIT(
      CONCATENATE(
        SELECT(
          USER_GL_ACCT_LINK[GL_ACCT_KEYS],
          ([USER_ID] = USERSETTINGS("Current_User_ID"))
        )
      ),
      " , "
    )
    +
    SPLIT(
      CONCATENATE(
        SELECT(
          USER_GL_ACCT_LINK[GL_ACCT_KEYS],
          ([USER_ID] = USERSETTINGS("Current_User_ACCT_GROUP"))
        )
      ),
      " , "
    )
    +
    SELECT(
      GL_ACCOUNTS[GL_ACCT_KEY],
      ([GROUP] = 4)
    )
    -
    LIST()
  )
)

See also:

help.appsheet.com

CONCATENATE()

Merged text from parts

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

TEXT()

Format as text

help.appsheet.com

UNIQUE()

Unique items in list

[Austin]

So the USER_GL_ACCT_LINK[GL_ACCT_KEYS] is a ENUMLIST column. That is the reasoning behind the SPLIT being necessary. The SELECTs also should only return a single entry as each User_ID and User_ACCT_GROUP has a USER_GL_ACCT_LINK entry. With this in mind would an ANY in place of your CONCATENATES eliminate the need for the SPLIT? When I didn’t have the splits originally it was just creating what I now believe was a LIST of LIST instead of combining the LIST. For some reason it also didn’t have any duplicates without any handling of duplicates explicitly, I believe this has to do with being a REF column but not sure.

The issue is that in my drop down it is searching by the KEY instead of searching by the LABEL. Specifically the first 2 select statements search by the KEY and the the last one searches by the label.

Sorry I wrote this response yesterday and forgot to post it.

[Steve]

Austin_Lambeth:

So the USER_GL_ACCT_LINK[GL_ACCT_KEYS] is a ENUMLIST column. That is the reasoning behind the SPLIT being necessary.

I agree.

Austin_Lambeth:

The SELECTs also should only return a single entry as each User_ID and User_ACCT_GROUP has a USER_GL_ACCT_LINK entry. With this in mind would an ANY in place of your CONCATENATES eliminate the need for the SPLIT?

No. Even though the SELECT() expressions might be expected to produce a single result each, each result would itself be an EnumList type. It’s best to handle the result by its type rather than by its expected value to protect against changes in the future. We don’t want to use ANY() because we want the result as a list (even if a list of only one item) so that list addition works.

Austin_Lambeth:

The issue is that in my drop down it is searching by the KEY instead of searching by the LABEL. Specifically the first 2 select statements search by the KEY and the the last one searches by the label.

I’m still missing something. Are you saying the GL_ACCT_KEY column of the GL_ACCOUNTS table is the label column for that table, but not also its key column?

[Austin]

Yes, the KEY for GL_ACCOUNTS is GL_ACCT_KEY but the label is a combined virtual column of the gl code and the gl description to allow users to find either the code or the name when selecting.
Below I have images to illustrate what I mean. The first 2 pictures are where it’s searching by KEY and the third is where it’s searching by LABEL

[Steve]

Okay, thanks for clarifying.

I have to admit, I have no explanation for the behavior of the search bar demonstrated by your screenshots. That looks like a bug to me. I would suggest you contact support@appsheet.com directly for further help.

Sorry!

[Austin]

No problem. I don’t think it’s a very common way to handle creating a drop down.

[Steve]

Now that I’ve read your question, I’m confused.

Austin_Lambeth:

I am assuming since it’s not a direct reference to that table it is the cause of the issue.

What is “the issue”?

[Austin · Accepted solution]

For completeness sake, I feel the need to post the solution and a problem found when trying the suggested formula. By using a IN() to filter a SELECT from GL_ACCOUNTS, which is the table that actually contains the label, the search used the label. An issue I ran into when trying Steve’s formula was that whenever I clicked the Edit button in the top right my app would go blank. No cancel or save would appear. This issue went away when I surrounded the final SELECT in my list in a SPLIT. I believe that the combination of SPLIT generated LIST and SELECT generated LIST cause this issue. Further more, when I used the phone preview, which has the column that contains this formula cut off, the issue was not present.
This has already been reported to support.