How can i hide some rows in grid befor showing (without changing my main sql query)

Applications Grid / Reports
#1

Hi there!

Is there a way to hide some rows in my grid (for example, adding some php code or using built-in SC macros in onRecord event) without changing the main sql statement? For example, in my case i have this grid:

1 Anna 0 2 0 2
2 Alexa 0 0 0 0
3 Mike 0 0 1 0

If here are all zeros in columns after name (and it can be checked with php in onRecord event) - i want to hide/remove this row (in this example, row with Alexa have to be removed)

So, it have to be finally:

1 Anna 0 2 0 2
3 Mike 0 0 1 0

Yes, you can tell me - just change your main sql! But in this particular case i can’t do it.

If i will hide this rows with js/jquery, there will be some problems with pagination, searching and so on. I would like to do this without js.

I know about “sc_field_display” macro, but it will hide field, not the row. But we don’t have “sc_row_display” macro…Any thoughts?

#2

Please explain why you cant add a where statement to SQL, so that we can understand the problem better!

#3

Hi Nico. I can’t add WHERE statement, because it is too complex for SQL language, it also contents IF statement and several things…Like this:

if (({somefield} !== NULL) && ({somefield} !== '')) {
// another stuff 1 with a lot of if statements checkings
	}
else {
// another stuff 2 with a lot of if statements checkings
}

Now i remove (like $(".rowhidden").closest(‘tr’).remove(); ) these rows with jquery and show all records at once, without pagination - to avoid issues. But it is not perfect idea, though it works. It is better to have something like “sc_field_display” macro, but for row.

For example, when i try to export this grid to excel, it also exports removed from DOM rows, because it redraws it…Do someone know how can i also remove some rows while exporting my grid to excel?

#4

Try a blank app before the grid with your statement and then redirect. Use a global var in your statement and in the where clausule of your grid

Or use the where add macro in onscriptinit

#5

You can do it in SQL using nested SELECT

SELECT id, fa, fb, fc, fd FROM (SELECT id, fa, fb, fc, fd FROM ...) AS Temp WHERE fa <> 0 and fb <> 0 and fc <> 0 and fd <> 0

1 Like
#6

I would make a function that updates a field called ‘display_record’
PHP can handle your complicated logic that will determine if a record should be shown or not
Then simply add a WHERE filter to the SQL on your grid: where display_record = 1
Will work for your excel export as well.

#7

Hy.
You can add (echo) some html with embedded JS/JQuery code in the on onfooter event which triggers after rows are displayed.
Here you can hide the rows just displayed.
But in my opinion you should really consider to put such logic in the SQL statement.

1 Like