Solved: Drop-down on the basis of start value

Sirfyaad · 06-12-2022 04:24 AM

Hi,

Can you tell what mistake I have done? Inside it, due to which the function is not working here. I have to select Drop-down on the basis of start value.

IFS(
  ISNOTBLANK(
    FILTER("ENUMLIST",
      STARTSWITH([UNIQUE KEYS],LEFT(COURIER NAME],5))
    )
  ),
  ANY(
   FILTER("ENUMLIST",
      STARTSWITH([UNIQUE KEYS],LEFT(COURIER NAME],5))
    )
  )
)

Note: This expression could impact performance.

IFS(
....This statement is false:
........(The list of values of column '_RowNumber'
............from rows of table 'ENUMLIST'
............where this condition is true: ((The value of column 'UNIQUE KEYS') starts with the text value (LEFT(
................"COURIER NAME]"
................5)))) is empty
....One randomly chosen value from this list (
............The list of values of column '_RowNumber'
............from rows of table 'ENUMLIST'
............where this condition is true: ((The value of column 'UNIQUE KEYS') starts with the text value (LEFT(
................"COURIER NAME]"
................5)))))

WillowMobileSys

"Unique Keys" column is defined as NUMBER. It needs to be TEXT. Change the column data type and try again.

View solution in original post

WillowMobileSys

You haven't specified where you are using this expression. I'll assume, based on your title, that you are trying to populate a list of dropdown values to pick from for your column. If so, 1) you do not need the IFS(). If there are no values returned, the list will be empty anyway. 2) You do not want to use the ANY() function at it will return ONLY the first value in the list. 3) You are missing a square-bracket for the column named [Courier Name].

based on my understanding, you need to only insert the expression as:

FILTER("ENUMLIST", STARTSWITH([UNIQUE KEYS],LEFT([COURIER NAME],5)))

Sirfyaad

FILTER("ENUMLIST", STARTSWITH([AWB NO],LEFT([COURIER NAME],5)))

Not working

WillowMobileSys

1) Syntax looks correct but you have a new column now. Describe this column - where it lives and what data it has.

2) You still haven't mentioned anything about WHERE you are using the expression. Context of usage may make a difference.

3) Explain what you mean by "not working".

Sirfyaad

Hi @WillowMobileSys @Joseph_Seddik

I want that if the start value in the AWB No column matches with the unique keys, then the courier name will be auto selected.

FORM

WillowMobileSys

Please describe the AWB No column - what table it is in and what the data values look like.

We need to know this information to be able to suggest an appropriate, precise expression to use.

Sirfyaad

I want courier name in awb no column. Based on start value.

my main table name is scan sheet.

Sirfyaad

Hi @WillowMobileSys

Joseph_Seddik

As @WillowMobileSys already told you, we don't understand your setup and you are not showing us information we could use to help you.

For example:

@WillowMobileSys asked you:

1) you have a new column now. Describe this column - where it lives and what data it has.

2) You still haven't mentioned anything about WHERE you are using the expression. Context of usage may make a difference.

3) Explain what you mean by "not working".

And you haven't answered any of these questions. But came back with some unclear comment, so @WillowMobileSys asked you:

Please describe the AWB No column - what table it is in and what the data values look like.

Again, you did not answer any of these questions. You just repeated again your earlier comment that did not help:

@Sirfyaad wrote:
I want courier name in awb no column. Based on start value.

then,

my main table name is scan sheet.

What is scan sheet? are we supposed to know this table? And if I ask you to show this scan sheet table, are you just going to ignore answering and reply with another question?

Please, please understand that you are making it very hard for anyone in this community to help you and you've been doing this in almost all of your post in this community.

When someone is trying to help you, please just stop about what you are doing. If you think you are magically going to get answers with any effort in explaining, unfortunately it will not work because people cannot read your mind. Instead, read the other person's comments carefully and do your best to answer his questions and do what he is asking you to do.

Sirfyaad

Hi,

@Joseph_Seddik @WillowMobileSys I want that if the start value in the AWB No column matches with the unique keys, then the courier name will be auto selected.

form page

main sheetmaint sheet viewfunction

WillowMobileSys

You still haven't explained:

1) Where "AWB No" lives. Is it an actual column or just a Label Name for a column?

2) What data values it has/how it is populated.

So....I will assume that AWB NO is a column that is part of your "Main Sheet" table and we just can't see it in your images and it always starts with one of the Unique Keys values from your "EnumList" table. I will also assume that you are only interested in populating your Courier Partner column with the Courier Name text value rather than a Ref value.

In your case, you need to return an actual column value. A FILTER() function will not work as it only return row references and not specific values from columns.

Instead, since this needs to use the STARTSWITH() function, you will need to use a SELECT() function.

ANY(SELECT(EnumList[Courier Name], 
    STARTSWITH([_THISROw].[AWB NO], [Unique Keys])))

Now, whether you use this in Initial Value or App Formula depends on your desired outcome.

If you want the value initially set and allow the user to change it OR if you need to set this value in an action or through automation, then place the function in the Initial Value property. Note that you can still set the value as not editable by the user in a Form

If you want the Courier Partner to always match with the user's choice of AWB NO and anticipate that this value will only ever change through a user updating AWB NO in the Form, then you will want to place the function in the App Formula property.

Sirfyaad

I want courier name based on start value. But the function you have given. He's not working.

WillowMobileSys

"Unique Keys" column is defined as NUMBER. It needs to be TEXT. Change the column data type and try again.

Sirfyaad

@WillowMobileSys

Thank you so much. its working.

Sirfyaad

This function is working. But I have one more condition with it. I want the value to be auto selected with the help of the EndSwitch().

AND(ANY(SELECT(Courier Auto Setup[Courier Name], 
    STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]))),
ANY(SELECT(Courier Auto Setup[Courier Name], 
    ENDSWITH([_THISROW].[AWB NO], [CHANNEL]))))

Condition AND(ANY(SELECT(Courier Auto Setup[COURIER NAME],STARTSWITH([_THISROW].[AWB NO],[COURIER AWB NO]))), ANY(SELECT(Courier Auto Setup[COURIER NAME],ENDSWITH([_THISROW].[AWB NO],[CHANNEL])))) has an invalid structure: subexpressions must be Yes/No conditions

Joseph_Seddik

I can't understand your question and there is no function called EndSwitch() in AppSheet.

So guessing that you want to combine two conditions in a SELECT() statement, then you should put the logical function AND() inside SELECT() not outside. Like this:

SELECT(table[column]
AND(
(condition 1),
(condition 2)
)
)

Sirfyaad

SELECT(Courier Auto Setup[Courier Name]
  AND(STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]),

SELECT(Courier Auto Setup[Courier Name], 
    ENDSWITH([_THISROW].[AWB NO], [CHANNEL]))


Expression 'SELECT(Courier Auto Setup[Courier Name] AND(STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]), SELECT(Courier Auto Setup[Courier Name], ENDSWITH([_THISROW].[AWB NO], [CHANNEL]))' was unable to be parsed: Number of opened and closed parentheses does not match.

Joseph_Seddik

SELECT(Courier Auto Setup[Courier Name],
  AND(
    STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]),
    ENDSWITH([_THISROW].[AWB NO], [CHANNEL])
  )
)

Sirfyaad

Hi @Joseph_Seddik

Function is ok but second function is not working see below.

SELECT(Courier Auto Setup[Courier Name],
  AND(
    STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]),
    ENDSWITH([_THISROW].[AWB NO], [CHANNEL])
  )
)
Note: This expression could impact performance.

The list of values of column 'COURIER NAME'
....from rows of table 'Courier Auto Setup'
....where this condition is true: (ALL these statements are true:
........1: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') starts with the text value (The value of column 'COURIER AWB NO')
........2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') ends with the text value (The value of column 'CHANNEL'))

WillowMobileSys

Your SELECT() expression is returning a LIST of values.

If you only expect a single row to match (as I suspect you do), then wrap your SELECT with the ANY() function to return the desired name value...like this:

ANY(
    SELECT(Courier Auto Setup[Courier Name],
      AND(
        STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]),
        ENDSWITH([_THISROW].[AWB NO], [CHANNEL])
      )
    )
)

Sirfyaad

Hi I am confused expression are okey but not working second function "ENDSWITH" why

ANY(
    SELECT(Courier Auto Setup[Courier Name],
      AND(
        STARTSWITH([_THISROW].[AWB NO], [COURIER AWB NO]),
        ENDSWITH([_THISROW].[AWB NO], [CHANNEL])
      )
    )
)

Note: This expression could impact performance.

One randomly chosen value from this list (
........The list of values of column 'COURIER NAME'
........from rows of table 'Courier Auto Setup'
........where this condition is true: (ALL these statements are true:
............1: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') starts with the text value (The value of column 'COURIER AWB NO')
............2: (The value of 'AWB NO' from the row referenced by 'GATE ENTRY NO') ends with the text value (The value of column 'CHANNEL')))

Joseph_Seddik

In what way it is not working? What are you getting?