cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
miguels
Frequent Visitor

Read from SQL Server but write to excel

Hi all. PowerApps novice here, and I'm struggling with something, below.

 

Currently:

I have built a simple app that reads and writes data to a SQL Server table. In the App, users select an item from a gallery, which takes them to a new screen with a form where they can edit one field ("Comment"), which is then written back to the table in SQL Server.

 

The data/columns in the SQL Server table is as follows:

Item_ID (Key) - not editable

Item_Description - not editable

Comment - editable (This column is initially blank in the database table, and only populated through the PowerApp).

 

Currently, this works fine, but I'd like to eliminate writing data back to SQL Server.

 

Goal:

I would like to modify the above app, so that data will still be read from the SQL Server table (ie. Item_ID and Item_Description) - but comments are maintained/written to a separate excel spreadsheet in OneDrive, with the following structure:

 

Item_ID (Key)

Comment

 

That means when a user selects an item in the Gallery (populated via the SQL Server table), it will take them to the form screen where a lookup will need to be performed (ie. Join Table.Item_ID = Excel.Item_ID) to display any existing comments for that Item - which they can update. If none exist, I would like them to be able to create a comment.

 

Is this possible? How can I accomplish this?

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi @miguels ,

 

The tips I gave are based on the assumption you wanted to edit existing comments, rather than creating new records for new comments, preserving a 1:1 relationship between Items in your SQL data source and the comments in your SharePoint list.

 

How should this then work? If you are saying there should be a 1:N relation between SQL and SharePoint, you'll probably need a second gallery to show all comments related to the selected item in the first gallery.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

6 REPLIES 6
BCBuizer
Super User
Super User

Hi @miguels ,

 

First of all you need to connect your form to the Excel data source. The Item_ID and Comments columns will be in your data source, so you can use the standard datacards. For the description you will have to add a custom datacard the performs a LookUp() to retrieve the information from your SQL data source.

 

By setting the Item property of your form to Gallery.Selected and the DefaultMode to If(IsEmpty(LookUp(YourExcel, Item_ID = Gallery.Selected.Item_ID)), FormMode.New, FormMode.Edit). This will check if the selected item exists in your Excel data source and display any comments. If it doesn't exist the form will act as a new form for the user to insert any comments.

 

Ps. the Default property of the Item_ID datacard needs to be set to Gallery.Selected.Item_ID so it shows up in the form when in New mode.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Hi @BCBuizer appreciate the reply.

This almost seems to work except that when I set the form's Item property to "Gallery.Selected", it throws the error "Invalid formula. Expected a value compatible with 'Data Source'".

I believe this is because the Form is set to the excel data source, while the Gallery is set to the SQL server data source. The gallery needs to be connected to the SQL server data source because it is the 'source of truth' - in that it contains all the data - the excel data source will only include those records that include a comment.

 

Hope that makes sense.

miguels
Frequent Visitor

Ah, I have changed the form's Item property to "LookUp(YourExcel, Item_ID = Gallery.Selected.Item_ID)" and it seems to have solved the issue I just reported.

 

I'll continue with your solution and report back.

Hi @BCBuizer I still can't get it working, unfortunately.

 

The form seems to display OK when I select an item that does exist in the excel. However, if I select an item that doesn't, the form just displays "Getting your data..." and I can't seem to fix it. It seems as if the form doesn't want to go into FormMode.New.

 

Currently, I have the form setup like this:

Default Mode: If(IsEmpty(LookUp(YourExcel, Item_ID = Gallery.Selected.Item_ID)), FormMode.New, FormMode.Edit)
Item: LookUp(YourExcel, Item_ID = Gallery.Selected.Item_ID)


Any thoughts?

miguels
Frequent Visitor

@BCBuizerI have managed to resolve the above by changing the "IsEmpty" to "IsBlank". This seems to change the mode correctly. This caused another issue whereby the form would get stuck in a mode when I backed out of the form screen to gallery and into another item, which I have resolved by resetting the form everytime the form screen loads.

 

But alas, now I'm encountering another issue: When I try to save an item where the comment already exists in the excel, it updates fine. But it doesn't seem to want to write a new record. Any thoughts?

BCBuizer
Super User
Super User

Hi @miguels ,

 

The tips I gave are based on the assumption you wanted to edit existing comments, rather than creating new records for new comments, preserving a 1:1 relationship between Items in your SQL data source and the comments in your SharePoint list.

 

How should this then work? If you are saying there should be a 1:N relation between SQL and SharePoint, you'll probably need a second gallery to show all comments related to the selected item in the first gallery.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,981)