Initial Value for a Ref Column

[Dan_Oliphant]

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.

[Jonathon]

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])

[Dan_Oliphant]

It works for me

[James_McFarlane]

@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

INTIAL VALUE

[Jonathon]

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.

[James_McFarlane]

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])
)

[Jonathon]

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

Is this correct?

[James_McFarlane]

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.

[Jonathon]

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))
)

[James_McFarlane]

Jonathon,

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

[Jonathon]

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.

[James_McFarlane]

shared

[Jonathon]

"Ok Google,

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

[James_McFarlane]

Where are you located, time zone?

[Jonathon]

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

[James_McFarlane]

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

[Jonathon]

Testing some things right now.

[James_McFarlane]

me too. let me stop.

[Jonathon]

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?

[James_McFarlane]

Yes that is the desired effect.

[Jonathon]

Okay - feel free to revoke my editor access.

The problem was your initial value was referencing the ‘Privado’ text instead of its paired key value of ‘0’. When dealing with REF columns, you have to target the parent / child records key column.

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

In the above, it is basically saying:

If [Cubierto_por_ARSs] is True, then leave the value blank, otherwise populate it with ‘0’ which is associated to ‘Privado’

[James_McFarlane]

so that is why I have been needing to do the if, because of the null. Will have to go in the DB and set some default values to CERO.

[James_McFarlane]

Thank you very. I tried with the 0 and left it with ‘Privado’. You are awesome!