Server Side Searchpanes with Sql View

sarooptrivedi

Server Side SearchPanes trying to look for table in table query rather than view field. It is throwing the error on view field which is not available inside the table. In below code I made the Year base on the EntryDate of table. on the fly in View query. but I got the error that unknown column Year, when I am trying to filter the value from the searchpanes. Is the Searchpanes support the SQL View at server side?

         var response = new Editor(db, "TblUser", pkey)
                .ReadTable(TblNames."V_User")
                .Field(new Field("Id"))        
                .Field(new Field("UserName"))
                .Field(new Field("FullName"))     
                .Field(new Field("EntryDate").Validator(Validation.DateFormat("yyyy-MM-dd")).SetFormatter(Format.NullEmpty()).GetFormatter(Format.DateSqlToFormat("yyyy-MM-dd")).SetFormatter(Format.DateFormatToSql("yyyy-MM-dd")))
                .Field(new Field("Year").SearchPaneOptions(new SearchPaneOptions()))        
                .TryCatch(true).Debug(true)
                .Process(httpRequest).Data();

sarooptrivedi

SELECT * FROM [TblUser] WHERE [Year] = @where_0

Above query generated in debug while apply the Search Panes filter. expectation with SQL View

SELECT * FROM [V_User] WHERE [Year] = @where_0

allan

You need to use the methods of the SearchPaneOptions class to tell it explicitly what table / view, field name and value you want to use as the data source. Have a look at the second example on that page to see how the code for that would look.

Regards,
Allan

sarooptrivedi

Hey @allan ,

I changed the column as per your suggestion. But still in debug I get the same error that unknow column Year because in the debug it is shows the actual table name rather than the view.

.Field(new Field("Year").SearchPaneOptions(new SearchPaneOptions().Table(TblNames.V_User).Value("Year").Label("Year")))

The debug result

SELECT * FROM [TblUser] WHERE [Year] = @where_0

Above query generated in debug while apply the Search Panes filter.
expectation with SQL View

SELECT * FROM [V_User] WHERE [Year] = @where_0

The Year column is in the view but not in the table. I am making Year column inside the view by SQL Function.