Conditional Dropdown behaviour

AppSheet AppSheet Q&A
1

Hello,

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

I have 4 tables:
Clients

3X_5_b_5bd4011446cfcbc5bb011be67e1e0330d9daafc3.png

Client Sites

3X_0_c_0c0219d7a8e36a917f26cbb9900709496aa92f03.png

Client Rates

3X_7_7_772d2707219fd944af14cfa52e64b42f56faf885.png

Bookings

3X_1_e_1e391c6417c59eed486237dceab7cf2be0b0c8ed.png

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.

2

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 ?

1 Like
3

Just tried and I get the exact same behavior.

4

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])
    )
  )
)
1 Like
5

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 )

1 Like
6

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

A star as always! Thanks, Steve!

1 Like
7

Initial value expression:

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

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

1 Like
9

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.

2 Likes
10

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.

1 Like