Read from SQL Server but write to excel

[miguels]

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.

[BCBuizer]

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.

[miguels]

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]

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.

[miguels]

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]

@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 · Accepted solution]

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.