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 Like

@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). :frowning:

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?

1 Like

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 :frowning:

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.

3 Likes

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.

2 Likes

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.

1 Like

Excellent!

@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.

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.

image

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

This is True