Initial Value for a Ref Column

I have a Ref row, and due to security filters now only 1 options is available. I really need this to be the initial value, so my clients don’t have to keep clicking it everytime they have to fill out a form.

If you can guarantee there will only ever be one option as a result of security filters, you can use ANY() to default to that value.

ANY(reftable[refcolumn])

1 Like

It works for me

@Jonathon
I am trying to accomplish something similar. In my case I want to limit the ref column to a specific value and set the dropdown automatically. when another field is set to NO. It works except that the user has to select the value from the dropdown.

VALID_IF
Screen Shot 2020-05-19 at 11.51.19 PM

INTIAL VALUE
Screen Shot 2020-05-19 at 11.51.39 PM

Hey James,

Your initial value formula does not consider the list from your valid_if statement - it is still trying to pick ANY() value from the entire v_aseguradoras table. However, the value it is trying to select is likely failing your valid_if statement so it is turning up blank.

Your initial value formula will need to be a SELECT() statement that matches the valid_if statement.

1 Like

Hi Jonathon,

I set the VALID_IF:

IF([Cubierto_por_ARSs]=False,
ORDERBY(
SELECT(v_Aseguradoras[Codigo_SAIL],[Codigo_SAIL]=0)
,[Nombre]),
  ORDERBY(v_Aseguradoras[Codigo_SAIL],[Nombre])
  )

Need help with the Intial Value:

if([Cubierto_por_ARSs]=False,
SELECT(v_Aseguradoras[Codigo_SAIL],[Codigo_SAIL]=0,true),
ANY(v_Aseguradoras[Codigo_SAIL])
)

If [Cubierto_por_ARSs] is FALSE, then there should only be one possible value, which is identified by [Codigo_SAIL]=0

Is this correct?

Yes that is correct it is correct. If it is false then dropdown value should be set to 0 and if Cubierto_Por_ARS is True then the user should be able to select from a list of values.

Valid IF:

  1. You can simplify the expression by changing [Cubierto_por_ARSs]=False to simply [Cubierto_por_ARSs], and reordering IF() expression. The [Cubierto_por_ARSs] column is already a T/F value, so you don’t need to re-evaluate it.
  2. In the case that [Cubierto_por_ARSs] is FALSE, we expect a single value (0). Therefore, the ORDERBY() expression is not necessary.

The simplified expression is:

IF([Cubierto_por_ARSs],
  ORDERBY(v_Aseguradoras[Codigo_SAIL],[Nombre]),
  SELECT(v_Aseguradoras[Codigo_SAIL],[Codigo_SAIL]=0)
)

Initial Value:

  1. Same as above, we do not need to re-evaluate the [Cubierto_por_ARSs] column.
  2. If the expected value is known, as it is in this case (0), then you can use that value explicitly.

The simplified expression is:

IF([Cubierto_por_ARSs],
  "",
  "0"
)

If the value is NOT known, then you could write:

IF([Cubierto_por_ARSs],
  "",
  ANY(SELECT(v_Aseguradoras[Codigo_SAIL],[Codigo_SAIL]=0))
)
2 Likes

Jonathon,

Man thanks for all the help I really appreciate it. But it is still not working. :cry: The column is a ref column.

Hey James;

Unfortunately I don’t think I have enough information to troubleshoot this further for you. If you shared editor access to me at redacted, I could likely find the issue.

1 Like

shared

"Ok Google,

Remind me tomorrow at 9am to help James McFarlane with his AppSheet app"

:joy:

2 Likes

:rofl: :rofl: :rofl:

Where are you located, time zone?

CST - It’s 6:40pm here currently.

It is 8:39 here. I am int EST.

Testing some things right now.

me too. let me stop.

James, I believe it is fixed now. The current behaviour as follows:

  1. Your app allows three possible states for the [Cubierto_por_ARSs] column: TRUE, FALSE, or NULL.
    • In the case of a BIT/BOOL value, NULL = FALSE
  2. If the value is NULL or FALSE, the application will default to ‘PRIVADO’ (REF KEY = 0), and there will be no other valid options for users to select other than a NULL value (clear the value)
  3. If the value is TRUE, the box will start blank and the dropdown will have all possible values.

Is this correct?

2 Likes

Yes that is the desired effect.