Grid Save Zero as Null

[Ben S.]

What are some of the simple ways I can have a grid automatically save 0's as null values?

I have a grid where users can forecast hours per week. Some users have been entering 0 to clear out cells instead of entering null. How can I go about converting 0 to NULL when the edited page of the grid is submitted?

2018-06-20_8-20-24.png

[Peter.Greulich]

Re: Grid Save Zero as Null

Ben,

You can probably simply set the display property of the field - see image...

Grid Blank if zero.png

[Ben S.]

Re: Grid Save Zero as Null

Will that save the 0 values as null? My guess is that will only affect how a zero is displayed. Ultimately I don't want zeros stored in my table because it affects other areas of the application.

Do you have any other suggestions?

[Jay Talbott]

Re: Grid Save Zero as Null

Did you try checking the Blank is null?
blankIsNull.png
You can force the field to be Null if it is zero with this js on the OnBlur event:

var rowNum = {Grid.RowNumber};
var fieldValue = {grid.Object}.getValue("G","AMT_PAID",rowNum);
if (fieldValue == 0){

{grid.Object}.setValue("G","AMT_PAID",rowNum,'');

}

(with AMT_PAID being the field you want to make null)
Jay

[Ben S.]

Re: Grid Save Zero as Null

Thank you Jay! This is a great start. I didn't know a concise way to address the row number.

I already had the "Blank is null" property checked. In your example code you are setting the new value to be ''. Is there an advantage of that over setting it to null?

I had started experimenting with the onChange event instead of onBlur. I noticed that onBlur runs the code every time I tab over the cell. I'd prefer to not have code running unnecessarily. Unfortunately there seems to be a side effect using the onChange event. The code below runs in an endless loop if 0 is entered into the grid cell. Is there a way to tell the Change event not to fire if its value is changed within the event?

var rowNum = {Grid.RowNumber}; 
var fieldValue = {grid.Object}.getValue('G','FCST_2018_25_JUN22',rowNum); 
 
console.log('FCST_2018_25_JUN22 CHANGE: ' + {grid.Object}.getValue('G','FCST_2018_25_JUN22',rowNum));    
 
if (fieldValue == 0){ 
    {grid.Object}.setValue('G','FCST_2018_25_JUN22',rowNum,''); 
}  

[Peter.Greulich]

Re: Grid Save Zero as Null

You could try something like this using the server side CanInsertRecord & CanUpdateRecord events:

function CanUpdateRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p )
with PageVariables
Result.Cancel = .f.
Result.ErrorHTML = ""

Dim args as SQL::Arguments
args.SetNull("DataSubmitted.Account_Id","N")
DataSubmitted.Account_Id =  :Account_Id


end with
end function

But I'm not sure it will work?

[Ben S.]

Re: Grid Save Zero as Null

Processing this from the server side is an acceptable approach but I know less about Xbasic than I do JavaScript.

Is there a way to loop through all columns and rows of a grid? I have 30 data entry columns that I need to run this against and the columns shift every week. I'd prefer to not have to hard code the column names. If I had a loop where I could identify the column name and if it is prefixed with 'FCST_' then I could look for a 0 and replace it with null. I don't know how to get the column name from the grid. I could do this on the client or the server.

[madtowng]

Re: Grid Save Zero as Null

I'm not sure how the columns can "shift each week", but presuming you are saving this data in some kind of sql database, I would use before insert and before update triggers to do this work.

[Ben S.]

Re: Grid Save Zero as Null

I'm not sure how the columns can "shift each week", but presuming you are saving this data in some kind of sql database, I would use before insert and before update triggers to do this work.

We have an denormalized database schema (like a pivot table) so we have a forecast column for each work week in the year. Every Monday we delete the first forecast column from the grid because it now represents a week in the past and we don't want to show it. We then re-publish the application. We do the same to show only the last 4 weeks of actual hours.

It's tedious publishing every week. We're using this method because there doesn't seem to be an easy way to store data in a normalized (un-pivoted) table, then allow it to be pivoted by work week and editable in a grid, and then stored back in a normalized way.

Using Triggers to do this is an option I originally hadn't considered. There are at least 52 columns that I'd have to validate in the trigger.

[madtowng]

Re: Grid Save Zero as Null

I think this can be done with a subquery to get the data for the first column.
My preferred database is MySQL and I'm quite creative with date related queries,
but it would help me greatly to see what the current query is.

[madtowng]

Re: Grid Save Zero as Null

The code below will select rows with pickup_dates between the most recent Monday and the following Sunday.
I think it would give you the data desired for the first column.

select  pickup_date, city, state FROM table 
where PICKUP_DATE between curdate() - interval (DAYOFWEEK(curdate())-2) day and curdate() - interval (DAYOFWEEK(curdate())-2) day+interval 6 day

[Ben S.]

Re: Grid Save Zero as Null

Thanks for your input. I have put together a solution to this issue. This implementation allows the user to enter zeros into the cells but it sets them to null before they are submitted to the database.

---

I created a Published Session Variable in the grid properties named SESSION.GRID_COLUMN_STRING

In the grid OnGridInitialize server-side event I added a routine to populate that session variable with a comma delimited list of the grid column headings.

	' Create a session variable containing a comma delimited string of the grid column headings.
	dim columnString as c = ""
	colNum = e.tmpl.field_info.size()
	for i = 1 to colNum
		columnString = columnString + "," + Upper(e.tmpl.field_info[i].BaseFieldExpression) 
	next
	
	' Remove the first comma
	columnString = substr(columnString,2) 
	
	' Set the published session variable that is defined in the grid properties.	
	SESSION.GRID_COLUMN_STRING = columnString

I created a function to loop through all of the grid cells by row and column. It specifically looks for my Forecast columns that may be named like 'FCST_2018_52_Dec28'. If the cell contains 0 it is replaced with null.

function validateGridCells() { 
/* 
 This code will loop through all grid cells and change all Forecast values of 0 to null. 
 This is called by the canGridSubmit() Client-side event. 
 BS 6/21/2018 
*/ 
     
    // Get the column string from the session variable and convert it into an array. 
    var columnString = {grid.Object}.getSessionVariable('GRID_COLUMN_STRING'); 
    var colArray = new Array(); 
    colArray = columnString.split(','); 
    var colArrayLen = colArray.length; // Number of columns. 
     
    var rowNum; 
    var colNum; 
    var colName; 
    var fieldValue; 
     
    // If you want this to only process the selected row in the grid replace the first For loop with the following statement. 
    //rowNum = {Grid.RowNumber}; 
 
    for (rowNum = 1; rowNum <= {grid.Object}.getRowsInGrid(); rowNum++ ) { 
 
        for (var colNum = 0; colNum < colArrayLen; colNum++ ) { 
     
            colName = colArray[colNum]; 
 
            // Process all of the weekly forecast columns     
            if (colName.substr(0,7) == 'FCST_20') {                 
                fieldValue = {grid.Object}.getValue('G',colName,rowNum); 
     
                // Must use quotes around '0' because an empty string '' == numeric 0. 
                if (fieldValue === '0'){ 
                    //console.log('Row: ' + rowNum + ', Column: ' + colNum + ' (' + colName + ') = ' + fieldValue); 
                    {grid.Object}.setValue('G',colName,rowNum,null); 
                }         
            } 
        }     
    } 
    // Always return True unless we have a reason to cancel the action that calls this function. 
    return true; 
}  

In the grid canGridSubmit client-side event I added a function call to validateGridCells();
During testing I was calling this function from the grid onDblClick event so I didn't keep saving over my test data.

If you know of a method to get the column headers from JavaScript without relying on Xbasic and a Session variable, please share that below.

[Ben S.]

Re: Grid Save Zero as Null

I think this can be done with a subquery to get the data for the first column.
My preferred database is MySQL and I'm quite creative with date related queries,
but it would help me greatly to see what the current query is.

My current table is flat and denormalized. It is laid out just the way the user would use it. Data entry is very easy with this schema. Querying by date range is very hard. See screenshot at the top of this thread.

If you want to private message me, I can give you a demonstration of my application over Skype. I would like to know your feedback in case there is a better way I can do this.

[lgrupido]

Re: Grid Save Zero as Null

What database are you using? If this is sql, you could just create a trigger to set all your zeros to null.

[madtowng]

Re: Grid Save Zero as Null

Hi Ben,
I couldn't make an exact copy, but I think I'm on the right path.
The date related columns are all generated programmatically.
Starting next week, it should shift the dates left 1 column, and put the next week into the right-most column.
I attached a script to create the table, as well as the query for the grid, and the sample data I used.
I did not create the suggested triggers yet, but those are relatively simple.

Capture.PNG