Select() Function

[Sirfyaad]

I want the value of the [PACKET TYPES] if the value matches with [awb no]. But here the function is not working.

AND(
    NOT(
        ISBLANK(
            ANY(
                SELECT(
                    check[PACKET TYPES],
                    STARTSWITH([_THISROW].[AWB NO], [Start])
                )
            )
        )
    ),
    NOT(
        ISBLANK(
            ANY(
                SELECT(
                    check[PACKET TYPES-2],
                    ENDSWITH([_THISROW].[AWB NO], [END])
                )
            )
        )
    )
)

 

[TeeSee1]

Your expression evaluates to TRUE for the [awb no] of value "5132....TCQ".

I believe what you want is

ISNOTBLANK(
 SELECT(
  check[PACKET TYPES],
  AND(
   STARTSWITH([_THISROW].[AWB NO], [Start]),
   ENDSWITH([_THISROW].[AWB NO], [END])
  )
 )
)

[Sirfyaad]

Thank you for reply. @TeeSee1 

---

@Sirfyaad wrote:

SELECT( check[PACKET TYPES-2], ENDSWITH([_THISROW].[AWB NO], [END]

---

I want that if the value of the start key matches, then the value of the pocket types should be found. And if the end key value matches, then the pocket types-2 value will be found.

[TeeSee1 · Accepted solution]

So you want to retrieve a text value and not a yes-no value.

Assuming you do NOT have cases where both start and end values match then you can try

CONCATENATE(
 ANY(
  SELECT(
   check[PACKET TYPES], 
   STARTSWITH([_THISROW].[AWB NO], [Start])
  )
 ),
 ANY(
  SELECT(
   check[PACKET TYPES-2],
   ENDSWITH([_THISROW].[AWB NO], [END])
  )
 )
)

If you do have cases where both start and end values match, you get a concatenated string of PACKET TYPES and PACKET TYPES-2.