Logging Changes .NET Editor - Parsing JSON Response To Front End

iqvct

I have gone through and closely followed the documentation regarding logging changes for a .NET solution shown at the following link:

https://editor.datatables.net/manual/net/events#Logging-changes

I've been able to get changes posted to a new table by adding the below to my controller:

        private void _LogChange(Database db, string action, object id, Dictionary<string, object> values, Dictionary<string, object> data = null)
        {
            var username = Environment.UserDomainName + "\\" + Environment.UserName;
            db.Insert("Changelog", new Dictionary<string, object>
            {
                { "Username", username },
                { "Action", action },
                { "ActionValue", JsonConvert.SerializeObject( values ) },
                { "ActionData", JsonConvert.SerializeObject( data ) },
                { "RowId", id },
                { "DateTime", DateTime.Now.ToString("yyyy-MM-ddTHHmmss") }
            });
        }

These entries are posted to the Changelog table.

I am attempting to create a cleaner front end rather than displaying the unformatted JSON.

Below is the API response of a view I created which takes all of the data submitted to the table shown above, and joins Username to another table to provide details on the user. Below is the first response from the api:

{"draw":null,"data":[{"DT_RowId":"row_1","VW_Changelog":{"ChangelogId":1,"Username":"BMaxwell","FirstName":"Bob","LastName":"Maxwell","Title":"Director","Email":"b.maxwell@gmail.com","Action":"edit","DateTime":"2022-05-31T161649","ActionValue":"{\"VW_Cash\":{\"CurrentForecastLc\":100}}","RowId":37226,"ActionData":"{\"DT_RowId\":\"row_37226\",\"VW_Cash\":{\"CurrentForecastLc\":100,\"CashId\":37226,\"Code\":\"ABC123\",\"CodeName\":\"ABC Company\",\"Region\":\"EMEA\",\"CountryName\":\"UnitedKingdom\"}}"}}

If we drill in to the field "ActionValue" the 3 values are table, field name, and value.

"ActionValue":"{\"VW_Cash\":{\"CurrentForecastLc\":100}}"

Without some convoluted JSON Parsing, is there an easy way to pick out these items to display in the datatable?

I tried to change the controller and encapsulate these values in brackets to create an array.

Then in the JavaScript I used:

{
"data": "VW_Changelog.ActionValue.0"
},
{
"data": "VW_Changelog.ActionValue.1"
},
{
"data": "VW_Changelog.ActionValue.2"
}

But all I would get is the character in the 0 spot, the 1 spot and the 2 spot.

I'm thinking I may need to change something in the controller so that the data is submit in a manner that's easier to parse inside the JavaScript file.

Would appreciate any suggestions on the best way to go about parsing data that has been logged.

allan

Hi,

The advantage of using JSON like that for the logging (and why I did so in that example) is that it makes the log table independent of the data structure for the host table. i.e. you could add and remove columns without needing to worry about the logging.

The disadvantage is what you are seeing - displaying the data is a little more complex.

What I would do in this case is use the xhr event to modify the JSON returned from the server to parse the internal JSON string - e.g.:

$('#myTable')
  .on('xhr.dt', function (e, s, json) {
    for (let row of json.data) {
      row.VW_Changelog.ActionData = JSON.parse(row.VW_Changelog.ActionData);
    }
  })
  .DataTable({
    ...
  });

Then in your DataTable configuration you can refer to that internal JSON using dotted Javascript notation - e.g.:

columns: [
  { data: 'VW_Changelog.ActionData.VW_Cash.CurrentForecastLc' },
  ...
]

If you want something more complex, just a ul list of the values, rather than a value per column (which again makes it easier to cope with schema changes), you could use a custom rendering function.

This is effectively what we do in CloudTables for almost exactly this sort of thing:

Hope that helps,
Allan

iqvct

Thanks Allan!

I will have to try out the above.

One thing I failed to mention. I added the same _LogChange method to many controllers, posting back to the Changelog table on each. This way I could have a single, centralized table which reports app-wide changes.

Ultimately the goal was to reference all three values of ActionValue, including table, field, and value, and place these on a single front end table for a quick overview of all changes.

I also intended to cherry pick a few same-named fields that each table has from the ActionData field as well.

So in your example provided where you referenced

columns: [
  { data: 'VW_Changelog.ActionData.VW_Cash.CurrentForecastLc' },
  ...
]

I'm not sure how this would work as VW_Cash.CurrentForecastLc is not going to be table/field combination to reference on all tables.

The output I'm looking for would be something like this:

And then if I was able to, I'd want to pick a couple fields by their same field name from the ActionData field in the Changelog table.

Intuitively I would think the first image is feasible as we're just looking for position 0, 1, and 2 of data points within the ActionValue field. The second may be a bit more difficult as we're looking for a specific key and it's corresponding value.

allan

The output I'm looking for would be something like this

That's a bit more tricky since DataTables is seeing the entries from the top level data object as the rows. Not objects inside it being rows (rows inside rows).

Are you using this table to show an individual record only? I think you'd need to make the Ajax request using fetch or $.ajax and then parse the return from the server for display in a DataTable.

Regards,
Allan

iqvct

Hi @allan ,

Thanks again for the response.

It looks like I'm not explaining this well. Maybe this visual will clarify things:

Below is the current table. The entire "ActionValue" field is displayed in a single column.

Ultimately I'd like to parse out these three items into their own columns as shown below:

Desired Table:

allan

Ah! I'm with you now. In that case, no - sorry there isn't a straightforward for DataTables to display this. It doesn't have the ability to display a JSON key, so the table and field names would be the issue.

However, the parsing for that I think shouldn't be too bad:

$('#myTable')
  .on('xhr.dt', function (e, s, json) {
    for (let row of json.data) {
      let action = JSON.parse(row.VW_Changelog.ActionValue);
      $.each(action, function (key, val) {
        row.actionTable = key;

        $.each(val, function (key2, val2) {
          row.actionField = key2;
          row.actionVal = val2;
        });
      });
    }
  })
  .DataTable({
    ...
  });

Then:

{ data: 'actionTable' },
{ data: 'actionField' },
{ data: 'actionVal' },

would do it.

Of course that makes a number of assumptions about the ActionValue but with the current structure that should do the job.

Allan

iqvct

As always, worked like a charm, thanks @allan !

Is this logic feasible to apply to ActionData that is also in the response?

"ActionData":"{\"DT_RowId\":\"row_37226\",\"VW_Cash\":{\"CurrentForecastLc\":100,\"CashId\":37226,\"Code\":\"ABC123\",\"CodeName\":\"ABC Company\",\"Region\":\"EMEA\",\"CountryName\":\"UnitedKingdom\"}}"}}

There are certain fields nested inside that are consistent on each record. For instance, Code, CodeName, Region, CountryName, etc.

Is there a way to call these out by name rather than by position?

allan

Yes, although for that you should create an array of the key / value pairs in the loop. Then use a renderer to create a ul/li list of them for display in a cell.

Allan

iqvct

Hmmm, I'm not sure I follow this last one.

I'm looking to do a similar thing as I did with ActionValue. Pull out a few of the values and place them into independent columns.

In the below string,

"ActionData":"{\"DT_RowId\":\"row_37226\",\"VW_Cash\":{\"CurrentForecastLc\":100,\"CashId\":37226,\"Code\":\"ABC123\",\"CodeName\":\"ABC Company\",\"Region\":\"EMEA\",\"CountryName\":\"UnitedKingdom\"}}"}}

everything I'm looking to pull out would be nested inside of

\"VW_Cash\"{..

which is the table name and always the second key inside of ActionData.

For instance, fields of Code, Region, CountryName should be pulled out of ActionData and brought in as independent columns.

The first 5 fields in here I've been able to pull out using the function from @allan above.

But unlike ActionValue which is always in the order of Table/Field/Value, ActionData does not have a consistent sequence which is why I would need to reference values by their key names, not positions.

From what I see, the data inside ActionData is always returned in the structure of:

ActionData: { Key 1 : Value 1 , Key 2 { Key 2.1 : Value 2.1 , Key 2.2 : Value 2.2 , Key 2.3 : Value 2.3... }}

Key 2 is always the table name, and then I'm looking to pull in the value whether it's 2.1, 2.2, 2.3, etc. corresponding to a static Key name such as Code, Region, CountryName, etc.

Hopefully this clarifies the question. I'll look to tinker with the previously provided function to see if it's feasible to leverage it in this more precise scenario.

allan

ActionData is different since it can contain multiple values. This comes back to my point starting "That's a bit more tricky". Your ActionData is in a row already, and there is no way for DataTables to split that one row into multiple rows (with the exception of using child rows I suppose, which might be an option for you? Search and ordering would operate on the child rows).

The data source array needs to contain one item per row. So if you want the data broken down to show multiple log items and each log item having multiple rows, the data source would need to be modified.

If you displayed it like that, would you use row grouping or something to show the individual records? I'm just not clear on how this would display? The other option, is to use a rendering function to display the key / value in a single cell, which would be relatively simple.

Regards,
Allan

iqvct

I'm confused on the comment...

Your ActionData is in a row already, and there is no way for DataTables to split that one row into multiple rows.

...when I'm looking to move ActionData into columns, not rows, similar to ActionValue.

Each record sent to the db contains the below 6 fields:

 db.Insert("Changelog", new Dictionary<string, object>
    {
        { "Username", username },
        { "Action", action },
        { "ActionValue", JsonConvert.SerializeObject( values ) },
        { "ActionData", JsonConvert.SerializeObject( data ) },
        { "RowId", id },
        { "DateTime", DateTime.Now.ToString("yyyy-MM-ddTHHmmss") }
    });

ActionValue contains Table/Field/Value.

"ActionValue":"{\"VW_Cash\":{\"CurrentForecastLc\":100}}"

ActionData contains Code/Region/CountryName

"ActionData":"{\"DT_RowId\":\"row_37226\",\"VW_Cash\":{\"CurrentForecastLc\":100,\"CashId\":37226,\"Code\":\"ABC123\",\"CodeName\":\"ABC Company\",\"Region\":\"EMEA\",\"CountryName\":\"UnitedKingdom\"}}"}}

I want to break ActionValue out into three independent columns. I have been able to do this using the advised method you laid out in this comment.

The goal with ActionData is the same. Break into 3 columns.

Now ActionValue and ActionData would be displayed as 6 independent columns on the front end as shown below: (ignore first two)

Apologies if I'm only repeating myself at this point. Your comment of "That's a bit more tricky" seems to appropriately sum things up.

allan

It entirely depends upon the display you want. Let's assume that we don't have the complication of needing to parse the JSON - so we have a simple data structure like this:

{
    "data": [{
            "Username": "allan",
            "ActionData": {
                "Id": 1,
                "Cash": 10,
                "Currency": "gbp"
            }
        },
        {
            "Username": "iqvct",
            "ActionData": {
                "Id": 2,
                "Cash": 15,
                "Currency": "usd"
            }
        }
    ]
}

Showing that as two rows with columns of Username, Id, Cash and Currency is easy - use data: 'ActionData.Cash' etc.

What can't be done in DataTables (without transforming that data) is showing columns Username, ActionData Key and ActionData Value.

That is what I meant by a row in a row - doing that there would be 6 rows in the table (3 for each ActionData).

To display that data as key/value pairs we'd need to transform the data to be:

{
    "data": [{
        "Username": "allan",
        "Key": "Id",
        "Value": 1
    }, {
        "Username": "allan",
        "Key": "Cash",
        "Value": 10
    }, {
        "Username": "allan",
        "Key": "Currency",
        "Value": "gbp"
    }, {
        "Username": "iqvct",
        "Key": "Id",
        "Value": 1
    }, {
        "Username": "iqvct",
        "Key": "Cash",
        "Value": 15
    }, {
        "Username": "iqvct",
        "Key": "Currency",
        "Value": "usd"
    }]
}

Now you have 6 rows with key / values which can be displayed. However, you'd probably want to group them as well - a "HistoryId" or something along with RowGrouping.

To my mind a history table would normally show an entry (row) per action performed. That row should then show the values that were edited (and propagated values if you wanted) inside it.

Allan

iqvct

To my mind a history table would normally show an entry (row) per action performed. That row should then show the values that were edited (and propagated values if you wanted) inside it.

Yes, this is exactly what I'm looking for. A single row for each action that is performed. That single row containing specific data points from within ActionValue and ActionData fields within the table.

I think your explanation is starting resonate more now. After I took my API output and passed it into a JSON formatter I can better see that inside of ActionData is currently a long string of data. If the ActionData field was stored differently, I could retrieve the underlying values by pointing to the specific key within ActionData as you presented with your simple data structure above.

To this point, does it make more sense for me to assess my controller and how the data is being passed into the table? Or is JSON parsing still a more appropriate solution?

allan

If you want a single row for each action, then your current JSON from the server is fine. However, that requirement (unless I am misunderstanding) conflicts with you also wanting it to be broken down for key / values per row.

Taking my two JSON examples above, there are two actions in the first JSON block === two rows. However, there are multiple data points inside ActionData (three in this case). For me, I would probably show the ActionData in a child row, either as a sub table, or as ul/li list.

Does that make sense?

Allan

iqvct

First, thank you for your patience. I'm clearly not doing a great job explaining the desired output.

As concise as possible, I'm simply looking to log any change that occurs to a table and display this in a single row.

I have provided the _LogChange method that is placed in each controller to show the data that is recorded on each edit.

First I want to know who did it, when they did it, to what table, to what field, and what's the modified value.

The first few are easy as they come in the form of their own fields.

What table, what field and what value are a combination of keys and values in ActionValue which you have kindly shown how to parse here.

Where I seem to be getting hung up on your explanation is within ActionData.

I have provided an image of what "data points" I'm looking to present on the front end in their own independent columns:

In my pretend world, it would be simple and the three values highlighted inside of ActionData would be able to be retrieved based on their corresponding key.

Something like:

VW_Changelog.ActionData.Code
VW_Changelog.ActionData.Region
VW_Changelog.ActionData.CountryName

But looking at the JSON I see it's not as clear as that. And I think I see what you're saying about the list. Rather than trying to parse and retrieve the values and place them all in their own column, create a list of data points provided by ActionData and stack them inside the single column.

allan

If you know you want to always present Code, Region and CountryName in columns - then yes, it is exactly the same as for the ActionValue. Parse the JSON and then use VW_Changelog.ActionData.Code etc for columns.

Where it gets more complex, and possibly what I jumped to prematurely, is displaying the other fields from ActionData. Say you want to display Code for one row, and CurrentForecastLc for another, that's where the wheels come off when doing it with columns. I'm now not sure that is what you want to do though!

Regards,
Allan

iqvct

If you know you want to always present Code, Region and CountryName in columns - then yes, it is exactly the same as for the ActionValue. Parse the JSON and then use VW_Changelog.ActionData.Code etc for columns.

This!

I would pre define exactly which fields I want to present, such as Code/Region/CountryName which would not change.

So you're saying that's feasible with some level of manipulation of the below?

$('#myTable')
  .on('xhr.dt', function (e, s, json) {
    for (let row of json.data) {
      let action = JSON.parse(row.VW_Changelog.ActionValue);
      $.each(action, function (key, val) {
        row.actionTable = key;
 
        $.each(val, function (key2, val2) {
          row.actionField = key2;
          row.actionVal = val2;
        });
      });
    }
  })
  .DataTable({
    ...
  });

The problem is they're not always going to be in the same order as with the ActionValue field. Even though I'm always looking for Code/Region/CountryName, they may be in different spots within ActionData on different rows if that makes sense.

This JSON parsing is likely starting to get outside the scope of Datatables so I may need to refine my google searches on general JSON parsing methods.

allan

Since you are going to be displaying the values from the JSON only, not the keys, it is actually a fair bit easier to do:

$('#myTable')
  .on('xhr.dt', function (e, s, json) {
    for (let row of json.data) {
      row.VW_Changelog.ActionData = JSON.parse(row.VW_Changelog.ActionData);

      // ... ActionValue stuff
    }
  }) 
  .DataTable({
    ...
  });

Order is irrelevant in an object. If it were an array, then that would make a difference, but it is an object with name properties, so it doesn't matter how they are arranged.

Allan

iqvct

Definitely on to something here!

The one issue I'm noticing is that within ActionData, the first key value pair is always

"{\"DT_RowId\":\"row_37491\"

So if for some reason I wanted to see row_37491 I could use VW_Changelog.ActionData.DT_RowId and see it without issue.

But digging deeper, immediately after RowId is another nested object. The entire JSON response looks like this:

"ActionData":"{\"DT_RowId\":\"row_37491\",\"TableName\":{\"Key1\":Value1,\"Key2\":Value2,\"Key3\":Value3,...}}"

The one problem I don't know how to solve is because the _LogChange method was added to many controllers of many tables, the key above titled as TableName will differ from row to row. But regardless of what TableName equals, there will always be a key of Code, Country, Region, within.

Is there a way to basically wildcard TableName , and retrieve the nested codes within?

allan

Is there a way to basically wildcard TableName , and retrieve the nested codes within?

Unfortunately, no. For that would would need to resort to a little more data processing.

  .on('xhr.dt', function (e, s, json) {
    for (let row of json.data) {
      row.VW_Changelog.ActionData = JSON.parse(row.VW_Changelog.ActionData);

      $.each(row.VW_Changelog.ActionData, function(key,val) {
          if (key !== 'DT_RowId') {
            row.VW_Changelog.ActionData.table = val;
          }
      });
 
      // ... ActionValue stuff
    }
  }) 

Then:

data: 'row.VW_Changelog.ActionData.table.Key1'

will do it in this case.

Basically I'm aliasing anything that is not DT_RowId to table so you can access its properties through a known string. The key here is that there is nothing in the ActionData object other than DT_RowId and the table object to alias. If there was anything else, this wouldn't work.

Allan

iqvct

For that would would need to resort to a little more data processing.

Worked like a charm!

A quick follow up to that.

If I did choose to a pull a value corresponding to a key that only exists in half the fields, rather than get the error:

Requested unknown parameter ''VW_Changelog.ActionData.table.Key1' for row x, column y

is there a way for it to simply render a null if it does not exist for a specific row rather than get a warning?

EDIT:

I did find this:

columns.defaultContent

But this seems to work if the value is null not that if the field does not exist at all.

allan

Yes, columns.defaultContent set to be an empty string should handle that. If it doesn't can you show me the full configuration?

Allan

iqvct

I had it set to null rather than an empty string.

Now it's working, thanks @allan