I am using mysql and have a existing mysql query(resource), this is called rdmvaluequery. I have another mysql query(resource) called eestatus. What I am trying to do is reference a specific value from the rdmvaluequery and filter it to the field I want in the eestatus query. For example the goal of the query would be the below:
Field X in ({{rdmvaluequery.data where rdm_type_cd = 'Employment_Status_CD'}}) then I would need to get the specific field value based on this above filter(these values would come from another column in the table named rdm_value.
I am trying to eliminate writing an additional query and just use the values based on the above filter. Can someone provide any help/guidance? Thanks
Hey @AccoladeRetool!
You can use some inline JavaScript to search your query data for the right value. For example, {{ formatDataAsArray(rdmvaluequery.data).filter(row => row.rdm_type_cd === 'Employment_Status_CD').map(row => row.fieldX) }} should return an array of fieldX values where the corresponding row meets the rdm_type_cd = 'Employment_Status_CD' criteria and you can apply additional filters using the && operator.
Putting a lot of logic into one-liners can get pretty messy though 
So if it works for you to create a separate Query JSON with SQL query or so to help keep things cleaner that might be the best way to go.
Would you mind describing your workflow a bit more? What data is being fetched with the eestatus query? And how exactly are you looking to filter it?
Hey Kabirdas! Thanks for the help on this, this worked perfectly! To describe a little more, I am creating an app which fetches employment attributes(from different customers) from certain data tables querying mysql. In this specific query, there is a rdm_type_cd column which needs to be filtered on EMPLOYMENT_STATUS_CD, then from the employee status cd I need to filter on a src_nm column where that value is say 'Terminated' and this then corresponds to a number value such as 2. The number 2 is the value I want to return, but the real purpose here is to be able to get values for multiple customer divisions(each customer has different divisions that the user can select and each customer may have a different value for 'Terminated', so this is where the array came in and wanted to fetch multiple values based on what customer/division choices the user makes. This avoided the use of reusing the same mysql queries with different filters.
Hi @Kabirdas, how would I use a wildcard within the filter in your solution?
Hey @AccoladeRetool!
If you want to use more complex matching you can try using the .match function (docs). It'll accept regular expressions (docs on those). for instance, if you wanted to have a search field that just does a case-insensitive check to see if that field exists anywhere in the string, you could do something like
.filter(row => row.rdm_type_cd.match(new RegExp(textInput1.value, "i"))
The wildcard in regular expressions is . and you can indicate if you want it to match 0 or more characters with * so, for instance, new RegExp("employment_.*_cd", "i") would match "Employment_Status_CD" as well as "Employment__CD" and "Employment_a_CD".
Hey @Kabirdas!
Thanks a lot for this help!