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

'Collect()' Not Pushing Data To Azure SQL Database

Hello, Everyone!

I'm new to the PowerApps community and to PowerApps as well. I'd like to ask your help to solve an issue that I'm stuck on for a few days now.

I'm building a simple PowerApps app - integrated into PowerBI - that I'd like to enable to users to send some data to the Azure SQL Database, directly from the PowerBI report.
What would they send? Simple IDs, numbers, a lot of them! Essentially, the entire PowerApps app would be a button that they'd push, which would send the IDs of the items currently selected in their PowerBI report, thus stating "Yes, I'm confirming, I've seen these items".

PowerBI:

I've downloaded the PowerApps Custom Visualization from the marketplace & placed it onto my report, added the ID column of the dataset that I'd like to pass to the PowerApp.
Published it to My Workspace (later moved it to another workspace).

Then, i went into Editing View on the PowerBI website & clicked on Create a new App, on the PowerApps visualization. That took me to the PowerApps website & i started creating a simple app, failed, read some documentation, came back, failed again, rinse & repeated for a couple days, now i'm here fellas!
 
The Database:

CREATE TABLE [dbo].[MyTable](
[ID] Bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
[EntryID] BIGINT NOT NULL,
[REVIEWER] NVARCHAR(128) DEFAULT SUSER_NAME(),
[ReviewDate] DATE DEFAULT GETDATE()
);

I'm trying to insert into this table, the plan is that the data for the [EntryID] column will be passed from the PowerBI report, the [REVIEWER] will come from PowerApps' User().Email function. The [ID] & [ReviewDate] are generated.

I've created a dedicated Login & User for the App, which has SELECT, INSERT, DELETE permissions to the [dbo].[MyTable] object.

PowerApps app, so far:
It won't be hard to imagine, it's just a button that the user can press, which has this function OnSelected action:
Collect(DropColumns('[dbo].[MyTable]'; "ID"; "ReviewDate"); AddColumns([@PowerBIIntegration].Data.ID; "REVIEWER"; User().Email) )

With this, I'd like to insert the data coming from PowerBI (The IDs) to the [EntryID] column of the database & the User's name to the [REVIEWER] column. The [ID] [ReviewDate] should be generated by default.

The issue:
The data is not passed. I can press the button, the Collect() function does run - and doesn't return any kind of message, successful or unsuccessful -, and I've checked the audit logs of the Azure SQL Database.

According to the logs, the Collect() function does reach the database and carries out 3 actions: Connects to the Database; Checks the schema, columns, structure, etc. of the table; Selects the rows from the table;

But the goddamn thing doesn't even try to insert anything!

I've spent the past days scouring these forums, various websites, StackOverflow, even YouTube, to no avail.
Please help me, oh mighty gods of PowerApps, don't let my first PowerApp wither in development hell forever!

Any ideas are much appreciated!

Thanks a bunch in advance!
OneFatCat

2 ACCEPTED SOLUTIONS

Accepted Solutions
WillPage
Level 8

Re: 'Collect()' Not Pushing Data To Azure SQL Database

I suggest using the OnSelect property of the button to trigger a Flow to add the row to the database.

OneFatCat
Level: Powered On

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi @WillPage !

Great tip!
I've read up on PowerApps + Flow a bit & i could get my app to work!!!

How i did it:

//	Collect the EntryIDs, that should be marked as reviewed
Collect	(
			//	Collection of these EntryIDs
			MyCollection,
			
			//	The Actual EntryIDs coming from the PowerBI Report
			RenameColumns	(
								[@PowerBIIntegration].Data.ID_as_Number,
								"ID_as_Number",
								"EntryID"
							)
		)

		
//	Call the 'MS Flow' that'll insert the data into the database
;;
ForAll	(
			MyCollection, PowerAppsbutton.Run(EntryID, User().Email, Now())
		)

I'm collecting the EntryIDs i'd like to send to the Database to a Collection (named MyCollection). In the next step, i'm cycling through each row of this Collection & call the Flow each time, passing the current row's EntryID, the user's email and a timestamp.


But how does the Flow look like?

It's rather simplistic.
Capture Flow.PNGThe flow used to send data to the SQL Server

(Sorry, I had to censor those fields. Those would be the EntryIDs)

Essentially, i just added in a SQL Server -> Insert row step after the auto-generated PowerApps step (when you create a new flow from the PowerApps Studio's interface), then i selected that everything that will be passed to the SQL Server, will come from PowerApps (see the ForAll() function, how it passes the required data as parameters!).

I've already created the connection between the PowerApps & the SQL Server, when i was playing around with the Collect() method, so it was already there. That's rather easy & straigthforward to create as well.


That's it. The PowerApp works now! Smiley Happy

Thanks a lot @WillPage and @Drrickryp  for bearing with me & helping me out!
OneFatCat

8 REPLIES 8
Super User
Super User

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi Mr. Cat,

The collect function will work if: 1. all required columns in the datasource are included in the function, 2. that the column names match exactly the names of the columns in the datasource and 3. the syntax is correct.  For example

Collect( IceCream, { Flavor: "Strawberry", Quantity: 300 } )

Please note that the data added to the datasource is enclosed in curly braces and the column names use a colon followed by the data point to be added.  For more information regarding the  Collect function please see function-clear-collect-clearcollect 

OneFatCat
Level: Powered On

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi @Drrickryp !

Thank you very much for the tips!

Let's go over them:
1. All required columns in the datasource ([dbo].[MyTable] in my case) are included in the function.
A) I believe my Collect() function fulfills this requirement, I've removed 2 columns from my datasource ([ID] and [ReviewDate]), as they are not required and generated by the database.

2. The column names match exactly the names of the columns in the datasource.
A) This should be fulfilled as well. The 2 columns into which I'd like to insert data is [ID] and [REVIEWER]. The data for the [ID] column comes from the PowerBI report, which is named ID as well and the data for the [REVIEWER] column comes from the User().Email function, which was added to the ID data with the AddColumns() function & renamed to REVIEWER.

3. The syntax is correct
A) I've used this structure/syntax, because I've seen it being recommended over many places for this specific purpose (writing data to a SQL database), so i went with it. Also, the PowerApps editor didn't raise any issues & the request even made it to the SQL Database, so i assumed the root of the issue may lie elsewhere.


I've read the piece of documentation you've linked & tried the syntax you proposed in your answer, but it didn't work. Smiley Sad
Even worse, I checked the logs & it turned out, the Collect() function's request(s) didn't even arrived to the SQL Database.

My Collect() function looks like this now:
Collect( DropColumns('[dbo].[MyTable]'; "ID"; "ReviewDate"); {EntryID: [@PowerBIIntegration].Data.ID; REVIEWER: User().Email} )

If i screwed up the syntax again, could you please help how should it look like instead & what i did wrong?

Another Question:

I assumed, that the Collect() function's syntax with curly braces was only for the insertion of a single value/record (as in the link you've provided, they were only inserting single values/records as well), but not for tables. I presumed in those cases, you'd have to provide a table with identical structure to the datasource, as the second argument of the Collect() function.

Now, the function doesn't even reach the SQL database after the changes in the syntax, but your answer leads me to believe that columns of data can be provided within the curly braces, effectively "building" a table column-by-column to be inserted into the datasource, is that true?


Any ideas are very much appreciated!

Thanks a lot!
OneFatCat

Super User
Super User

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi OFC,

 

You are correct that you can bulk update a datasource with the Collect function. In one of my apps, I use this method to add records from a gallery into a SharePoint datasource as follows:

Collect(LetterData,ShowColumns(Gallery8.AllItems,"MDFullName","PatientName", etc.)

In that case, all that is necessary is that the column names are identical matches. In SharePoint as in Sql, the ID field is automatically incremented so it is not necessary to add it manually. Like you, I shape the table before collecting it.  In my case, I used ShowColumns but the input table of data could be shaped with RenameColumns(), DropColumns(), and AddColumns() functions to match it to the datasource. To see if you actually have data to add to your datasource, you could check by capturing the data in a Gallery or a specific item as the Text of a label. I did that and in my case, I used the Gallery.AllItems property as the source for the data that I wanted to collect into my SharePoint list. 

OneFatCat
Level: Powered On

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi @Drrickryp !

Thanks again for the suggestions, you've directed me to the good direction!

I've created some DataTables to make sure the names are correct, and it turned out, that they weren't!
I wanted to insert the values into the [EntryID] column, while the name of the column in the collection that i wanted to insert was [ID], which came from the PowerBI Report.

Now, I've fixed that with RenameColumns(), but encountered another issue. In the Azure SQL Database's table, the [EntryID] is an Integer & the IDs coming from PowerBI were Strings.
I've looked around, couldn't get the Value() function to work in PowerApps (It can't convert columns/tables?), so i went with the other suggestion i found, which is to convert the value in the DataSource (PowerBI) to a number. That's why the name of the IDs coming changed from simply ID to ID_as_Number.

I've changed that now, and this is how my Collect() function looks like now:

Collect(
	//	Collection -- Where I'd like to insert my records
	//		Columns: [EntryID] (The Azure SQL Database Table's [EntryID] column);
	//			 [REVIEWER] (The Azure SQL Database Table's [REVIEWER] column)
	ShowColumns	(
			'[dbo].[MyTable]';
			"EntryID";
			"REVIEWER"
			);

	//	Collection -- The records I'd like to insert
	//		Columns: [EntryID] (The IDs coming from PowerBI, renamed, converted to Number type);
	//			 [REVIEWER] (Comes from the User().Email function, from PowerApps)
	RenameColumns	(
			AddColumns	(
					Value([@PowerBIIntegration].Data.ID_as_Number);
					"REVIEWER";
					User().Email
					);
			"ID_as_Number";
			"EntryID"
			)
	)

 

I can see the data in the PowerApps, in both the online PowerApps editor & in PowerBI, when i click the button.
But the goddamn thing still doesn't want to insert anything!

I've checked the logs again, the PowerApp now checks some stuff in the Database, like the tables/views in it, the table definition where I'd like it to insert the data, but after that it just queries the data with a SELECT in the table & considers it done! So, still no luck with writing back data.

I feel like I'll go mad over this issue! The data is in the PowerApps, it shows up in the DataTable, the Collect() function even reaches & queries the database, but it just won't insert the data! So close yet so far!

I can't be grateful enough for bearing with me @Drrickryp !

Any ideas for this issue would be appreciated!

Thanks a bunch!
OneFatCat


Super User
Super User

Re: 'Collect()' Not Pushing Data To Azure SQL Database

One more thought, could there be something about the source table that isn’t allowing it to be written to. Just for a trial, create a new Sql table and try to use it as the target of the Collect() function. 

OneFatCat
Level: Powered On

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hello @Drrickryp !

One step closer to the solution, but not quite there yet!

I've tried to tweak the Azure SQL Database's table. I tried to enable IDENTITY_INSERT on the table (to explicitly provide even the ID of the record) and insert a single record (it worked!), but it also turned out, that PowerApps doesn't work with the Default constraints at all!

I had the false assumption that in case i don't include a column in the first parameter (Source or Target, based on your point of view) of the Collect() function, it'll be simply left out of the INSERT statement the PowerApp will run & the default value will get added.

Like so:

 

INSERT INTO [dbo].[MyTable]
(
     [ID]
,    [EntryID]
,    [REVIEWER]
)
VALUES
(
     1
,    1213
,    'SomeDude@SomeCompany.com'
)

 


That's not the case, the [ReviewDate] column of the Table has a Default constraint, which would provide it data, but that's not happening. The **bleep** thing doesn't even try inserting anything in this case!

So, I've included the values for the [REVIEWER] and [ReviewDate] columns as well, besides the [EntryID].

This is how my Collect() function looks like now:

 

Collect	(
		//	Target Collection
		//	Columns:
		//		[ID]		 --	Identity, given by the database, not required
		//		[EntryID]
		//		[REVIEWER]	 --	Since PApps doesn't work with defaults at all, it's required!
		//		[ReviewDate]	 --	Since PApps doesn't work with defaults at all, it's required!
    		ShowColumns	(
					'[dbo].[MyTable]';
					"EntryID";
					"REVIEWER";
					"ReviewDate
				);


		//	Source Collection
		//	Columns:
		//		[EntryID] 	 --	Comes from PowerBI
		//		[REVIEWER]	 --	User().Email
		//		[ReviewDate]	 --	Now()
    		AddColumns	(
					//	Rename the data coming from PowerBI, from "ID_as_Number" to target's name
					RenameColumns	(
								[@PowerBIIntegration].Data.ID_as_Number;
								"ID_as_Number";
								"EntryID"
							);
				
					"REVIEWER";
					User().Email;
	
					"ReviewDate";
					Now()
				)
	)


So, what's the issue still?

 

The ID. The [ID] column was Bigint & nothing happened, it wasn't generated automatically by the database, when i used the above Collect() function.
Then, i guessed the Bigint datatype might confuse the PowerApp, so i changed it to simple Int(eger). Then, i received the following error, when i pushed the button that executes my Collect() function:

Error with the Provider.
Provider's response: Not a valid connector response. returning message as is: 
 {
  "error": {
    "code": 502,
    "source": "europe-001.azure-apim.net",
    "clientRequestId": "86566579-e4a5-4d74-9069-db26184846bc",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": "Incorrect syntax near the keyword 'FROM'.\r\nclientRequestId: 86566579-e4a5-4d74-9069-db26184846bc",
      "source": "sql-we.azconn-we.p.azurewebsites.net"
    }
  }
}


From what i could decipher, it's a syntax issue in the statement the PowerApp tries to execute.

I've granted the PowerApp SELECT and INSERT rights on the table (just in case i removed it accidentally, during the tweaks). After that, i stopped receiving the error message, but it still doesn't insert anything into the database!
Also, syntax error, due to lack of access???

Is there a way to execute SQL Queries/Stored procedures from PowerApps? At this point, that would be much easier & straightforward, than to figure out what the hell is the problem of the native functions with literally 0 feedback from the tool.

Thanks a lot!
OneFatCat

 

WillPage
Level 8

Re: 'Collect()' Not Pushing Data To Azure SQL Database

I suggest using the OnSelect property of the button to trigger a Flow to add the row to the database.

OneFatCat
Level: Powered On

Re: 'Collect()' Not Pushing Data To Azure SQL Database

Hi @WillPage !

Great tip!
I've read up on PowerApps + Flow a bit & i could get my app to work!!!

How i did it:

//	Collect the EntryIDs, that should be marked as reviewed
Collect	(
			//	Collection of these EntryIDs
			MyCollection,
			
			//	The Actual EntryIDs coming from the PowerBI Report
			RenameColumns	(
								[@PowerBIIntegration].Data.ID_as_Number,
								"ID_as_Number",
								"EntryID"
							)
		)

		
//	Call the 'MS Flow' that'll insert the data into the database
;;
ForAll	(
			MyCollection, PowerAppsbutton.Run(EntryID, User().Email, Now())
		)

I'm collecting the EntryIDs i'd like to send to the Database to a Collection (named MyCollection). In the next step, i'm cycling through each row of this Collection & call the Flow each time, passing the current row's EntryID, the user's email and a timestamp.


But how does the Flow look like?

It's rather simplistic.
Capture Flow.PNGThe flow used to send data to the SQL Server

(Sorry, I had to censor those fields. Those would be the EntryIDs)

Essentially, i just added in a SQL Server -> Insert row step after the auto-generated PowerApps step (when you create a new flow from the PowerApps Studio's interface), then i selected that everything that will be passed to the SQL Server, will come from PowerApps (see the ForAll() function, how it passes the required data as parameters!).

I've already created the connection between the PowerApps & the SQL Server, when i was playing around with the Collect() method, so it was already there. That's rather easy & straigthforward to create as well.


That's it. The PowerApp works now! Smiley Happy

Thanks a lot @WillPage and @Drrickryp  for bearing with me & helping me out!
OneFatCat

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 111 members 3,722 guests
Please welcome our newest community members: