Conditional Dropdown behaviour

[AlexM]

Hello,

I am a bit confused about my app’s behavior.

I have 4 tables:
Clients

Client Sites

Client Rates

Bookings

The expected outcome is that, when creating a new Booking, the [Rate] dropdown will only have the [Client Rates] for the client that owns the selected [Client Site]

[Rate] from Bookings table has the following ValidIf condition:

SELECT(Client Rates[ID],[_THISROW].[Client Site].[Client]=[Client])

What actually happens is that when I select the dropdown, the list is empty.
I click New on the dropdown to add a rate but then I click Cancel.

Selecting the [Rate] again, the dropdown will have the correct values.

EXTRA: If, after selecting the Rate, I change the Client Site and then save, it allows me to do it even if the rate should be invalid.

Tried to upload a screen recording but the format is not allowed.

[Aurelien]

HI

Did you check on the “suggested Value” field ?
This is supposed to be a list, I think you should add your formula there.

Can you let us know if that works for you ?

[AlexM]

Just tried and I get the exact same behavior.

[Aurelien]

Can you use CloudApp to provide a short video of your settings ? That will give us a clue about something else maybe
(CloudApp ==> give shareable link you can put here )

[Steve · Accepted solution]

AlexM:

What actually happens is that when I select the dropdown, the list is empty.
I click New on the dropdown to add a rate but then I click Cancel.

That’s very odd. I’d call it a bug. Consider reporting it to support@appsheet.com.

As for the Valid if expression, try this:

FILTER(
  "Client Rates",
  IN(
    [Client],
    SELECT(
      Client Sites[Client],
      ([ID] = [_THISROW].[Client Site])
    )
  )
)

[AlexM]

OK, it works, but why?
What was wrong with my formula?

A star as always! Thanks, Steve!

[Steve]

AlexM:

What was wrong with my formula?

I don’t know, but chained dereferences (e.g., [_THISROW].[Client Site].[Client]) are new, and I myself haven’t used them. They may still be buggy.

[AlexM]

It is buggy.
Tried to bring the value to a virtual column and it worked.

I am using your solution.
Let support know about the issue.

[Steve]

Initial value expression:

ANY(
  FILTER(
    "Client Rates",
    IN(
      [Client],
      SELECT(
        Client Sites[Client],
        ([Client] = [_THISROW].[Client Site])
      )
    )
  )
)

[AlexM]

Initial won’t work as a client might have multiple rates.
The rate has to be manually selected every time.