Dynamic dropdownbox

Hello friends,

I have a question about an expression for a dynamic dropdown boxe.

First let me try to exlpain my case:

I have one table “KUNDEN” (means Customer in German) where I want to add Customers name and e-mail, but also
1 - who recommended the client (field REF1) and
2 - the recommender 2nd. grade (field REF2) → recommender of the recommender.

So these are two levels of recommendation and both fields are drop-down boxes.

I am able to create the 1st. recommender dynamic filled drop down box, but the expression for the second one is quite some complex matter for me:
image

As you can see on the picture

I need the expression for the REF2 Dropdown box. As an example: If I would edit the record from Magdalena Aydin, who’s recommender (REF1) is Christian Aydin, THEN I need the Dropdown box for REF2 be filled with only the recommender of Christian Aydin, which is Robert Aksan.

I really would appreciate you help with this expression in the Data Validty “Valid IF” field in the properties of this attribute.

Thnx.
Robert

You might try:

LOOKUP(
  [_THISROW].[REF1],
  "KUNDEN",
  "KUNDE",
  "REF1"
)

or (equivalent):

ANY(
  SELECT(
    KUNDEN[REF1],
    ([KUNDE] = [_THISROW].[REF1])
  )
)

See also:

1 Like

Thank you for trying me to help Steve. I got the following error with both of your statements:

Column Name ‘REF2’ in Schema ‘KUNDEN_Schema’ of Column Type ‘Ref’ has an invalid data validation constraint ‘=LOOKUP( [_THISROW].[REF1], “KUNDEN”, “KUNDE”, “REF1” )’.

Column Name ‘REF2’ in Schema ‘KUNDEN_Schema’ of Column Type ‘Ref’ has an invalid data validation constraint ‘=ANY( SELECT( KUNDEN[REF1], ([KUNDE] = [_THISROW].[REF1]) ) )’.

1 Like

Ah! Oops! I was thinking Initial value rather than Valid If, for some reason. :frowning:

Try this instead:

SELECT(
  KUNDEN[REF1],
  ([KUNDE] = [_THISROW].[REF1])
)
1 Like

Thank you so much Steve, this worked out!
BR. Robert

1 Like