cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Form based on SQL Rows

Hi all, 

 

I have a SQL table which has a list of questions (new question each row).

I want those questions to be shown on the powerapps screen.

I want to use the Toggle (Yes/No) for each row in the table.

 

Example of the Questions Table.

Question IDQuestionGroup ID
1Test11
2Test22
3Test33

 

You then fill out the form on the powerapps and it saves the answers to another table like below

Question IDAnswerVisit ID
11Vis12
20Vis12
31Vis45
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@MrDannyHarry 

My suggestion would be to create an app that looks like my picture below.  I will give step-by-step instructions to allow you to build it like mine.

xyz.png

#1  Add a blank gallery to the app called 'Gallery1'.  Set the Items property to

 

your_questions_table

 

#2  Place a label into the gallery row which will display the question.  Put this code in the Text property of the label

 

ThisItem.Question

 

#3  Put a toggle into the gallery row.  Name the toggle 'Toggle1'.

#4  Place a button below the gallery and use this code in the OnSelect property.

 

// creates the table of values to be inserted into the answers table
ClearCollect(colResponse,
    ShowColumns(
        AddColumns(
            Gallery1.AllItems,
            "Answer",If(Toggle1.Value,1,0),
            "Visit ID", "Vis12"),
        "Question ID", "Answer"
    )
);

// inserts values into the answers table
Collect(your_datasource_name, colResponse);

 

Note: I have manually assigned "Vis12" here but you may want to come up with your own method.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

4 REPLIES 4
Super User III
Super User III

@MrDannyHarry 

My suggestion would be to create an app that looks like my picture below.  I will give step-by-step instructions to allow you to build it like mine.

xyz.png

#1  Add a blank gallery to the app called 'Gallery1'.  Set the Items property to

 

your_questions_table

 

#2  Place a label into the gallery row which will display the question.  Put this code in the Text property of the label

 

ThisItem.Question

 

#3  Put a toggle into the gallery row.  Name the toggle 'Toggle1'.

#4  Place a button below the gallery and use this code in the OnSelect property.

 

// creates the table of values to be inserted into the answers table
ClearCollect(colResponse,
    ShowColumns(
        AddColumns(
            Gallery1.AllItems,
            "Answer",If(Toggle1.Value,1,0),
            "Visit ID", "Vis12"),
        "Question ID", "Answer"
    )
);

// inserts values into the answers table
Collect(your_datasource_name, colResponse);

 

Note: I have manually assigned "Vis12" here but you may want to come up with your own method.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

@mdevaney 

 

Many thanks for this, I think i get the concept and what it needs to do!

My only question is how would I then patch the data in the Collection to SQL.

 

Cheers,

Danny

@MrDannyHarry 

I've made a small adjustment to my code above.  I believe this will patch the data to SQL.  If it does not work I have other ideas.  Please let me know.

 

Collect(your_datasource_name, colResponse);

 

Fun fact: COLLECT can be used to insert new rows into any datasource.  Not just collections!

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Many thanks @mdevaney!!

 

Really helpful here and ha actually enabled me to do more things that I needed!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,671)