Nested Tables Help

[Anonymous]

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

[RandyHayes · Accepted 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]

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]

@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?

[Anonymous]

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

[Anonymous]

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]

@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)))
)

[RandyHayes]

@Anonymous 

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

[Anonymous]

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'.'.

[Anonymous]

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}