cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asetser
Frequent Visitor

Nested Tables Help

In desperate need of help build and populating a nested table with 3 levels. When a tech is logged in to a dispatch, they can work on multiple pieces of equipment. Each piece of equipment can have multiple pieces of materials used on it. I need to create a nested table where there is 1 dispatch in the main collection, that collection has a field called TechNotes. Tech notes has 10 columns that needs to hold all of the notes for that 1 dispatch. One of the columns in the TechNotes table is MaterialsUsed. MaterialsUsed has 4 columns and holds all of the materials used on the piece of equipment that is associated with that TechNote item.

 

I need this in nested tables without HTML so that I can convert it to JSON to send to a flow to fill out a Word Template.

I can't seem to successfully do this....

TRY #1 - Creates a new row at every level (i.e. 3 pieces of material used on 1 piece of equipments & creates 3 lines on each table instead of 1 line on the Dispatch table and 1 line on the TechNotes table and 3 lines on the MaterialsUsed table):

ForAll(Filter('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID)
, ForAll(Filter('[dbo].[DailySWO_DailyTechNotes]', NoteAssigneeID = DispatchAssigneeID)
, ForAll(Filter('[dbo].[DailySWO_Materials]', MaterialAssigneeID = NoteAssigneeID && MaterialEquipmentID = NoteEquipmentID)
, Collect(CollDispatchSWO, {AssigneeID: DispatchAssigneeID, DisWorkDT: Today(), TechName: User().FullName, DisDispatchNBR: DispatchNumber, AfterhoursID: AfterhoursID,
DisDispatchType: DispatchType, DisSiteNBR: SiteNumber, DisSiteNM: SiteName, DisSiteAddress: SiteAddress, DispatchInfo: DispatchDetails, TorchCB: TorchCB,
VacuumCB: VacuumCB, ReclaimerCB: ReclaimerCB, NitrogenCB: NitrogenCB, TerminalsCB: WireNutsCB, RefrigCapCB: RefrigCapCB, CallSTS: CallSTS, QuoteSTS: QuoteSTS,
CustomerSign: CustSign.Image, CustomerNM: CustSign_CustNM.Text, CustomerEmail: CustSign_CustEmail.Text, SendCustEmail: If(CustSign_CustEmail_YesCB.Value = true, 1, 0),
TechSign: TechSignature.Image, StartTM: TechSign_StartTM.Text, EndTM: TechSign_EndTM.Text, TotalHours: TechSign_TotalHours.Text, PayRate: TechSign_PayRate.Text,
TechNotes: Table({TechNote: TechNote, AreaServed: AreaServed, EquipID: NoteEquipmentID, EquipMake: EquipMake, EquipModel: EquipModel, EquipSerialNBR: SerialNBR,
EquipType: EquipType, UnitNBR: UnitNBR, UnitSTS: UnitSTS, RefrigType: RefrigType, RefrigRemoved: Removed, RefrigAdded: Added,
MaterialsUsed: Table({MatEquipID: MaterialEquipmentID, MatPO: MaterialPO, MatQTY: MaterialQTY, MatDESC: MaterialsDESC})})}))));

 

 

 

 

TRY #2 - Notes and Materials are in different collections:

ClearCollect(CollDispatchSWO, {AssigneeID: SelectedAssigneeID, DisWorkDT: Today(), TechName: User().FullName, DisDispatchNBR: SelectedDispatchNBR, AfterhoursID: SelectedDispatchOT, DisDispatchType: SelectedDispatchType, DisSiteNBR: SelectedSiteNBR, DisSiteNM: SelectedSiteNM, DisSiteAddress: Concatenate(SelectedSiteStreetAddress, ", ", SelectedSiteCity, ", ", SelectedSiteState, ", ", Text(SelectedSiteZipCD)), DispatchInfo: SelectedDispatchDetails, TorchCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, TorchCB), VacuumCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, VacuumCB), ReclaimerCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, ReclaimerCB), NitrogenCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, NitrogenCB), TerminalsCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, WireNutsCB), RefrigCapCB: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, RefrigCapCB), CallSTS: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, CallSTS), QuoteSTS: LookUp('[dbo].[DailySWO_DailyDispatchWorked]', DispatchAssigneeID = SelectedAssigneeID, QuoteSTS), CustomerSign: CustSign.Image, CustomerNM: CustSign_CustNM.Text, CustomerEmail: CustSign_CustEmail.Text, SendCustEmail: If(CustSign_CustEmail_YesCB.Value = true, 1, 0), TechSign: TechSignature.Image, StartTM: TechSign_StartTM.Text, EndTM: TechSign_EndTM.Text, TotalHours: TechSign_TotalHours.Text, PayRate: TechSign_PayRate.Text, TechNotes: Table({TechNote: "", AreaServed: "", EquipID: 0, EquipMake: "", EquipModel: "", EquipSerialNBR: "", EquipType: "", UnitNBR: "", UnitSTS: "", RefrigType: "", RefrigRemoved: "", RefrigAdded: "", MaterialsUsed: Table({MatEquipID: 0, MatPO: "", MatQTY: 0, MatDESC: ""})})});


ForAll(
CollDispatchSWO,
ForAll(Filter('[dbo].[DailySWO_DailyTechNotes]', NoteAssigneeID = SelectedAssigneeID),
Collect(TechNotes,{TechNote: TechNote, AreaServed: AreaServed, EquipID: NoteEquipmentID, EquipMake: EquipMake, EquipModel: EquipModel, EquipSerialNBR: SerialNBR, EquipType: EquipType, UnitNBR: UnitNBR, UnitSTS: UnitSTS, RefrigType: RefrigType, RefrigRemoved: Removed, RefrigAdded: Added, MaterialsUsed: Table({MatEquipID: 0,
MatPO: "", MatQTY: 0, MatDESC: ""})})
)
);

ForAll(
TechNotes,
ForAll(Filter('[dbo].[DailySWO_Materials]', MaterialAssigneeID = SelectedAssigneeID && MaterialEquipmentID = EquipID),
Collect(MaterialsUsed,{MatEquipID: MaterialEquipmentID, MatPO: MaterialPO, MatQTY: MaterialQTY, MatDESC: MaterialsDESC})
)
);

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@asetser 

The following formula will give you the JSON data you are looking for.

With({_data:    
    ForAll(
        Filter('[dbo].[DailySWO_DailyDispatchWorked]', 
            DispatchAssigneeID = SelectedAssigneeID
        ) As _disp,
    
        {AssigneeID: _disp.DispatchAssigneeID, 
         DisWorkDT: Today(), 
         TechName: User().FullName, 
         DisDispatchNBR: _disp.DispatchNumber, 
         AfterhoursID: _disp.AfterhoursID,
         DisDispatchType: _disp.DispatchType, 
         DisSiteNBR: _disp.SiteNumber, 
         DisSiteNM: _disp.SiteName, 
         DisSiteAddress: _disp.SiteAddress, 
         DispatchInfo: _disp.DispatchDetails, 
         TorchCB: _disp.TorchCB,
         VacuumCB: _disp.VacuumCB, 
         ReclaimerCB: _disp.ReclaimerCB, 
         NitrogenCB: _disp.NitrogenCB, 
         TerminalsCB: _disp.WireNutsCB, 
         RefrigCapCB: _disp.RefrigCapCB, 
         CallSTS: _disp.CallSTS, 
         QuoteSTS: _disp.QuoteSTS,
         CustomerSign: CustSign.Image, 
         CustomerNM: CustSign_CustNM.Text, 
         CustomerEmail: CustSign_CustEmail.Text, 
         SendCustEmail: If(CustSign_CustEmail_YesCB.Value, 1, 0),
         TechSign: TechSignature.Image, 
         StartTM: TechSign_StartTM.Text, 
         EndTM: TechSign_EndTM.Text, 
         TotalHours: TechSign_TotalHours.Text, 
         PayRate: TechSign_PayRate.Text,
         TechNotes: ForAll(
                        Filter('[dbo].[DailySWO_DailyTechNotes]', 
                            NoteAssigneeID = _disp.DispatchAssigneeID
                        ) As _tn,
                        {TechNote: _tn.TechNote, 
                         AreaServed: _tn.AreaServed, 
                         EquipID: _tn.NoteEquipmentID, 
                         EquipMake: _tn.EquipMake, 
                         EquipModel: _tn.EquipModel, 
                         EquipSerialNBR: _tn.SerialNBR,
                         EquipType: _tn.EquipType, 
                         UnitNBR: _tn.UnitNBR, 
                         UnitSTS: _tn.UnitSTS, 
                         RefrigType: _tn.RefrigType, 
                         RefrigRemoved: _tn.Removed, 
                         RefrigAdded: _tn.Added,
                         MaterialsUsed: ForAll(
                                            Filter('[dbo].[DailySWO_Materials]', 
                                                MaterialAssigneeID = _tn.NoteAssigneeID && MaterialEquipmentID = _tn.NoteEquipmentID
                                            ) As _mat,
                                            {MatEquipID: _mat.MaterialEquipmentID, 
                                             MatPO: _mat.MaterialPO, 
                                             MatQTY: _mat.MaterialQTY, 
                                             MatDESC: _mat.MaterialsDESC
                                            }
                                        )
                        }
                   )
         }
    )},
    
    JSON(_data)
)

 

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

View solution in original post

12 REPLIES 12
RandyHayes
Super User III
Super User III

@asetser 

The following formula will give you the JSON data you are looking for.

With({_data:    
    ForAll(
        Filter('[dbo].[DailySWO_DailyDispatchWorked]', 
            DispatchAssigneeID = SelectedAssigneeID
        ) As _disp,
    
        {AssigneeID: _disp.DispatchAssigneeID, 
         DisWorkDT: Today(), 
         TechName: User().FullName, 
         DisDispatchNBR: _disp.DispatchNumber, 
         AfterhoursID: _disp.AfterhoursID,
         DisDispatchType: _disp.DispatchType, 
         DisSiteNBR: _disp.SiteNumber, 
         DisSiteNM: _disp.SiteName, 
         DisSiteAddress: _disp.SiteAddress, 
         DispatchInfo: _disp.DispatchDetails, 
         TorchCB: _disp.TorchCB,
         VacuumCB: _disp.VacuumCB, 
         ReclaimerCB: _disp.ReclaimerCB, 
         NitrogenCB: _disp.NitrogenCB, 
         TerminalsCB: _disp.WireNutsCB, 
         RefrigCapCB: _disp.RefrigCapCB, 
         CallSTS: _disp.CallSTS, 
         QuoteSTS: _disp.QuoteSTS,
         CustomerSign: CustSign.Image, 
         CustomerNM: CustSign_CustNM.Text, 
         CustomerEmail: CustSign_CustEmail.Text, 
         SendCustEmail: If(CustSign_CustEmail_YesCB.Value, 1, 0),
         TechSign: TechSignature.Image, 
         StartTM: TechSign_StartTM.Text, 
         EndTM: TechSign_EndTM.Text, 
         TotalHours: TechSign_TotalHours.Text, 
         PayRate: TechSign_PayRate.Text,
         TechNotes: ForAll(
                        Filter('[dbo].[DailySWO_DailyTechNotes]', 
                            NoteAssigneeID = _disp.DispatchAssigneeID
                        ) As _tn,
                        {TechNote: _tn.TechNote, 
                         AreaServed: _tn.AreaServed, 
                         EquipID: _tn.NoteEquipmentID, 
                         EquipMake: _tn.EquipMake, 
                         EquipModel: _tn.EquipModel, 
                         EquipSerialNBR: _tn.SerialNBR,
                         EquipType: _tn.EquipType, 
                         UnitNBR: _tn.UnitNBR, 
                         UnitSTS: _tn.UnitSTS, 
                         RefrigType: _tn.RefrigType, 
                         RefrigRemoved: _tn.Removed, 
                         RefrigAdded: _tn.Added,
                         MaterialsUsed: ForAll(
                                            Filter('[dbo].[DailySWO_Materials]', 
                                                MaterialAssigneeID = _tn.NoteAssigneeID && MaterialEquipmentID = _tn.NoteEquipmentID
                                            ) As _mat,
                                            {MatEquipID: _mat.MaterialEquipmentID, 
                                             MatPO: _mat.MaterialPO, 
                                             MatQTY: _mat.MaterialQTY, 
                                             MatDESC: _mat.MaterialsDESC
                                            }
                                        )
                        }
                   )
         }
    )},
    
    JSON(_data)
)

 

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

View solution in original post

You are a SAVIOR!!!

One other issue now.. when I try to use that to send to a flow, I get errors. Is this something that you can help me with?

I had to turn the bottom of what you sent to this:

Filter('[dbo].[DailySWO_Materials]',
MaterialAssigneeID = _tn.NoteAssigneeID && MaterialEquipmentID = _tn.NoteEquipmentID
) As _mat,
{MatEquipID: _mat.MaterialEquipmentID,
MatPO: _mat.MaterialPO,
MatQTY: _mat.MaterialQTY,
MatDESC: _mat.MaterialsDESC
}
)
}
)
}
)},

Set(varJSON, JSON(_data))
);

 

So I could send it to my flow:

'REAL-SWO'.Run(varJSON);

 

But the flow gives me the following error message:
Unable to process template language expressions in action 'Parse_JSON' inputs at line '1' and column '2397': 'Unexpected token encountered when reading schema. Expected StartObject, Boolean, got StartArray. Path 'schema'.'.

 

I have it Compose first (so I could get the input), which I copied and used to populate the Parse JSON in the flow.

RandyHayes
Super User III
Super User III

@asetser 

You should be able to use the following change to the bottom of the formula:

         }
    )},
    
    'REAL-SWO'.Run(JSON(_data))
)

Stay away from variables that aren't needed.

 

Now, as for the error....this is telling you that your flow is expecting a JSON object (record) and it got an array (table), which is correct as far as the formula is concerned as you are sending it a table.

 

So, the question then is this...are you intending to process a table of records in your flow or just a single record?  

And, does the filter of the dispatch table by DispatchAssigneeID = SelectedAssigneeID produce one or more records?

_____________________________________________________________________________________
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

The highest level of data, which is the dispatch/assignee level, will always be 1. The Notes and Materials levels can have multiple each.

This is the output that gets sent to the Flow based on what you gave me (just in case it helps - it's test data only):
[
{
"AfterhoursID": 0,
"AssigneeID": 45404,
"CallSTS": "Complete",
"CustomerEmail": "testcust@testemail.com",
"CustomerNM": "Test Customer",
"DisDispatchNBR": "90702",
"DisDispatchType": "1",
"DisSiteAddress": "3202 N Euclid Ave, Indianapolis, IN 46218",
"DisSiteNBR": "HBH001",
"DisSiteNM": "Him By Her",
"DisWorkDT": "2021-04-12",
"DispatchInfo": "Eniter 3rd floor has no cool ",
"EndTM": "11 am",
"NitrogenCB": 0,
"PayRate": "Regular",
"QuoteSTS": "",
"ReclaimerCB": 0,
"RefrigCapCB": 1,
"SendCustEmail": 1,
"StartTM": "8 am",
"TechName": "Angie Setser",
"TechNotes": [
{
"AreaServed": null,
"EquipID": 43884,
"EquipMake": "Built up",
"EquipModel": "None",
"EquipSerialNBR": "None",
"EquipType": "Air Handler",
"MaterialsUsed": [
{
"MatDESC": "Test Material 1",
"MatEquipID": 43884,
"MatPO": "Test",
"MatQTY": 1
},
{
"MatDESC": "Test Material 2",
"MatEquipID": 43884,
"MatPO": "Test",
"MatQTY": 1
},
{
"MatDESC": "T6 Pro Programmable Thermostat -",
"MatEquipID": 43884,
"MatPO": "L49-157",
"MatQTY": 1
}
],
"RefrigAdded": "",
"RefrigRemoved": "",
"RefrigType": "",
"TechNote": "Test note to build SWO. ",
"UnitNBR": "-fcu01",
"UnitSTS": "Operational"
}
],
"TerminalsCB": 0,
"TorchCB": 1,
"TotalHours": "3",
"VacuumCB": 1
}
]

RandyHayes
Super User III
Super User III

@asetser 

Then change the formula to the following:

With({_data:    
    ForAll(
        Filter('[dbo].[DailySWO_DailyDispatchWorked]', 
            DispatchAssigneeID = SelectedAssigneeID
        ) As _disp,
    
        {AssigneeID: _disp.DispatchAssigneeID, 
         DisWorkDT: Today(), 
         TechName: User().FullName, 
         DisDispatchNBR: _disp.DispatchNumber, 
         AfterhoursID: _disp.AfterhoursID,
         DisDispatchType: _disp.DispatchType, 
         DisSiteNBR: _disp.SiteNumber, 
         DisSiteNM: _disp.SiteName, 
         DisSiteAddress: _disp.SiteAddress, 
         DispatchInfo: _disp.DispatchDetails, 
         TorchCB: _disp.TorchCB,
         VacuumCB: _disp.VacuumCB, 
         ReclaimerCB: _disp.ReclaimerCB, 
         NitrogenCB: _disp.NitrogenCB, 
         TerminalsCB: _disp.WireNutsCB, 
         RefrigCapCB: _disp.RefrigCapCB, 
         CallSTS: _disp.CallSTS, 
         QuoteSTS: _disp.QuoteSTS,
         CustomerSign: CustSign.Image, 
         CustomerNM: CustSign_CustNM.Text, 
         CustomerEmail: CustSign_CustEmail.Text, 
         SendCustEmail: If(CustSign_CustEmail_YesCB.Value, 1, 0),
         TechSign: TechSignature.Image, 
         StartTM: TechSign_StartTM.Text, 
         EndTM: TechSign_EndTM.Text, 
         TotalHours: TechSign_TotalHours.Text, 
         PayRate: TechSign_PayRate.Text,
         TechNotes: ForAll(
                        Filter('[dbo].[DailySWO_DailyTechNotes]', 
                            NoteAssigneeID = _disp.DispatchAssigneeID
                        ) As _tn,
                        {TechNote: _tn.TechNote, 
                         AreaServed: _tn.AreaServed, 
                         EquipID: _tn.NoteEquipmentID, 
                         EquipMake: _tn.EquipMake, 
                         EquipModel: _tn.EquipModel, 
                         EquipSerialNBR: _tn.SerialNBR,
                         EquipType: _tn.EquipType, 
                         UnitNBR: _tn.UnitNBR, 
                         UnitSTS: _tn.UnitSTS, 
                         RefrigType: _tn.RefrigType, 
                         RefrigRemoved: _tn.Removed, 
                         RefrigAdded: _tn.Added,
                         MaterialsUsed: ForAll(
                                            Filter('[dbo].[DailySWO_Materials]', 
                                                MaterialAssigneeID = _tn.NoteAssigneeID && MaterialEquipmentID = _tn.NoteEquipmentID
                                            ) As _mat,
                                            {MatEquipID: _mat.MaterialEquipmentID, 
                                             MatPO: _mat.MaterialPO, 
                                             MatQTY: _mat.MaterialQTY, 
                                             MatDESC: _mat.MaterialsDESC
                                            }
                                        )
                        }
                   )
         }
    )},
    
    'REAL-SWO'.Run(JSON(First(_data)))
)
_____________________________________________________________________________________
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

@asetser 

Yes...see that starts with an array/table of records.  The last post I replied should fix that.

_____________________________________________________________________________________
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

Still fails...
Unable to process template language expressions in action 'Parse_JSON' inputs at line '1' and column '2396': 'Unexpected token encountered when reading schema. Expected StartObject, Boolean, got StartArray. Path 'schema'.'.

asetser
Frequent Visitor

This is the output that is sent to the Flow with the change:
{"AfterhoursID":0,"AssigneeID":45404,"CallSTS":"Complete","CustomerEmail":"testcust@testemail.com","CustomerNM":"Test Customer","DisDispatchNBR":"90702","DisDispatchType":"1","DisSiteAddress":"3202 N Euclid Ave, Indianapolis, IN 46218","DisSiteNBR":"HBH001","DisSiteNM":"Him By Her","DisWorkDT":"2021-04-12","DispatchInfo":"Eniter 3rd floor has no cool ","EndTM":"11 am","NitrogenCB":0,"PayRate":"Regular","QuoteSTS":"","ReclaimerCB":0,"RefrigCapCB":1,"SendCustEmail":1,"StartTM":"8 am","TechName":"Angie Setser","TechNotes":[{"AreaServed":null,"EquipID":43884,"EquipMake":"Built up","EquipModel":"None","EquipSerialNBR":"None","EquipType":"Air Handler","MaterialsUsed":[{"MatDESC":"Test Material 1","MatEquipID":43884,"MatPO":"Test","MatQTY":1},{"MatDESC":"Test Material 2","MatEquipID":43884,"MatPO":"Test","MatQTY":1},{"MatDESC":"T6 Pro Programmable Thermostat -","MatEquipID":43884,"MatPO":"L49-157","MatQTY":1}],"RefrigAdded":"","RefrigRemoved":"","RefrigType":"","TechNote":"Test note to build SWO. ","UnitNBR":"-fcu01","UnitSTS":"Operational"}],"TerminalsCB":0,"TorchCB":1,"TotalHours":"3","VacuumCB":1}

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Top Solution Authors
Top Kudoed Authors
Users online (2,944)