When you create a BAQ, it shows you the SQL code right on the front tab (General tab) of the BAQ. I can copy this code and edit it locally in my preferred editor, or play around with it in SQL Server Management Studio.
But is there a way to go in reverse? What I am thinking is that I want to copy my BAQ SQL code to github so that it can be easily shared, viewed, archived, managed, etc. But then I don’t have a way to go in reverse and copy the SQL code back into a new BAQ. Is there a way to do this?
Just did, while I appreciate all of the complexity that a BAQ can handle without touching SSMS or being super proficient in SQL, it’s very cumbersome if you do know SQL.
I think it’s more of a conversion utility - converting those things we can do in SQL into the BAQ syntax that may never have known you could do. I don’t want it to bypass any of the Epicor code, just convert my SQL to a BAQ.
Oh, I think I get it. Will the conversion utility add this for us? Will SQL authors test in SSMS first? They won’t provide the same results as a BAQ unless we do the stuff above. Is the idea to get us started instead of messing with the GUI? Just quick and dirty queries? I’m trying to understand the use cases where this makes our lives easier.
I’m all for making it easier to create BAQs. Given a choice on where to put Epicor’s effort first, I must admit that I like @josecgomez’s idea to go the other way and make the BAQs’ SQL more efficient.
Something as simple as being able to take just part of another BAQ, like a subquery, and copy paste into a new BAQ would be nice. Currently, I think the only way to do that is to copy the whole query and then cut out the stuff you don’t need.
Also, (and this is for the on prem people with access to the server), being able to take a query that Epicor is performing and grabbing it via SQL-profiler or some other tool and being able to make it into a BAQ could be very helpful too, or at least save a lot of time.
Also, for repetitive queries/fields, where you can copy paste code to things relatively quickly in a text editor, but you have to do one at a time in BAQ land. Calculated fields that are super similar but not quite (like things with ranges, like number of days, where you need 1-10, 10-30, 30-60 etc).
It helps. But I believe there are limitation with that right? Can you use that feature with Updateable BAQ’s? I haven’t had time to dig into that functionality yet. Also, you might be making the calculated fields for the first time.
If they don’t know, wouldn’t they just do the same weird thing in BAQ designer? What about BAQ designer would stop them from performing an unnecessary filter or inaccurate join apart from the intellisense/suggested joins that they’d probably delete or ignore anyways?
and runs it, they’ll get all customers from all companies regardless of territory. If we pass that SQL into “the tool” and then run the query, users will get only the customers in the current company UNLESS the cross company box is enabled, and only customers where the user is an authorized user of the territory BUT if they are not authorized users and their workforce record has view all territories then all customers are seen.
So how do we make sure the two queries match? Again, if this is just a “get me started tool” then maybe that’s OK.
I think perfect is becoming the enemy of done in your case. Will there be caveats? Of course, but they can at least be explained. To say we shouldn’t make this tool because the conversion won’t 100% match all of the time is short sighted.
What about BAQ designer would stop them from performing an unnecessary filter or inaccurate join apart from the intellisense/suggested joins that they’d probably delete or ignore anyways?