Converting JSON Data for use in Power apps

Like (0) Share

Report

Posted on 31 Oct 2024 03:44:25 by CU-19080002-10

Hi, I Have a application where users create a quote with various line items. To save space in the Sharepoint list, The collection is saved as JSON data: this is how it is patched to the list for drafts templates and Completed Quotes
Set(
serializedQuoteData,
JSON(
ForAll(
QuoteDataCollection,
{
'Participant Name': 'Participant Name',
'NDIS Number': 'NDIS Number',
Day: Day,
Occurrence: {Value: Occurrence.DisplayTitle},
Year: Year,
Frequency: {Value: Frequency.Value},
'Custom Frequency': 'Custom Frequency',
'Service Start Date': DateValue('Service Start'),
'Service End Date': DateValue('Service End'),
Weeks: Value(Weeks),
'Start Time': 'Start Time',
'Finish Time': 'Finish Time',
Hours: Value(Hours),
Kilometers: Kms,
'Support Item': 'Support Item',
Ratio: {Value: Ratio},
Rate: Rate,
'Custom Rate': Value('Custom Rate'),
'Number Of Staff': Value('Num Staff'),
'Description Of Service': Desc,
'Assigned Staff Member': Assigned,
'Total Cost': Value(Total)
}
),
JSONFormat.Compact// Compact JSON format
)
);
// Step 3: Patch to SharePoint, including dynamic assignment of Status, Quote Type, and ID
Patch(
'NDIS Quote Tool Quote Data',// SharePoint list name
Defaults('NDIS Quote Tool Quote Data'),// Create new record
{
'Participant Name': ParticipantName.Value,
'NDIS Number': NDISNumber.Value,
'Selected Service': SelectedService.Value,
Day: ForAll(
FormattedDaysTable,
{Value: Value}
),
Occurrence: {Value: Occurrence.Selected.DisplayTitle},
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start Date': DateValue(ServiceStart.SelectedDate),
'Service End Date': DateValue(ServiceEnd.SelectedDate),
Weeks: QuoteWeeks.Value,
'Start Time': Text(
StartTime.Value,
"HH:mm"
),
'Finish Time': Text(
FinishTime.Value,
"HH:mm"
),
Hours: Value(QuoteHours.Value),
Kilometers: Kms.Value,
'Support Item': NDISSupportItem.Selected.'Support Item',
Ratio: {Value: RatioSelector.Selected.Value},
Rate: Value(SupportRate.Value),
'Custom Rate': Value(CustomRate.Value),
'Number Of Staff': Value(NumOfStaff.Value),
'Description of Service': QuoteDesc.Value,
'Assigned Staff Member': AssignedStaff.Value,
'Total Cost': Value(QuoteTotal.Value),
'Item Id': uniqueDraftID,
// Unique identifier for the draft
FormData: serializedQuoteData,
// Save entire collection as JSON
// Fields for status and draft type:
Status: {Value: "Pending"},
// Draft status is Pending
'Quote Type': {Value: "Draft"},
// Quote type is set to Draft
'User Email': UserMail.Value,
'Users With Access': Concat(
ComboboxCanvas2.SelectedItems,
Mail,
" ; "
)
}
);

I am having trouble retrieving this data to populate the galleries when the user selects a draft, template or completed quote.
I set up a flow to retrieve the data - The flow is as follows:

Trigger: When Power Apps calls a flow - Input is ItemId (This is the id of the item in the list that is being selected - Set(ItemId, SelectedItem.'Item Id')

Get Items: I retrieve the item from the SharePoint list path with a query field_37 eq ItemId so it pulls the data from the correct item.

Initialize Variable: Here i initialize an array variable to collect the Parsed data into (in case its needed)
Apply to each: Inside this loop i have:
Compose: From the FormData column of the Get Items output (Where the JSON is stored)

Parse JSON: Input is the Ouputs of the Compose action with the schema
Select: Here i map out the exact fields i want to retrieve

Respond: I then respond to Power apps with the outputs of the Select Action

I then run the flow:

Set(SelectedItem, ThisItem);

Set(ItemId, SelectedItem.'Item Id');

Set(
MyResponse,
ParsedJSON.Run(ItemId)
);

The flow runs successfully however I am having trouble retrieving a single item from the returned data. I can see the data is output as an array of objects when testing with a label using MyRepsonse.parsedata in the text but nothing seems to be allowing me to retrieve the individual items from the data in the gallery. When entering MyResonse into the items property of the gallery, the individual items are not found such as thiItem.'Support Item' etc. When entering MyResponse.parseddata in the items property it is expecting a Table value but getting text.

Is there something in this process, I am doing wrong? Happy to follow up with more information, it's just a lengthy post to try and explain everything.

Categories:

General Questions

All responses (11)

Answers (0)

DBO_DV 4,625 Super User 2025 Season 1 on 04 Nov 2024 at 06:58:49

Like (0)

Report

Converting JSON Data for use in Power apps

Hey, Could you look into the Variable to see the column name?

Maybe there was an issue with the naming.

Just look if yoy can find the data tou need in any coulmn.

If this solvede your porblem please accept it as solution so others can find it as well.
If it helped in any other way consider liking it so we can keep supporting eachother.
CU-19080002-10 20 on 01 Nov 2024 at 01:44:38

Like (0)

Report

Converting JSON Data for use in Power apps

This seems to work:
ClearCollect(
retrievedData,
ForAll(
Table(
ParseJSON(ParsedJSON.Run(ItemId).parseddata)
),
ForAll(
Table(Value.body), // Access the body field from the parsed JSON
{
'Participant Name': Text(Value.'Participant Name'),
'NDIS Number': Text(Value.'NDIS Number'),
Day: Value.Day, // Handle array of days
Occurrence: Text(Value.Occurrence.Value),
Year: Text(Value.Year),
Frequency: Text(Value.Frquency.Value),
'Custom Frequency': Text(Value.'Custom Frequency'),
'Service Start Date': DateValue(Value.'Start Date'),
'Service End Date': DateValue(Value.'End Date'),
'Start Time': Text(Value.'Start Time'),
'Finish Time': Text(Value.'Finish Time'),
Kilometers: Text(Value.Kms),
'Support Item': Text(Value.'Support Item'),
Ratio: Text(Value.Ratio.Value),
Rate: Value(Value.Rate),
Hours: Value(Value.Hours),
'Total Cost': Value(Value.Total)
}
)
)
);

I can see the retrievedData Table now in the collections however it is still not finding the individual items such as ThisItem.'Support Item' etc.
CU-19080002-10 20 on 01 Nov 2024 at 00:13:18

Like (0)

Report

Converting JSON Data for use in Power apps

Ok so i have followed the instructions in the link you provided. All was looking good until i ran the flow and then got this error in the formula:

If i look at the output of the respond action of my flow: my JSON data is an array

Compared to the output shown in the tutorial:

The JSON data is single string.

Am I setting the JSON wrong in my app?
CU-19080002-10 20 on 31 Oct 2024 at 22:28:46

Like (0)

Report

Converting JSON Data for use in Power apps

User Defined Types wasn't turned on, i have now turned it on. I'll follow the instructions in the link now to see how it goes.
Suggested answer

DBO_DV 4,625 Super User 2025 Season 1 on 31 Oct 2024 at 13:06:16

Like (0)

Report

Converting JSON Data for use in Power apps

Okay have you turned on the option of user defined types?

Could you try to follow this blog? Parse JSON Power Automate Function: Get Collection From A Flow

If this solvede your porblem please accept it as solution so others can find it as well.
If it helped in any other way consider liking it so we can keep supporting eachother.
CU-19080002-10 20 on 31 Oct 2024 at 12:37:18

Like (0)

Report

Converting JSON Data for use in Power apps

Unfortunately Still the same error:
Suggested answer

DBO_DV 4,625 Super User 2025 Season 1 on 31 Oct 2024 at 11:32:50

Like (0)

Report
Converting JSON Data for use in Power apps

Could you try to simple delete the table portoin?
Set( MyResponse, ParseJSON(ParsedJSON.Run(ItemId)) );

If this solvede your porblem please accept it as solution so others can find it as well.
If it helped in any other way consider liking it so we can keep supporting eachother.
CU-19080002-10 20 on 31 Oct 2024 at 11:22:34

Like (0)

Report

Converting JSON Data for use in Power apps

I gave that a go however I am getting this error.
Suggested answer

DBO_DV 4,625 Super User 2025 Season 1 on 31 Oct 2024 at 09:27:18

Like (0)

Report
Converting JSON Data for use in Power apps

Yes perfect. This is a simple JSON

you need to use the ParseJSON function on either the answer directly or on your variable

Set( MyResponse, Table(ParseJSON(ParsedJSON.Run(ItemId)).body)) );

Let me know if it worked

If this solvede your porblem please accept it as solution so others can find it as well.
If it helped in any other way consider liking it so we can keep supporting eachother.
CU-19080002-10 20 on 31 Oct 2024 at 09:10:11

Like (0)

Report

Converting JSON Data for use in Power apps

@DBO_DV

Did you mean the MyResponse variable?

This is what is in the variable.

[{"body":[{"NDIS Number":"466466466","Particpant Name":null,"Day":null,"Occurrence":{"Value":null},"Year":{},"Frquency":{"Value":"Weekly"},"Ratio":{"Value":"1:1"},"Rate":"$57.23","Total":null,"Start Time":"09:00","Finish Time":"15:00","Start Date":"2024-03-04","End Date":"2025-03-03","Kms":"","Support Item":"01_004_0107_1_1 - Assistance with Personal Domestic Activities","Custom Rate":null,"Custom Frequency":"","Assigned":"","Desc":"","Number Of Staff":null,"Hours":288},{"NDIS Number":"466466466","Particpant Name":null,"Day":[{"Value":"Monday"}],"Occurrence":{"Value":null},"Year":{},"Frquency":{"Value":"Weekly"},"Ratio":{"Value":"1:1"},"Rate":"$67.56","Total":null,"Start Time":"09:00","Finish Time":"15:00","Start Date":"2024-03-04","End Date":"2025-03-03","Kms":"","Support Item":"01_011_0107_1_1 - Assistance With Self-Care Activities - Standard - Weekday Daytime - Non-Face-To-Face","Custom Rate":null,"Custom Frequency":"","Assigned":"","Desc":"","Number Of Staff":null,"Hours":288},{"NDIS Number":"466466466","Particpant Name":null,"Day":[{"Value":"Monday"}],"Occurrence":{"Value":null},"Year":{},"Frquency":{"Value":"Weekly"},"Ratio":{"Value":"1:1"},"Rate":"$67.56","Total":null,"Start Time":"09:00","Finish Time":"15:00","Start Date":"2024-03-04","End Date":"2025-03-03","Kms":"","Support Item":"01_200_0115_1_1 - Assistance With Self-Care Activities in a STA - Weekday Daytime","Custom Rate":null,"Custom Frequency":"","Assigned":"","Desc":"","Number Of Staff":null,"Hours":288}]}]