Can I show a Yes/No, but store different values in the spreadsheet based on Yes/No?

I have a field called “Power Tag” that is configured as a Yes/No field. If the user selects Yes, I want to store the string “PTPresent” in the spreadsheet. If the user selects No, I want to store the string “PTAbsent” in the spreadsheet. I like the simplicity in the UI of the Yes/No toggle rather than a drop list with PTPresent and PTAbsent in the list values. But when it comes time to send the data to the customer, the simple Yes/No in the data doesn’t make as much sense as the more descriptive text. Is there any way to convert the Yes/No to the text (and back) without using additional columns in the spreadsheet along with Excel formulas?

1 22 3,808
  • UX
22 REPLIES 22

Mike_A
Participant V

@Eleshia_Robinson - I think you can do that by adding a virtual Y/N type column that you use in our view, and just add an appformula to the actual PTR column to calculate its value based on the VC column.

You can use IF or even a SWITCH formula.

edit: I wrote the above backwards… I was actually thinking the VC would be the PTPpresent/PTPabsent (calculated from the Y/N physical col).

I tried the Virtual Column, but I can’t make a virtual column editable. Maybe I’m misunderstanding.
My goal is to have the user select Yes/No, then save some text based on the yes/no selection. Because the Yes/No field is the one that I don’t want to save to the spreadsheet, I set this one up as the virtual. Then I set up another field linked to the spreadsheet field and added the formula. Only to find that the user cannot edit the virtual field in the UI.

The exact interaction you are describing will require 2 physical columns in your spreadsheet: The Y/N question and the PTPresent/PTAbsent column. As you have noted, virtual columns are not directly editable, therefore any Y/N column which users can interact with must be a physical column in your database.

There are several workaround which may get you closer to what you want, however. If you like the feel of buttons rather than dropdowns, you can make your “PTPresent/PTAbsent” an Enum type and specify button, and eliminate the Y/N column.

If the Y/N button is storing the answer you need - I am curious, why do you need to store the PTPresent/PTAbsent values in its place?

Thanks for the correction @Jonathon. I was actually thinking the VC would be the PTPpresent/PTPabsent, and sheet needed a Y/N col. I wrote it backwards

One solution is to create a two rows table and then read it with a Ref field. If you coose the Yes/No field as a label, it will show those values but it will save different values into your data source.

Hi @Aleksi - maybe I’m missing something…how do I get the Yes/No to show as a label but plug different values into the data table? (I’ve got a 2 row/2 column table) Yes - 1, No - 2 & have the primary form looking at the table as a Ref field…but my data table is still showing yes/no as the entry.

[have clearly spent too much time overthinking the build of this new app, as NOTHING is making sense right now!]

You have 2x2 table like
ID > Value
Yes-1 > Yes
No-2 > No

If the ID column is a Key and Value as Label, it will show you Yes & No on the form but when you save it, it will write either Yes-1 or No-2. For this, you need to use Ref field.

ah… ok…makes sense.
I just made the one column Y/N ([Any Stress?]) and in an adjacent column, I did an ‘ifs’ formula:

ifs(
[Any stress?]= “Yes”,2,
[Any stress?]= “No”,0,
)
and then did a sum of all of the columns where I had similar numerical entries.

@Tim_Martin
Hi,

When you say “adjacent” column, is it in the spreadsheet or a virtual one in the app?
I am trying to figure out the same workaround - my enum produces only YES value (empty string are defaults). I need to have 0 defaults (which is going to be initial value but YES should be saved as 1).

Hi @AlexShevyakov -

In my app, all of my columns are spreadsheet columns.

In the example above, Column 1 is a Y/N column [Any Stress?].
The next column in my table, I have named [Stress Math]. This column is a numbers column (type=number) and the column is hidden {show=no} from the end users (when I was building it, I had the column visible to make sure the math was working).

In the [Stress Math] column, I have an Auto Compute - App Formula:
ifs([Any stress?]= “Yes”,2,[Any stress?]= “No”,0,)
which plugs in either 0 (for no) or 2 (for yes) (your values will probably differ).

Then in one of my last columns, I have another Numbers column [Risk Score] that just totals all of the previous number columns (I have 7 or 8 of these columns):
sum (list([24 math] + … +[stress math]))

Lastly, I have 3 columns [Low Risk]/[Medium Risk]/[High Risk] that are initially hidden, but will show based on the number provided by the math in the [Risk Score] column.

My table is probably way too simplistic (meaning long…columns reach from A to AD) and could probably be pared way down, but this works for me.

Please let me know if this help you at all or if I need to explain/re-explain something differently. I’m happy to help how I can.

Thank you @Tim_Martin, apologies for the delayed response.
So, you are in fact using two columns to provide “other” value for the Y/N response.
I had an impression that it is possible to achieve such substitution within the app itself without a need to modify the spreadsheet table.

Hi Tim, I´m triying to do something exactly as you are explaining here, can you help me with more details? Where did you use this formula? In the column formula, especifically for that field?

Thanks

Hi Vassili -

sorry it has taken so long to get to this as I am not on the boards very often…

I have a column that asks a Y/N question (in this case, I’m asking about ‘any stress’ (are you feeling stressed? yes / no. The user chooses either yes or no. There is nothing fancy inside of that.

The next column, I have it titled ‘stress math’ which is a ‘numbers’ type column. In here, I have an app formula that looks at the previous column:

ifs(
[Any stress?]= “Yes”,2,
[Any stress?]= “No”,0,
)

So what this formula does it says 'if the column titled ‘any stress’ = Yes, give that a value of 2. If the ‘any stress’ column = No, give that a value of 0.

There are other columns that basically do the same thing as the previous 2 - one asks a question and the other does some math based on the answer. The other columns are enum columns, but do basically the same thing - they have a number associated with them.

Then at the end I have a column that adds up all of the ‘math’ columns, (a risk score), and then there are 1 of 3 columns that will show in the app based on the risk score.

Hope this helps!

@VMM - were you able to get your app working??

I’m not sure if this has always been the case, but AppSheet will not allow a column type of TRUE/FALSE to be the primary key. I understand why they would restrict this, but unfortunately it makes using a REF table for true/false values a tiny bit more annoying.

Specifically, the True/False 2x2 table primary key must be of type integer. Then when you use it as a ref column you have to add [id]=1 or [id]=0 to represent TRUE rather than just [id].

Excellent!

Jonathan_S
Participant V

Why not just use an enum list?

option 1: PTPresent
Option 2: “PTAbsent

I believe OP wanted “the simplicity in the UI of the Yes/No toggle rather than a drop list with PTPresent and PTAbsent in the list values”.

It wouldnt be a drop down list, you can set it to be buttons.

This is what a Yes/No column toggle looks like as a quick-edit column in a detail view. I’m sure many would argue that is a bit more pleasing to the eye than enum buttons.

2X_2_25982671f600400914cd0de034904803443f8cbe.png

This is True

VMM
Participant I

Hi Tim,
Sorry for the late response.
Yes i did, thanks so much!

Top Labels in this Space