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.
