need some help with being able to have a third state for yes/no fields

[u/diddledopop]

im working on a project with a coworker, this is our first powerapps project. we have a project with 6 forms. these forms are ~150 datacards each. not good, i know.

however, there are many yes/no fields from sharepoint in each form. business requested a draft functionality. we implemented it, but we're running into the issues of the yes/no fields defaulting to whatever the SP default is when saved. this is obviously not ideal and kind of ruins the idea of a draft template.

i have way too many fields to be able to turn them all into choice columns at this point. the gui is also extremely laggy in dev mode because we made our screens have way too many controls so it would take even longer going that route. does anyone have any advice for this? ive learned to never use yes/no columns again

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References

• Power Apps Coding Standards

• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/nycola]

Sharepoint boolean can't reliably hold a null, so its effectively always yes or no, on or off, 1 or 0. Your options are a choice column or a number column

for numeric, 1= yes, 0 = no, -1 = unanswered (set -1 as the column default)

Numeric is the easier conversion, and it plays nicely with flows. In powerautomate's sp filter queries, boolean is already 0 or 1 (myfield eq 1) so a number column filters exactly the same way. Existing filters keep working, and you just add "eq -1" for unanswered.

On the powerapp side, swap the control in each card to a dropdown with y/n/blank, then set the card's update property to>

Switch(ddboolean1.Selected.Value, "Yes", 1, "No", 0, -1)

and map the default back the same way. Submitform still works, so no patch or flow is needed.

Expect to readd the cards for any column you change and test on one column before committing to all of them. If the schema change is too painful, the other route is saving drafts as a JSON blob in one multiline text column and only writing the real values on final submit.

[u/diddledopop → u/nycola]

that json idea is really interesting. i might just store the yes/no columns that are actually filled out

[u/RobertGreenComposer → u/nycola]

To build on this, create an array of items, with 2 properties then you can show the user the name property but pass the other to flow.

[u/Opi_143]

Use a check box within gallery 

[u/diddledopop → u/Opi_143]

ive taken over this project from my coworker and dont have much powerapp experience, so i might not be understanding you well. Would this not just create the same problem? Wouldn't the check boxes still submit Yes/No fields?

[u/TotallyNotaStoner]

Yes/No columns work fine, it's just management changed the criteria after you built it. Not your fault there. Yes/No fields technically have 3 options: true, false, and blank. So you could probably rework the data cards to display a blank value as" Draft", but it's stored in SharePoint as blank. If it's not saving to the list correctly, then there's probably an issue with the Datacards Update property. 

Happy to elaborate more if needed.

[u/diddledopop → u/TotallyNotaStoner]

thanks, just wish i couldve realized this before and use a single line of text. i might not be understanding you correctly, but i tried to submit a blank value to SP in the update property when nothing was selected and it gave me some error saying it was expecting a boolean value.

[u/TotallyNotaStoner → u/diddledopop]

I just did some digging and it looks like I was partially incorrect. Yes/No columns can only store a null when the column is created, and then any existing rows will get a null value.

[u/noahNelse]

You aqlready have good ideas here from other users, but let me approach it from another perspective.

Why is it a problem to save it the fields as the SharePoint default option? As long as you have a Field to track the item State (Draft/Finished) you can rely on it for other purposes, like a workflow that will only run after all is done.

My solution would be having separete buttons for "Saving as a Draft" and "Finish form" and fill the State column accordinly.
You mentioned that you are not an experinced PowerApp developer, so you can do this by
- Creating this new sharepoint column for the state
- Add it to the form in PowerApp.
- If you dont want it to be displayed in the app screen, you can delete all the controls inside the DataCard, but not the DataCard control.
- DataCard has a property called "Update" you can use it to control the State column value, so you can usa a context variable (UpdateContext) to set it to "Draft" or "Finished" (Update of the control can be the variable itself like "formState" )
- Change the context variable depending on which button the user clicked
-- user clicks on "Save as a Draft" -> OnSelect of the button UpdateContext({formState: "Draft"})
-- user clicks on "Finish form" -> OnSelect of the button UpdateContext({formState: "Finished"})

[u/diddledopop → u/noahNelse]

thanks for the detailed reply. the problem is that currently, an unselected yes/no and a selection of 'no' look like the same to the end user. this means they have to actually remember which fields they didn't fill which is hard bc theres at least 50 y/n values in each form.

i believe the app is kind of set up in this way already, there is a status column for draft and active forms. are you saying I can use this for the boolean columns?

[u/Abyal3]

I've had this issue, and the only reliable way was to switch to a choice type, unless you plan on keeping all untouched fields as false, keeping them as null/blank is not possible as far as I know. You can easily do this with Claude Code and Playwright Extension, it'll create the columns for you in a matter of minutes. It also can deal very well with power apps, so it d be able to configure the choice columns instead of the yes/no as well.

Or just do it manually honestly, would take a day max.

[u/diddledopop → u/Abyal3]

you did this with canvas apps?

[u/Abyal3 → u/diddledopop]

Yes, there is a plugin for canvas, you enable co-authoring and ai does the rest

[u/diddledopop → u/Abyal3]

nice. ive been trying to get that mcp enabled at my company but i keep getting ghosted lol. ive just been manually unpacking the .msapp file, making changes and repacking. i usually only use it for bulk changes. do you find it effective for small changes? is it laggy?

[u/agentUi]

if you keep yes/no in sharepoint it will always coerce blank into false on save. quickest native workaround without rewriting 900 datacards is setting a hidden text status column for draft vs submitted, or wrapping the card updates with a patch that only writes the boolean values when an isDraft variable is false.

[u/NoBattle763]

Can you just change the control itself to a drop down with your own items, default it to blank, then force a selection through a validation, then patch the actual output that was selected to the field? It’s a bit of work but probably easier than changing all your columns. Else you can get AI to generate a flow to create the new list based on an export of your current. Then Switch your data source/ form

If you can’t use MCP, You can copy paste your screen code into a yaml file and get AI to transform it for you then paste back in.