Generating a JSON file in a workflow

[Hussein_Osseily]

Hello Community, 

I am trying to generate a JSON file in a workflow , "generate a document", but the result is most likely incorrect, any help would be really appreciated. 

Example:

Table1:

ID	Client	Rep
1A	A	H
2B	B	H
3C	C	I

Template: 

 <<Start: FILTER("Table1", [Rep] = "H")>>
{
   "ID": "<<[ID]>>",
   "Client": "<<[Client]>>",
   "Rep": "<<[Rep]>>"
}
   <<END>>

Result: 

{
"ID": "1A",
"Client": "A",
"Rep": "H"
}
{
"ID": "2B",
"Client": "B",
"Rep": "H"
}

Shoudn't the multiple objects be either enclosed within an array [ ] or separated by commas , if they are part of the same array or object as below ? 

[
  {
    "ID": "1A",
    "Client": "A",
    "Rep": "H"
  },
  {
    "ID": "2B",
    "Client": "B",
    "Rep": "H"
  }
]

I tried adding the [   ] in the template but the , between the objects are still not appearing

[scott192 · Accepted solution]

I can see that you have had a few replies to this and I know getting JSON templating correct can be difficult at first.  So my answer is to paste a JSON template that I created for a specific job.  It has Main, sub and sub sub records.  I hope you can study it and see how it all fits together.

{
    "Quotes":[<<Start: Select(QuotesV2[QuoteID],[QuoteID] = [_THISROW].[QuoteID],TRUE)>>
    {
    "QuoteFolder":"<<[FolderID]>>",
    "QuoteDocID":"<<[QuoteDocID]>>",
    "Filename":"<<Concatenate('Quote ',[TFPReference],' V',[QuoteVersion] + 1)>>",
    "QuoteRef": "<<[TFPReference]>>",
    "EnquiryRef":"<<[EnquiryID].[EnquiryRef]>>",
    "InitialInformation": "<<[InitialInformation]>>",
    "Venue": "<<[Venue]>>",   
    "QuoteFor": "<<[ContactName]>>",
    "Email": "<<[ContactEmail]>>",
    "QuoteGenerated": "<<TEXT([LastQuoteGen],'DDD MMM dd yyyy')>>",  
    "PreDiscountTotal":"<<[PreDiscTot]>>",
    "QuoteDiscount":"<<[Discount]>>",
    "DiscountValue":"<<[DiscountValue]>>",
    "QuoteTotal":"<<[QuoteTotal]>>",
    "QuoteLines":[<<Start: [Related QuoteLines]>>
      {
         "ItemName": "<<[ItemName]>>",   
         "Description": "<<[Description]>>",    
         "Amount": "<<[Amount]>>", 
         "PreDiscountCost": "<<[PreDiscountCost]>>",
         "PreDiscountTotal": "<<[PreDiscountTotal]>>",
         "Discount": "<<[Discount]>>",
         "DiscountValue": "<<[DiscountValue]>>",   
         "PostDiscountCost": "<<[PostDiscountCost]>>",        
         "TotalCost": "<<[TotalCost]>>",
         "Therapy":[<<Start: [Related Itemisation]>>
        {
            "ItemName": "<<[ItemName]>>",
            "Description": "<<[Description]>>",
            "UnitCost": "<<[UnitCost]>>",
            "Unit": "<<[Unit]>>",
            "Amount": "<<[Amount]>>",
            "Length": "<<[Length]>>",
            "Workers": "<<[Workers]>>",
            "Units": "<<[Units]>>",
            "Cost": "<<[Cost]>>",
            "RowCost": "<<[RowCost]>>",
            "TimeCost": "<<[TimeCost]>>",
            "Journeys": "<<[Journeys]>>",
            "JL": "<<[JL]>>"          
        }
        <<End>>
        ],
        "AdditionalExpenses":[<<Start: [Related AdditionalExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Amount": "<<[Amount]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ],
        "TravelExpenses":
        [<<Start: [Related TravelExpenses]>>
        {
               "ItemName": "<<[ItemName]>>",
               "Description": "<<[Description]>>",
               "UnitCost": "<<[UnitCost]>>",
               "Unit": "<<[Unit]>>",
               "Journeys": "<<[Journeys]>>",        
               "Length": "<<[Length]>>",
               "Workers": "<<[Workers]>>",
               "Cost": "<<[Cost]>>"
        }
        <<End>>
        ]
      }
      <<End>>
      ]
    }
    <<End>>
    ]
}