How to Query BAQ Where Used

Kinetic ERP
,
1

Good morning,
I am looking at the Ice.QueryHdr table and wondering if there is a table that links to show where used, as in the BAQ designer. I’d like to query the locations that all my BAQs are used, so I can export it. Is that possible?
Thanks for your time!

2

I think its the ice.DashBdBAQ table. You can link on the QueryID field and related where used field is ‘DefinitionID’.

Please note never actually used this so please validate!

EDIT: just realised this is only for Dashboards… hopefully some better answers come.

3

It works! This shows the locations of all dashboards that use a particular BAQ. Awesome! What about all those other where used locations?

4

I’m actually able to see this in field help (classic)… but only sometimes :person_shrugging:

image

So I guess if you can find a BAQ for all the where used types, then you could find it out. But doesn’t feel a very efficient method. I’m interested this as well so will keep looking when I have a chance, and please could you post if you find all the table names?

1 Like
5

I use this monster for upgrade prep (It’s not efficient, but it gets the job done):

WITH u AS
(
    SELECT  DISTINCT QueryID, WhereUsed = 'Dashboard'
    FROM    Ice.DashBdBAQ
    WHERE   SystemFlag = 0
    UNION ALL
    SELECT  DISTINCT ExportID, 'QuickSearch'
    FROM    Ice.QuickSearch
    WHERE   SystemFlag = 0
    UNION ALL
    SELECT  DISTINCT ExportID, 'BAQReport'
    FROM    Ice.BAQReport
    WHERE   SystemFlag = 0
    UNION ALL
    SELECT  DISTINCT QueryID, 'ReportList'
    FROM    Ice.RptTable
    WHERE   NOT ISNULL(QueryID, '') = '' AND SystemFlag = 0
    UNION ALL
    SELECT  DISTINCT QueryID, 'Function'
    FROM    Ice.QueryHdr h, Ecf.EfxFunction f
    WHERE   f.Body LIKE '%' + h.QueryID + '%'
    UNION ALL
    SELECT  DISTINCT QueryID, 'BPM'
    FROM    Ice.QueryHdr h, Ice.BpDirective b
    WHERE   CAST(b.Body AS nvarchar(MAX)) LIKE '%' + h.QueryID + '%'
    UNION ALL
    SELECT  DISTINCT QueryID, 'Customization'
    FROM    Ice.QueryHdr h, Ice.XXXDef f
    WHERE   f.TypeCode = 'Customization' AND f.Content LIKE '%' + h.QueryID + '%'
)
    ,uc AS
(
    SELECT      QueryID, Usage = STRING_AGG(WhereUsed, '~')
    FROM        u
    GROUP BY    QueryID
)
    ,stats AS
(
    SELECT      QueryID = Key1, LastUsed = MAX(CAST(Key2 AS date)), Uses = COUNT(*)
    FROM        Ice.UD20
    GROUP BY    Key1
)
SELECT      Task = CAST(1 AS bit)
           ,Scope = CASE
                        WHEN IsGlobal = 1 THEN
                            'Global'
                        ELSE
                            q.Company
                    END
           ,BAQType = CASE
                          WHEN Updatable = 1 THEN
                              'Updatable'
                          WHEN ExtQuery = 1 THEN
                              'External'
                          ELSE
                              'Standard'
                      END
           ,q.QueryID
           ,q.Description
           ,AuthorID
           ,q.IsShared
           ,Usage = ISNULL(uc.Usage, '')
           ,s.LastUsed
           ,Uses = ISNULL(s.Uses, 0)
FROM        Ice.QueryHdr q
            LEFT JOIN uc ON q.QueryID = uc.QueryID
            LEFT JOIN stats s ON q.QueryID = s.QueryID
WHERE       SystemFlag = 0
ORDER BY    AuthorID, QueryID;

Note, you can remove the stats portion … I have a BPM that logs when each query is called.

2 Likes
6

Isn’t there a way to import SQL into a BAQ now? I am not sure how to rebuild all that without just importing it. Do I need to import in the kinetic BAQ editor? I don’t see the place to paste it in.

7

The export was already written by @klincecum:

Now if you want import too…

Make the software more friendly for DevOps | ERP-I-139

(But I’m sure you have already voted for it.)

1 Like
8

Unless I am missing something, this doesn’t show where BAQs are used, it just exports them to a ZIP file. Useful? Yes. But not exactly what I needed. A least your comment got me to finally get this setup! Thanks @klincecum, the export tool is great!

1 Like
9

For what the system can see, there is a BO Method.

Ice.BO.BAQDesigner.GetUsedInBAQList

If you want to look in code, well that’s a bit more involved.

2 Likes
10

That gets a bit outside of the simple BAQ realm. But glad to know there is a way! Thanks!