Ref column with initial value

[PJMatelli]

Hello,
I am looking for a way for a column to be a ref in one situation and auto-complete in another situation. I have employees that belong to companies. When an employee gets a Merit increase, I want the “New” information to be filled in, but when an employee gets a reclassification, I want the same “New” fields to be editable. I have tried a ref field with an initial value of a lookup to the “Current” info (i.e. Company name), but then I can’t edit the field. I think what I am looking for is an if statement that sets the value to be a ref or auto filled in. I hope that makes sense?

Thanks in advance.

[Rich_E]

Is the user selecting “Merit Increase” or “Reclassification” as a enum in the app? Do you want the behavior to change based on when the user chooses one or the other?

[PJMatelli]

Yes, that is exactly what they are doing and yes, I need the behavior to change based on their selection.

[Rich_E]

I would first create a virtual column that is set equal to your Ref column. Then make this new virtual column the REF column and change your original REF column to text.

Then I would populate an IF THEN condition in the Valid If and Suggested Values formula for the Original Ref column.

Here is an example of the Valid if formula:

if( [Status]="Merit Increase”, in([_this], Reftable[Refcolulmn]), true )

Suggested values formula:

Reftable[Refcolumn]

This formula says, if the Status is Merit Increase, then only allow for the referenced values. If it is not then allow for any value in this field. At that state Appsheet should be pulling suggested values and be functioninng like autocomplete.

[PJMatelli]

Thanks Rich…I tried this late last night and couldn’t make it work. What would your suggestion be for the App Formula on the virtual column? Or am I missing something? Thanks.

[Rich_E · Accepted solution]

The app formula for the virtual column would just be the column you originally used as your ref column. So the app formula is simply “[REFCOLUMN]”.

This is done to let the vritual column function as the REF column, so you can have the original column change states as needed.