# BAQ On The Fly?

**URL:** <https://www.epiusers.help/t/baq-on-the-fly/121248>\
**Category:** Kinetic ERP\
**Created:** [November 27, 2024, 1:07pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248 "2024-11-27T13:07:39Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\
**Post date:** [November 27, 2024, 1:07pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/1 "2024-11-27T13:07:39Z")

</div>

Ok, here is what I want to accomplish, and I have not come up with a way to do it yet. Looking for some suggestions on how to tackle it.

I have a custom app that I made where I am using UD Tables to store the data. The data is recursive once a column out of a list of columns is chosen. I have dropdowns for the user to select values and one of them is for which column is needed. I have a button that takes the values and runs a BAQ. My problem is I need to dynamically put that column in the select statement and also in the where statement.

So, what I am looking to roughly do is the following:

```auto
with CTE (Key1, Key2, Key3, Key4, Key5, Column, Level)
as
(
    select Key1, Key2, Key3, Key4, Key5, *DynamicColumn*, 0 as Level
    from UD01
    where *DynamicColumn* <> ''
    union all
    select a.Key1, a.Key2, a.Key3, a.Key4, a.Key5, a.*DynamicColumn*, Level + 1
    from UD01 as a
    inner join CTE as b
    on a.*DynamicColumn* = b.Key2
)
select *
from CTE;

```

I was thinking I might need to do a Function where I construct the BAQ myself, but am not sure. Any thoughts are appreciated.

---

<div class="post-metadata">

**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\
**Post date:** [November 27, 2024, 1:53pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/2 "2024-11-27T13:53:38Z")

</div>

Great minds. Probably completely different reasons lol.

![oh my god omg GIF by CBC](https://media4.giphy.com/media/jt2kGEJhTQXFhUOfRm/giphy.webp?cid=e90edba3c1ac3yhpseparj2latml6cny66sfp89fem9mafzn&ep=v1_gifs_search&rid=giphy.webp&ct=g)

Is it just this one column?

---

<div class="post-metadata">

**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\
**Post date:** [November 27, 2024, 1:55pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/3 "2024-11-27T13:55:41Z")

</div>

Yes, beyond the Key columns there are like 30 other columns, but I need to just select 1 of them.

---

<div class="post-metadata">

**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\
**Post date:** [November 27, 2024, 4:30pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/4 "2024-11-27T16:30:25Z")

</div>

Ok, this is quick, dirty, evil, and may eat your children.

It handles one or two tables, and if you don’t have unique names for the replaced fields, well… Round two will blow it up lol

```cs
CallService<DynamicQuerySvcContract>(dq =>
{
    var queryTS = dq.GetByID(BaqID);

    string queryTSJson = JsonConvert.SerializeObject(queryTS, Formatting.Indented);
    
    //Round 1
    if(!String.IsNullOrEmpty(Table1) && !String.IsNullOrEmpty(Table1FieldToReplace) && !String.IsNullOrEmpty(Table1FieldReplacement))
    {
        queryTSJson = queryTSJson.Replace($"{Table1}_{Table1FieldToReplace}", $"{Table1}_{Table1FieldReplacement}");
    }
    
    if(!String.IsNullOrEmpty(Table2) && !String.IsNullOrEmpty(Table2FieldToReplace) && !String.IsNullOrEmpty(Table2FieldReplacement))
    {
        queryTSJson = queryTSJson.Replace($"{Table2}_{Table2FieldToReplace}", $"{Table2}_{Table2FieldReplacement}");
    }
    

    //Round 2
    if(!String.IsNullOrEmpty(Table1) && !String.IsNullOrEmpty(Table1FieldToReplace) && !String.IsNullOrEmpty(Table1FieldReplacement))
    {
        queryTSJson = queryTSJson.Replace(Table1FieldToReplace, Table1FieldReplacement);
    }
    
    if(!String.IsNullOrEmpty(Table2) && !String.IsNullOrEmpty(Table2FieldToReplace) && !String.IsNullOrEmpty(Table2FieldReplacement))
    {
        queryTSJson = queryTSJson.Replace(Table2FieldToReplace, Table2FieldReplacement);
    }
    
    
    var dqTS = JsonConvert.DeserializeObject<DynamicQueryTableset>(queryTSJson);

    output = dq.Execute(dqTS, new QueryExecutionTableset());

});

```

---

<div class="post-metadata">

**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\
**Post date:** [November 27, 2024, 4:33pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/5 "2024-11-27T16:33:22Z")

</div>

Round two could be refined to not eat your children if needed, but then it wouldn’t be quick and dirty.

---

<div class="post-metadata">

**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\
**Post date:** [November 27, 2024, 4:36pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/6 "2024-11-27T16:36:30Z")

</div>

If I were going to build a baq to use with this function, I would chose something that is definitely unique for the template column.

I’d say use a calculated field, but then we’d have to modify the query structure.  
So just find something unique so you always know what you are replacing.

---

<div class="post-metadata">

**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\
**Post date:** [November 27, 2024, 4:42pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/7 "2024-11-27T16:42:37Z")

</div>

In fact, if it’s _ **completely unique** _, you could simplify the function to just do a straight replacement of the field name with another.

Like if it was UD01._ **MyAbsolutelyUniqueTemplateField** _…

Would just be →

```cs
CallService<DynamicQuerySvcContract>(dq =>
{
    var queryTS = dq.GetByID(BaqID);

    string queryTSJson = JsonConvert.SerializeObject(queryTS, Formatting.Indented);
    
    
    if(!String.IsNullOrEmpty(FieldToReplace) && !String.IsNullOrEmpty(FieldReplacement))
    {
        queryTSJson = queryTSJson.Replace(FieldToReplace, FieldReplacement);
    }
    
    var dqTS = JsonConvert.DeserializeObject<DynamicQueryTableset>(queryTSJson);
    
    output = dq.Execute(dqTS, new QueryExecutionTableset());

});

```

---

<div class="post-metadata">

**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\
**Post date:** [December 3, 2024, 3:38pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/8 "2024-12-03T15:38:14Z")

</div>

Thanks @klincecum , appreciate the help. There was a lot of changes that needed to be done, so I skipped the code and made @JasonMcD proud with widgets instead. It is working as I needed it too.

![image](https://www.epiusers.help/uploads/default/original/3X/8/8/886127860b7613bafec4f45437c79f6bd6eca9e5.png)

---

<div class="post-metadata">

**Author:** ![JasonMcD](https://www.epiusers.help/user_avatar/www.epiusers.help/jasonmcd/32/22326_2.png) [@JasonMcD](https://www.epiusers.help/u/JasonMcD)\
**Post date:** [December 3, 2024, 3:49pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/9 "2024-12-03T15:49:09Z")

</div>

Uhhhh…

That scares **me**.

It reminds me of the Idea to allow us to set many fields in one widget:

[Within BPM & Epicor Functions designer, allow the SET widget to set multiple variables/fields](https://epicor.ideas.aha.io/ideas/KNTC-I-2553)

Man, even @aidacra can’t get it expedited, eh? 2.5 years now…

Anyway, that’s not what those are after all - those are all “Update Table by Query”? And you had to do them all individually???

---

<div class="post-metadata">

**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\
**Post date:** [December 3, 2024, 3:51pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/10 "2024-12-03T15:51:46Z")

</div>

Unfortunately, yes. I’m hijacking a BAQ and that is what needs to change in the dataset tables. Each one is a separate set of filtered fields.

---

<div class="post-metadata">

**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\
**Post date:** [December 3, 2024, 3:53pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/11 "2024-12-03T15:53:35Z")

</div>

![ryan reynolds hd GIF](https://media0.giphy.com/media/s239QJIh56sRW/giphy.webp?cid=e90edba36p6zwxjabywxelqtjr3ulzt5l0bnaucp85tiaekh&ep=v1_gifs_search&rid=giphy.webp&ct=g)

---

<div class="post-metadata">

**Author:** ![jkane](https://www.epiusers.help/user_avatar/www.epiusers.help/jkane/32/57624_2.png) [@jkane](https://www.epiusers.help/u/jkane)\
**Post date:** [December 3, 2024, 3:58pm UTC](https://www.epiusers.help/t/baq-on-the-fly/121248/12 "2024-12-03T15:58:47Z")

</div>

🤷‍♂️ Solution I came up with to my problem. Might be a better way, but I don’t know of it. 🤣