cancel
Showing results for 
Search instead for 
Did you mean: 

FromJson() formula

Hi PowerApps Team,

 

There is a JSON() formula which transforms a collection or table to a JSON formated string. Could you also add a formula to transform a JSON formatted string back to a Collection?

 

My goal is to de-crease the time my app needs to load in data. Let me give insights in my thoughs, open spoiler!

  •  
Spoiler
  • Process I'm using now is that I use Flow to Call Stored Procedure and return the results with the Respons-Request step to PowerApps. This mean with a large dataset it takes time to load the data to powerapps.
  • Proces I want to be able to use. I call one Stored Procedure, which calls several stored procedures and convert to returned result to JSON formatted string by using nvarchar(max) datatype. Then add the end of my master SP I return all the datasets in a select statement. Example:
SP MasterSP
@Parameters
BEGIN
DECLARE @DataSet1 NVARCHAR(MAX),@DataSet2 NVARCHAR(MAX),@DataSet3 NVARCHAR(MAX),@DataSet4 NVARCHAR(MAX)

Call DataSet1Proc @Parameters, @DataSet1 OUTPUT
-- Convert @DataSet1 to JSON Formatted file script

Call DataSet2Proc @Parameters, @DataSet2 OUTPUT
-- Convert @DataSet2 to JSON Formatted file script

Call DataSet3Proc @Parameters, @DataSet3 OUTPUT
-- Convert @DataSet3 to JSON Formatted file script

Call DataSet4Proc @Paremeters, @DataSet4 OUTPUT
-- Convert @DataSet4 to JSON Formatted file script


Select
@DataSet1 AS [DataSet1]
,@DataSet2 AS [DataSet2]
,@DateSet3 AS [DataSet3]
,@DataSet4 AS [DataSet4]

END

Basically this will return a one row table with 4 columns, all string formatted.

 

Then in PowerApps I could do:

Collect(FromProc, MasterProc.Run());
ClearCollect(DataSet1, FromJson(First(FromProc).DataSet1));
ClearCollect(DataSet2, FromJson(First(FromProc).DataSet2));
ClearCollect(DataSet3, FromJson(First(FromProc).DataSet3));
ClearCollect(DataSet4, FromJson(First(FromProc).DataSet4));
Clear(FromJson)


Getting 4 Collections by running only one flow instead of four. Also returning a lot faster because only one column has to be loaded.

 

 

@TopShelf-MSFT  & @Mr-Dang-MSFT , could you also take a look at this? I would think you will like this approach a lot ;-) Thanks.

 

Greating Paul

Status: New
Comments
Level: Powered On

Hi,

 

A FromJSON formula function or the ability to use collect() with JSON strings instead of record literals would solve a lot of problems for us too, but our use case is a bit different. We often use PowerApps to create customized SharePoint forms, and we need to be able to save tabular state data in the edited list item. The data could for example come from a multi-value lookup to a different SharePoint list, but we need to stora a snapshot of these values since the data in the lookup list changes over time. If we could use JSON() to store the collection data as a string in the current list item and then convert it back to a collection when the item is displayed or edited, it would be really great!

 

Thanks,

// Robert