Invalid column name using custom search query and filter

[Joe.Thorpe]

Hi,

I've created a plugin which adds a custom search query to the Purchase Order and Sales Order forms.

This query uses a SQL view to add some columns which aren't on SO_Main/PO_Main. One of these columns is FurthestETAToSH.
I have a search filter which filters on FurthestETAToSH. Understandably, I can't use this filter with the builtin queries, since FurthestETAToSH doesn't exist in those queries.

The filter does work when I use this custom query, that's expected.

What I didn't expect, is that that if I open the PO or SO form, I get an error saying "Invalid column name FurthestETAToSH". JIWA remembers the last query and filter you use, and for me, that is my custom query and filter. JIWA should be opening the form with that query, and then the filter would work.

So TLDR: Do you know why, when I use a filter that depends on a column that's not in any of the default queries, and I'm using a custom query which does have the column, why I get this error when opening the SO/PO form?


I've attached the plugin, if you want to check it out.
I've put a breakpoint in the Search_Showing method, and it doesn't get hit when opening the form. That makes sense this error is happening on like PO/SO ReadStart/ReadEnd. Perhaps there's some way I can add these queries on ReadStart/ReadEnd?

[SBarnes]

Hi Joe,

Can you provide the views that you are using it's hard to diagnose the problem without the complete picture?

However beyond that I believe your problem may not be related to the searches you've added but rather filters related to the searches, what Jiwa will do is load a query of the business object when it loads the form and then apply filters to it and if the filter has columns not in this SQL then that will produce an error.

The property on the business object that contains this SQL is called BaseFindSQLQuery, for a more detailed explanation of this and sort orders have a look at viewtopic.php?f=26&t=2225

If you want to test if what I am suggesting is correct try doing your custom search and make sure there are no filters on and the then close the form and reopen it and see if you get the error, if you don't then my assumption is the correct one.

[Joe.Thorpe]

Hi Joe,

Can you provide the views that you are using it's hard to diagnose the problem without the complete picture?

However beyond that I believe your problem may not be related to the searches you've added but rather filters related to the searches, what Jiwa will do is load a query of the business object when it loads the form and then apply filters to it and if the filter has columns not in this SQL then that will produce an error.

The property on the business object that contains this SQL is called BaseFindSQLQuery, for a more detailed explanation of this and sort orders have a look at viewtopic.php?f=26&t=2225

If you want to test if what I am suggesting is correct try doing your custom search and make sure there are no filters on and the then close the form and reopen it and see if you get the error, if you don't then my assumption is the correct one.

Hi Stuart,

Thanks for pointing us in the right direction. We had to modify the BaseFindSQLQuery property when the user is using the custom query. I've attached the code.

Appreciate your help!