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
Super User

@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

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

View solution in original post

12 REPLIES 12
RandyHayes
Super User
Super User

@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

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

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
Super User

@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

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

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
Super User

@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

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

@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

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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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