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})
)
);
Solved! Go to Solution.
@Anonymous
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.
@Anonymous
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.
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.
@Anonymous
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?
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
}
]
@Anonymous
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)))
)
@Anonymous
Yes...see that starts with an array/table of records. The last post I replied should fix that.
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'.'.
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}
User | Count |
---|---|
157 | |
93 | |
78 | |
73 | |
57 |
User | Count |
---|---|
201 | |
166 | |
98 | |
94 | |
79 |