InfoPath Dev

Hello,

I have a two-part question regarding repeating tables on a form. The first part is something many users have asked, but googling and also searching this forum doesn't give me the straight-forward answer for what I'm looking for, so bear with me as I explain below.

Part one: A user fills out a form that has a repeating table. For simplicity (I can figure out the rest) the repeating table has FirstName and LastName columns. Once the form is submitted I want all the rows from the repeating table to save to a SharePoint list with the form's AutoID (not GUID) while the form is saved into the Form library. So, in this simple scenario, filling out the form (repeating table) will look something like this:

FirstName LastName
John   Smith
Jane   Doe

Once the user submits the form it is saved with the AutoID = 1001 (or 1002, 1003 etc.) in the form library. The repeating table data is saved in a list and should look like this:

AutoID FirstName LastName
1001   John   Smith
1001   Jane   Doe

How to save all the rows in a repeating table without code has been asked many times, but the answers I've read are not that clear to me. Feel free point me to a thread if it already exists (I might have missed it during my search).

Part two (I've never seen this asked/answered before): My customer asked if it's possible to have a button on the form that will get all the data from the repeating table list (where the AutoID = 1001 etc.) and export it to Excel? This will speed up the work and there is no need for the end-user to query SharePoint. A no-code solution (or as little code as possible) is preferred.

Thanks in advance,

Patrick

Are you using SharePoint Online or on-prem? What version? If you are using on-prem, you may be able to get your data to SharePoint using CAML in the form. If you are using online, it would likely be easiest to use a third party tool like FormsViewer, or you can use Power Automate (Flow) for this.... For auto-ID (which I don't like, for a variety of reasons) it is best to generate that as part of your submit rules - query the existing forms, increment by one, then submit. For Excel.... well, again, the answer depends on whether online or on-prem. I don't know of a no-code way to get the data to Excel from InfoPath. But Power Automate can do this, and you could use some form logic to provide the user with a link to the resulting file.... It may be possible to use HTTP Request and Response actions in Power Automate for this too - but those are premium connectors.

Hi Hilary and thanks for answering,

We are using SP Online version 16, but I can't tell if it's 2016 or 2019. I work for a .edu so we get updates last, after all paying customers.

I'm not that well-versed in PA (Flow), just started learning Power Apps and Power Automate so if you have something I can look at it's much appreciated.

I will generate the AutoID so that's not a problem since I've done that many times in the past.

This past Friday I found this site and started tinkering with the suggestion. http://shareallthepoint.blogspot.com/2015/07/submit-infopath-each-repeating-table.html. What I did differently was to change the repeating section to a repeating table and it works fine but has some limitations.
1. It does not save the form, only data (I cannot find the form anywhere on my site).
2. I have to create all the fields in SharePoint first
Still tinkering with it.

Any other suggestions??? -Thx

Posting an update if it will help somebody else with the same problem.

Since my customer is only interested in getting an Excel output I decided to write some code in Excel VBA. I'm not going to post any code, just conceptualize the manual process.

1. Customer exports to Excel and downloads the .iqy file
2. Opens the .iqy file in Excel and deletes all the unnecessary rows and columns
3. Then use "Text to Columns" in Excel and finally copy/paste special with Transpose https://trumpexcel.com/split-multiple-lines/

For step 2, if you use code, there will be a pesky Import Data dialog if another Excel file is open and it can be bypassed using this code https://stackoverflow.com/questions/26221618/how-can-i-open-an-iqy-file-without-being-prompted-for-a-destination

Thx Hilary for all you do!