BAQ On The Fly?

Kinetic ERP
1

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:

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.

4 Likes
2

Great minds. Probably completely different reasons lol.

oh my god omg GIF by CBC

Is it just this one column?

2 Likes
3

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

2 Likes
4

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

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());

});
3 Likes
5

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

6

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.

7

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 → 

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());

});
1 Like
8

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

12 Likes
9

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

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???

2 Likes
10

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.

1 Like
11

ryan reynolds hd GIF

1 Like
12

:man_shrugging: Solution I came up with to my problem. Might be a better way, but I don’t know of it. :rofl:

3 Likes