Any (select

Hi,

I have two action buttons that create a new row in a table each when hit to send info to a shipping company to send sample packs to cutomers.

Each button sends a differrent type of sample.

Each has an expression to determine the name of the button so it it changes from “Send Biscuit Sample” to “Sample Sent”

The expression looks to see if the [outlet no’ (unique id of the outlet appears in the table and if the [SKU] (sample type) appears also

Two types of sample are “SAMP” and “SAMPBIS”

The expresions works fine except the ANY part as the expression looks as though it finds the first row with a matching [Outlet No] and then looks at the [SKU] column and gives button name based on that even if there is a row below that has the same [outlet no] but a different [SKU].

Expression below give the button the name “Send Biscuit Sample” even though there is a row in the table with the same [Outlet No] and also has “SAMPBIS” as the [SKU].

IF(AND(IN([Outlet No], SAMPLE Requests[Outlet No]),ANY( SELECT (SAMPLE Requests[SKU], [Outlet No] = [_THISROW].[Outlet No]))=“SAMPBIS”),

“Sample Sent”,“Send Biscuit Sample”)

I’m looking for an expression that asks if the there is any row that has the same outlet no and SKU that = “SAMPBIS” anywhere in the table.

Thanks

Phil

Order Fulfilled? Unique ID Outlet no SKU Order Description Order Date Delivery Date Shipping ex vat Shipping Inc Vat Total Order Weight Complete Total Complete Order Total Ex vat Complete Order Total Inc Vat Order Taken By Date Taken Time Taken FAO Standing Delivery/Order Details Name Address 2 Address 3 Town County Post Code Latitude longitude Latlong Cash Status Invoice Number Route User Stop Actual Payment Type (driver) Payment Type Signature Print Name Driver User Id Due Date Photo Outlet Discounted Invoice Discounted Invoice Discounting Open/Closed
NO f5b26746 E9BB6340 SAMP SAMPLE PACK 1 bottle each of BB/APA 1 each of LBB/LTW/LBM Treats 2/1/2021 2/1/2021 1900 phil@listersbrewery.com 2/1/2021 19:22:35 Cherry Orchard Farm The Old Warren Flintshire Cheshire CH4 0EQ 2/1/2021
NO f49bc39c E9BB6340 SAMPBIS SAMPLE BISCUITS 1 each of LBB/LTW/LBM Treats 2/1/2021 2/1/2021 1900 phil@listersbrewery.com 2/1/2021 19:29:15 Cherry Orchard Farm The Old Warren Flintshire Cheshire CH4 0EQ 2/1/2021

Yes, ANY() is essentially the same as INDEX(… , 1 ).

Perhaps:

ISNOTBLANK(
  FILTER(
    SAMPLE Requests ,
    AND(
      [Outlet No] = [_THISROW].[Outlet No] ,
      [SKU] = "SAMPBIS"
    )
  )
)
2 Likes

For reference:

2 Likes

This might help as well

2 Likes

That’s great. That worked.

Thank you

Phil

1 Like