OnValidate vs OnFilterValidate, Want to limit number of rows that might be returned

Applications Grid / Reports
#1

On the Advanced Search I am wanting to see if the search will retrieve more than 30 rows, and if so I want to put an alert telling the user to limit the criteria more.

I am trying to use the OnValidate event, because I only see onScriptInit, onRefresh, onSave and onValidate… I could have sworn at one point I had an onFilterValidate but I sure don’t see it now.

The sc_Select macro that I have in the onValidate doesn’t seem to be running. I turned SQL debug on and it never shows it running.

For now this is my code. I am actually testing so using a customer SQL statement but once I get it working, I will replace with the sql statement that has the criteria in it.

sc_select(my_data, “select count(1) from businessaddress where state = ‘RI’”);

if ({my_data} === false)
{
echo “Access error. Message =”. {my_data_erro};
}
else
{
while (!{my_data}->EOF)
{
$Count = {my_data}->fields[1];
{my_data}->MoveNext();
}
{my_data}->Close();
}

if ($Count > 10)
{
$error_message = ‘You have selected too many’;
sc_alert($error_message);

}

#2

Hi Alan,
1 - Since your sql is only returning 1 record I would use sc_lookup vrs sc_select but that is not a big deal just my preferrence.
2 - The field index should start at zero, not 1, so fields[0];
3 - sc_alert will not function here as the search is executed immediately after this code. So I would replace that with this code that will display the error with an OK button then direct you back to the advanced search.

if ($Count > 10)
{
sc_error_message (’<br> <font size=“6”>Too many rows returned, ‘.$Count.’ <br>Please Modify the Filter.</font><br><br>’);
sc_error_exit(application_x, “_parent”);
}

#3

Thanks BWalk, that helped a lot. The only 2 issues I have now is the sc_error_message returns Erro in the title of the error, and not Error like it should in English.

Second issue is now I’m trying to get the “where clause” that is being created in the Advanced Search so I can use it to create something like

select count(1) from businessaddress where (whatever criteria SC is building for the search query), however I can’t seem to find anything that works. I have tried {sc_where_filter}, {sc_where_orig} and {sc_where_current} and they give me errors. I have tried placing it in the onFilterValidate and the onFilterInit but it doesn’t like it