Answered

Write PowerApps Gallery Data to Single Row in SharePoint

(0) Share

Report

Posted on by SharePointJoe

I have a form in a Power App where questions can be selected and contained in a gallery. I'm using the gallery to provide an answer field that when the Next button is clicked, it patches the values from the gallery to a single row in a SharePoint list. There will not be a specific number of questions & answers, but the number will always range between 5 and 15 questions & answers. I've created the SharePoint list to have a column fields that correspond to the question and answer order (i.e. Question1, Answer1, Question2, Answer2).

I've been able to figure out a way that writes the data like I want it to by using First(GalleryItem.AllItems).labelName.Text and Last(FirstN(GalleryItem.AllItems, Order).labelName.Text formula formats. However, this only works if there are exactly 15 questions and answers. If there are less, the last answer data is repeated for the remaining slots. If there are less questions and answers, those fields should be blank in SharePoint.

My basic Patch command is:

Patch(

Form, Defaults(Form),

{

Question1: First(formGallery.AllItems).Question.Text,

Answer1: First(formGallery.AllItems).Answer.Text,

Question2: Last(FirstN(formGallery.AllItems, 2)).Question.Text,

Answer2: Last(FirstN(formGallery.AllItems, 2)).Answer.Text,

Question3: Last(FirstN(formGallery.AllItems, 3)).Question.Text,

Answer3: Last(FirstN(formGallery.AllItems, 3)).Answer.Text,

Question4: Last(FirstN(formGallery.AllItems, 4)).Question.Text,

Answer4: Last(FirstN(formGallery.AllItems, 4)).Answer.Text,

....

Question15: Last(FirstN(formGallery.AllItems, 15)).Question.Text,

Answer15: Last(FirstN(formGallery.AllItems, 15)).Answer.Text

}

));

Any thoughts on how I can accomplish my goal of writing the gallery items to a single SharePoint row, leaving any of the 15 question and answer fields blank if they are not used?

Categories:

Using Apps Using Formulas

All responses (5)

Answers (1)

WarrenBelz 146,668 Most Valuable Professional on at

Like (0)

Report

Re: Write PowerApps Gallery Data to Single Row in SharePoint

Hi @SharePointJoe ,

Now fixed - hazards of free-typing code.
SharePointJoe 6 on at

Like (0)

Report
Re: Write PowerApps Gallery Data to Single Row in SharePoint

@WarrenBelz ,

Thank you! This solution you provided worked well! I did have 1 small issue implementing it, and saw that there was an extra comma at the end of the line in your code sample.

Question15: If( wItems > 14, Last(FirstN(formGallery.AllItems, 15)).Question.Text, <--- Remove this comma > ), Answer15: If( wItems > 14, Last(FirstN(formGallery.AllItems, 15)).Answer.Text )

Once that comma was removed from each of the lines, it worked perfectly!
Verified answer

WarrenBelz 146,668 Most Valuable Professional on at

Like (0)

Report
Re: Write PowerApps Gallery Data to Single Row in SharePoint

Hi @SharePointJoe ,

If your Patch statement - apply as far back as you need to

With( {wItems:CountRows(formGallery.AllItems)}, Patch( Form, Defaults(Form), { Name: txtName.Text, Date: DateTimeValue(txtDate.Text), Reviewer: txtReviewer.Text, Question1: First(formGallery.AllItems).Question.Text, Answer1: First(formGallery.AllItems).Answer.Text, Question2: Last(FirstN(formGallery.AllItems, 2)).Question.Text, Answer2: Last(FirstN(formGallery.AllItems, 2)).Answer.Text, Question3: Last(FirstN(formGallery.AllItems, 3)).Question.Text, Answer3: Last(FirstN(formGallery.AllItems, 3)).Answer.Text, Question4: Last(FirstN(formGallery.AllItems, 4)).Question.Text, Answer4: Last(FirstN(formGallery.AllItems, 4)).Answer.Text, .... Question15: If( wItems > 14, Last(FirstN(formGallery.AllItems, 15)).Question.Text ), Answer15: If( wItems > 14, Last(FirstN(formGallery.AllItems, 15)).Answer.Text ) } ) );

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
SharePointJoe 6 on at

Like (0)

Report
Re: Write PowerApps Gallery Data to Single Row in SharePoint

@WarrenBelz ,

I've considered using the CountRows formula, to achieve this but I have been unable to figure out how to incorporate this methodology into my formula. One thing complicating the mix is that I have several additional fields that are to be attached in the Patch command to provide details such as Name, Date, Reviewer. Since I have these additional fields, I don't think I can do some type of loop to run through each item with the CountRows suggestion, unless you have something different in mind and can perhaps give me a better example. My code in full looks like:

Patch( Form, Defaults(Form), { Name: txtName.Text, Date: DateTimeValue(txtDate.Text), Reviewer: txtReviewer.Text, Question1: First(formGallery.AllItems).Question.Text, Answer1: First(formGallery.AllItems).Answer.Text, Question2: Last(FirstN(formGallery.AllItems, 2)).Question.Text, Answer2: Last(FirstN(formGallery.AllItems, 2)).Answer.Text, Question3: Last(FirstN(formGallery.AllItems, 3)).Question.Text, Answer3: Last(FirstN(formGallery.AllItems, 3)).Answer.Text, Question4: Last(FirstN(formGallery.AllItems, 4)).Question.Text, Answer4: Last(FirstN(formGallery.AllItems, 4)).Answer.Text, .... Question15: Last(FirstN(formGallery.AllItems, 15)).Question.Text, Answer15: Last(FirstN(formGallery.AllItems, 15)).Answer.Text } ));
Because I have several items that don't repeat, can I still use the CountRows formula? If so, how would I incorporate it?
WarrenBelz 146,668 Most Valuable Professional on at

Like (0)

Report
Re: Write PowerApps Gallery Data to Single Row in SharePoint

Hi @SharePointJoe ,

The formula

CountRows(formGallery.AllItems)

will get you the number of items present - just base your formula to only apply for the number.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps