cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ROE-DK
Regular Visitor

JSON: 2 listboxes to single JSON object with custom property names?

Hi

I'm having some issues getting to grips with the JSON function in PowerApps.

I have these two collections, presented in two multiselect listboxes:

Listbox1:

 

 

ClearCollect(Collection1, 
	{ Name: "Group1", 
	  Number: 100, 
	  Type: "Sec" }, 
	{ Name: "Group2", 
	  Number: 200, 
	  Type: "Mail" }, 
 	{ Name: "Group3", 
	  Number: 300, 
	  Type: "Sec" }, 
	{ Name: "Group4", 
	  Number: 400, 
	  Type: "Mail" } 
);

 

 

Listbox2: 

 

 

ClearCollect(Collection2, 
	{ Name: "BLDG1", 
	  Stories: 100, 
	  Material: "Wood" }, 
	{ Name: "BLDG2", 
	  Stories: 200, 
	  Material: "Grass" }, 
	{ Name: "BLDG3", 
	  Stories: 300, 
	  Material: "Cement" }, 
	{ Name: "BLDG4", 
	  Stories: 400, 
	  Material: "Concrete" } 
);

 

 

I'm trying to get the Name of the selected values, in both listboxes, exported to a single JSON object like this:

 

 

{
    "Buildings":  [
                      "BLDG1",
                      "BLDG3",
                  ],
    "Groups":  [
                   "Group1",
                   "Group3"
               ]
}

 

 

 

Simply adding the selections to a single collection and then using the JSON() function, gives a single JSON object but all the values are under the "Name" property:

 

 

Clear(CombinedCollection);
Collect(CombinedCollection,ListBox1.SelectedItems.Name);
Collect(CombinedCollection,ListBox2.SelectedItems.Name);
Set (JSONVariable, JSON(CombinedCollection))

[{"Name":"Group1"},{"Name":"Group3"},{"Name":"BLDG1"},{"Name":"BLDG3"}]

 

 

 

Creating a new collecting and adding the selected values to seperate columns just provided even more confusing result

 

 

Clear(CombinedCollection);
Collect(CombinedCollection,  AddColumns(ListBox2.SelectedItems.Name, "Buildings", ListBox2.SelectedItems.Name));
Collect(CombinedCollection,  AddColumns(ListBox1.SelectedItems.Name, "Groups",    ListBox1.SelectedItems.Name));
Set (JSONVariable, JSON(CombinedCollection))

[{"Buildings":[{"Name":"BLDG1"},{"Name":"BLDG3"}],"Name":"BLDG1"},{"Buildings":[{"Name":"BLDG1"},{"Name":"BLDG3"}],"Name":"BLDG3"},{"Groups":[{"Name":"Group1"},{"Name":"Group3"}],"Name":"Group1"},{"Groups":[{"Name":"Group1"},{"Name":"Group3"}],"Name":"Group3"}]

 

 

 

Furthermore, I would like single selections to result in a single-value property, instead of a single element array.

Can anyone point me in the right direction for solving this?

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@ROE-DK 

You will only be able to get close to what you want exactly.  The JSON function produces well formatted JSON.  What you want is not well-formatted (in terms of the JSON function).

 

So, considering this scenario:

ListBox1 Items property:

Table({ Name: "Group1", Number: 100, Type: "Sec" }, 
      { Name: "Group2", Number: 200, Type: "Mail" }, 
      { Name: "Group3", Number: 300, Type: "Sec" }, 
      { Name: "Group4", Number: 400, Type: "Mail" } 
)

 

ListBox2 Items property:

Table({ Name: "BLDG1", Stories: 100, Material: "Wood" }, 
      { Name: "BLDG2", Stories: 200, Material: "Grass" }, 
      { Name: "BLDG3", Stories: 300, Material: "Cement" }, 
      { Name: "BLDG4", Stories: 400, Material: "Concrete" } 
)

 

And finally, setting your variable:

Set (JSONVariable, 
    JSON(
       {Buildings: ListBox2.SelectedItems.Name,
        Groups: ListBox1.SelectedItems.Name
       }
    )
)

 

Will produce:

{
    "Buildings": [
        {"Name": "BLDG1"},
        {"Name": "BLDG3"}
    ],
    "Groups": [
        {"Name": "Group1"},
        {"Name": "Group3"}
    ]
}

You will notice that the JSON function's "well formatted JSON" includes the column name of the Value in each record.  And, since it is observing the "well-formatting", it will not allow a record to have a table/array at one point and just a single string at others (single selection).

 

I would recommend in your case to not use the JSON function and instead, convert it to text.

ex.

"{""Buildings"": " & 
    If(CountRows(ListBox2.SelectedItems)>1, "[") &
    With({_bld: Concat(ListBox2.SelectedItems, """" & Name & """,")}, Left(_bld, Len(_bld)-1)) &
    If(CountRows(ListBox2.SelectedItems)>1, "]") & "," &

    Char(10) & """Groups"":" & 
    If(CountRows(ListBox1.SelectedItems)>1, "[") & 
    With({_grp: Concat(ListBox1.SelectedItems, """" & Name & """")}, Left(_grp, Len(_grp)-1)) &
    If(CountRows(ListBox1.SelectedItems)>1, "]") &
"}"

The above will produce the following (multiple selections):

{"Buildings": ["BLDG1","BLDG3"],
"Groups":["Group1""Group3]}

And Single Selection in Building:

{"Buildings": "BLDG1",
"Groups":["Group1""Group3]}

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
RandyHayes
Super User III
Super User III

@ROE-DK 

You will only be able to get close to what you want exactly.  The JSON function produces well formatted JSON.  What you want is not well-formatted (in terms of the JSON function).

 

So, considering this scenario:

ListBox1 Items property:

Table({ Name: "Group1", Number: 100, Type: "Sec" }, 
      { Name: "Group2", Number: 200, Type: "Mail" }, 
      { Name: "Group3", Number: 300, Type: "Sec" }, 
      { Name: "Group4", Number: 400, Type: "Mail" } 
)

 

ListBox2 Items property:

Table({ Name: "BLDG1", Stories: 100, Material: "Wood" }, 
      { Name: "BLDG2", Stories: 200, Material: "Grass" }, 
      { Name: "BLDG3", Stories: 300, Material: "Cement" }, 
      { Name: "BLDG4", Stories: 400, Material: "Concrete" } 
)

 

And finally, setting your variable:

Set (JSONVariable, 
    JSON(
       {Buildings: ListBox2.SelectedItems.Name,
        Groups: ListBox1.SelectedItems.Name
       }
    )
)

 

Will produce:

{
    "Buildings": [
        {"Name": "BLDG1"},
        {"Name": "BLDG3"}
    ],
    "Groups": [
        {"Name": "Group1"},
        {"Name": "Group3"}
    ]
}

You will notice that the JSON function's "well formatted JSON" includes the column name of the Value in each record.  And, since it is observing the "well-formatting", it will not allow a record to have a table/array at one point and just a single string at others (single selection).

 

I would recommend in your case to not use the JSON function and instead, convert it to text.

ex.

"{""Buildings"": " & 
    If(CountRows(ListBox2.SelectedItems)>1, "[") &
    With({_bld: Concat(ListBox2.SelectedItems, """" & Name & """,")}, Left(_bld, Len(_bld)-1)) &
    If(CountRows(ListBox2.SelectedItems)>1, "]") & "," &

    Char(10) & """Groups"":" & 
    If(CountRows(ListBox1.SelectedItems)>1, "[") & 
    With({_grp: Concat(ListBox1.SelectedItems, """" & Name & """")}, Left(_grp, Len(_grp)-1)) &
    If(CountRows(ListBox1.SelectedItems)>1, "]") &
"}"

The above will produce the following (multiple selections):

{"Buildings": ["BLDG1","BLDG3"],
"Groups":["Group1""Group3]}

And Single Selection in Building:

{"Buildings": "BLDG1",
"Groups":["Group1""Group3]}

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thank you very much. 

This was a big help!

 

The JSON objects are to be used as splats for PowerShell runbooks, so we need the not well-formatted version, to reduce complexity further down the line.

 

As a workaround we are already building our JSON objects manually by concatenating the listboxitems, as you also suggested.
I was just hoping this was only a necessity until we got the JSON() function to do as we would like.

But looks like we'll keep building them manually.



Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,950)