Scenario iDempiere allows the user to make some advanced database searches through search panel (FindWindow). With that resource, we can create many conditions to filter the records. An extremely useful feature of this window is the save search function, which allows us to use the same search multiple times, without needing to recreate it.
Failure A failure has been found when trying to execute saved queries that have flag parameters. Before the flag value is saved in the search, it’s execution returns the expected results, on the other hand, after saving this search and trying to execute it again, no records are found on database.
Failure cause That failure happens because of the way that generated SQL handles values provided by the user in parameter flag. Figure 1 shows a research that was created from a flag parameter.
Figure 1 - Research that will be executed.
When running this search, the system will find records normally, generating a similar SQL shown in Figure 2.
Figure 2 - SQL generated in query before saving.
After saving the same search that was executed, the system no longer finds records. Figure 3 shows a similar SQL to the one generated by the search executed after it was saved.
Figure 3 - SQL generated in the search after being saved.
The search code that is persisted in the database when a search is saved does not have the treatment that changes Boolean values to "N" or "Y", as shown in Figure 4.
Figure 4 - Code saved at database.
When executing the code that was saved on the database, the value "false" is used in the search instead of "N", making it impossible to find data.
Solution A possible solution is to create a validation that will save the parsed value on database, instead of the non-parsed value. Figure 5 shows a way to do that.
Figure 5 - Possible solution.
If there is a value for the variables "parsedValue" and "parsedValue2" (which indicates that value conversion already happen), it’s values will be considered at the code that will be saved, turning the value into “N” or “Y”, instead of “false” or “true”. Figure 6 demonstrates the code that was saved to the database after this modification.
Figure 6 - Solution result.
Test case To recreate the demonstrated failure, simply follow these steps: 1) Create two Sales Orders: one with the value of the "Drop Shipment" field set to true and another one with the same field set to false. 2) Create an advanced search. On "Tab", select "Order". On "Field", select "Drop Shipment". On "Operator", select "=". On "Query value", select "true".
3) Run the query. System will find the record you created where the "Drop Shipment" field is set to true. 4) Return to the advanced search window. Save the search.
5) Execute the search.System will not find the records you created.
Scenario
iDempiere allows the user to make some advanced database searches through search panel (FindWindow). With that resource, we can create many conditions to filter the records.
An extremely useful feature of this window is the save search function, which allows us to use the same search multiple times, without needing to recreate it.
Failure
A failure has been found when trying to execute saved queries that have flag parameters.
Before the flag value is saved in the search, it’s execution returns the expected results, on the other hand, after saving this search and trying to execute it again, no records are found on database.
Failure cause
That failure happens because of the way that generated SQL handles values provided by the user in parameter flag. Figure 1 shows a research that was created from a flag parameter.
Figure 1 - Research that will be executed.
When running this search, the system will find records normally, generating a similar SQL shown in Figure 2.
Figure 2 - SQL generated in query before saving.
After saving the same search that was executed, the system no longer finds records. Figure 3 shows a similar SQL to the one generated by the search executed after it was saved.
Figure 3 - SQL generated in the search after being saved.
The search code that is persisted in the database when a search is saved does not have the treatment that changes Boolean values to "N" or "Y", as shown in Figure 4.
Figure 4 - Code saved at database.
When executing the code that was saved on the database, the value "false" is used in the search instead of "N", making it impossible to find data.
Solution
A possible solution is to create a validation that will save the parsed value on database, instead of the non-parsed value. Figure 5 shows a way to do that.
Figure 5 - Possible solution.
If there is a value for the variables "parsedValue" and "parsedValue2" (which indicates that value conversion already happen), it’s values will be considered at the code that will be saved, turning the value into “N” or “Y”, instead of “false” or “true”. Figure 6 demonstrates the code that was saved to the database after this modification.
Figure 6 - Solution result.
Test case
To recreate the demonstrated failure, simply follow these steps:
1) Create two Sales Orders: one with the value of the "Drop Shipment" field set to true and another one with the same field set to false.
2) Create an advanced search. On "Tab", select "Order". On "Field", select "Drop Shipment". On "Operator", select "=". On "Query value", select "true".
3) Run the query. System will find the record you created where the "Drop Shipment" field is set to true.
4) Return to the advanced search window. Save the search.
5) Execute the search.System will not find the records you created.