Convert multi-level table to nested JSON string?

Kinetic ERP
1

Hey folks,

I have a table like this with repeating order and order line level data with each order release row.

image

I’d like to format the JSON string where it shows multiple nested levels instead of just repeating lines like this:

  {
    "Order": 10004,
    "Customer": 466,
    "Lines": [
      {
        "Line": 1,
        "Part": "MD10000000",
        "OrderQty": 10.0,
        "Releases": [
          {
            "Rel": 1,
            "OurRequestedQty": 10.0,
            "ShipBy": "11/30/2024"
          }
        ]
      }
    ]
  },

I can manually build the string, but it’s really a pain – and it will change.

Anyone have an automagic way to build that string before serializing it?

I’ve done this with AI, but don’t have it available inside the Epicor function.

Thanks,

Joe

1 Like
2

I’ll take a look when I get back to a pc.

3

Cool, thanks.

4

Okay, so in multiple iterations, I persuaded ChatGPT to rewrite my script and spit out the one below.

The EFx requires:

image

And

image

The BAQ:

image

// Convert BAQ data table to JSON string
var context = Ice.Services.ContextFactory.CreateContext<ErpContext>();
jsonOrderSet = "";

using (var boDynamicQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(context))
{
    // BAQ filtering goes here

    // Load results from BAQ
    DataSet dsResults = boDynamicQuery.ExecuteByID("JT_JSONBAQTEST", dsQueryExecution);

    var orderSet = dsResults.Tables["Results"];

    // Flatten rows into dictionaries
    var rows = new List<Dictionary<string, object>>();
    foreach (DataRow row in orderSet.Rows)
    {
        var rowDict = new Dictionary<string, object>();
        foreach (DataColumn col in orderSet.Columns)
        {
            rowDict[col.ColumnName] = row[col];
        }
        rows.Add(rowDict);
    }

    // Define grouping keys
    string keyOrder = "OrderHed_OrderNum"; // related fields must be grouped between OrderHed_OrderNum and OrderDtl_OrderLine in the table
    string keyLine = "OrderDtl_OrderLine"; // related fields must be grouped between OrderDtl_OrderLine and OrderRel_OrderRelNum in the table
    string keyRelease = "OrderRel_OrderRelNum"; // related fields must follow rderRel_OrderRelNum

    // Get column order from the dataset
    var columnNames = orderSet.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();

    int idxOrder = columnNames.IndexOf(keyOrder);
    int idxLine = columnNames.IndexOf(keyLine);
    int idxRelease = columnNames.IndexOf(keyRelease);

    // Assign field ranges
    var orderLevelFields = columnNames.GetRange(idxOrder + 1, idxLine - idxOrder - 1);
    var lineLevelFields = columnNames.GetRange(idxLine + 1, idxRelease - idxLine - 1);
    var releaseLevelFields = columnNames.GetRange(idxRelease + 1, columnNames.Count - idxRelease - 1);

    // Group data
    var result = rows
        .GroupBy(row => row[keyOrder])
        .Select(orderGroup =>
        {
            var orderDict = new Dictionary<string, object>
            {
                { "OrderNum", orderGroup.Key }
            };

            // Add Order-level fields
            foreach (var col in orderLevelFields)
                orderDict[col] = orderGroup.First()[col];

            var lines = orderGroup
                .GroupBy(row => row[keyLine])
                .Select(lineGroup =>
                {
                    var lineDict = new Dictionary<string, object>
                    {
                        { "OrderLine", lineGroup.Key }
                    };

                    // Add Line-level fields
                    foreach (var col in lineLevelFields)
                        lineDict[col] = lineGroup.First()[col];

                    // Build Releases
                    var releases = lineGroup.Select(relRow =>
                    {
                        var releaseDict = new Dictionary<string, object>
                        {
                            { "OrderRelNum", relRow[keyRelease] }
                        };

                        foreach (var col in releaseLevelFields)
                        {
                            var val = relRow[col];
                            releaseDict[col] = val is DateTime dt ? dt.ToString("MM/dd/yyyy") : val;
                        }

                        return releaseDict;
                    }).ToList();

                    lineDict["Releases"] = releases;
                    return lineDict;
                }).ToList();

            orderDict["Lines"] = lines;
            return orderDict;
        }).ToList();

    // Serialize to indented JSON
    jsonOrderSet = Newtonsoft.Json.JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented);
}

This should work on POs, too, by changing OrderHed_OrderNum to POHead.PONum, etc. Or any three-level set of data in the BAQ results table.

Note that fields related to a level should be located between the two level keys (i.e. OrderNum and OrderLine).

I’d be interested in your results, or if you have an easier or more flexible method.

Thanks,

Joe

1 Like