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