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})
)
);

12 REPLIES 12
asetser
Frequent Visitor

I fixed it. Thank you SO much!! You're a saint, an angel and my savior!!! THANK YOU!!!!!

RandyHayes
Super User
Super User

@asetser 

No problem!!  What was it?  A schema issue?

_____________________________________________________________________________________
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
Frequent Visitor

My parse schema still had the outer brackets. Took them out and it was able to parse successfully!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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,517)