cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McMonty
Level: Powered On

SQL Server Datasource but saving to SharePoint

Hi,

I want to create an app where I populate lookups from SQL Server views, then after the user have made the selections, save those selections to a SharePoint list. I'm trying to find some examples of doing this, but haven't found any that demonstrates this. Any ideas would be appreciated 🙂

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: SQL Server Datasource but saving to SharePoint

Hi @McMonty ,

Do you want to update sharepoint list based on the selection of SQL view?

Could you tell me the data type of the related fields in sharepoint list and SQL view?

I suggest you save the selections of SQL view in collection and then use this collection to update sharepoint list.

Please note that if you use complex data type, the formula will be a little special, so I really need to know it.

 

I assume that all the related fields' data type are text and made a similar test for your reference:

1)use a gallery to display the SQL view.

2)insert check box inside the gallery (for you selecting items to update)

3)insert a button to submit items, set the button's OnSelect:

ClearCollect(selecteddata,Filter(Gallery1.AllItems,Checkbox1.Value=true));
//save the selected items of SQLview as a collection
if the collection's structure is very different from the sharepoint list, you could use RenameColumns/AddColumns/DropColumns/ShowColumns to modify the structure of the collection

ForAll(selecteddata,Patch(fruit1234,Defaults(fruit1234),{Title:selecteddata[@Title],price:ID}))
//use this collection to update sharepoint list
you should make sure that the related two fields are the same data type
If the two fields name are the same, you could use selecteddata[@fieldname] to avoid ambiguity
If the two fields name are different, you just need to use fieldinsharepoint:fieldincollection

In my test, I use Title field in SQL view to update Title field in sharepoint list and

                           ID field in SQL view to update price field in sharepoint list.

1174.PNG

 

Here's a blog about updating multiple records at the same time for your reference:

(also you could just select one item and save this item to a collection to update)

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
McMonty
Level: Powered On

Re: SQL Server Datasource but saving to SharePoint

Hi,

I'm not going to update SQL, just a SharePoint list.

 

This is the aim for the app:

The app will load a list of Publication titles and Editions from a SQL Server view

The user selects a title (Text)

The user selects a current edition (Text)

The user selects a previous edition (Text)

The user selects a currency (GBP, USD, EUR) (Text)

The user enters a percentage (0 - 20) (Number)

Clicks Submit

 

The submit will save these selections to a SharePoint list

 

The end goal is to use these selections to take items (these are taken from a SQL view) from a previous edition and calculate a new price for these items in the new edition. I don't want to load these items into the app since it will in some cases be 10k+ item lines.

I just want to get the selection into a SharePoint list and can then do whatever is needed there.

 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,766)