Dynamically set Fetch Criteria

Unknown

Dynamically set Fetch Criteria

Unknown

Hi,
Hoping someone can help my understanding. To give a little background, Im using a fetch entities step to get data from a database structure and we have criteria to fetch by based on inputs on a form. The form contains many text box fields that correlate with fields of the data structure. Only when data is present for that field, then we use that as input on the fetch criteria.

To sum, I want to be able to dynamically set the fetch criteria on a fetch entities step, but when ran it returns no results and Im not completely as to why.

Unknown

I have attached an example project.

Unfortunately, if you select the Combine Filter Using AND on the Fetch Entities step and one of the filtering criteria is not used it will return a null value. This causes the Fetch Entities step to output no results. Since the requirements allow you to select any one or more criteria, this way of fetching data alone will not give you the results you need.

In the attached example, we have three criteria we want to search by (Car make, year, and color). In the flow we run a Truth Table to check which criteria is used and it returns the necessary SQL statement. This statement is then used as input into a Raw SQL Step as a Where clause to fetch specified data.
[img]att1[/img]

The Raw SQL step uses Merge Plain Text mapping and includes SQL query to Select the table from the database. The WhereClause is the returned result of the truth table. For your project you will need to replace this with the following:

SELECT * FROM QualityChecklistTestRun_MainDatabasePV
WHERE

If you have any further questions and/or concerns, I am happy to get on a call to discuss.

[b]Please Note: [/b][i]The examples attached were developed to be instructional, and were not developed as officially supported components. For more information or to engage our service team to develop fully supported, production quality solutions, please contact: services@decisions.com[/i]
[i]edited by cameron@decisions.com on 3/30/2020[/i]