Find BAQs that are in use for BAQ cleanup

Epicor ERP 10
1

I’m trying to clean up BAQs that have accumulated in our system and have no purpose, so I want to see the full list of which are being used, such as part of a Dashboard. I was expecting to find an “inUse” boolean field or something similar in Ice.QueryHdr, but it’s not there.

I also tried looking at Dashboard data to see if there was a mapping to BAQ(s) used by the Dashboard, but it looks like that info is buried in some XML inside the Ice.DashBdDef.DashboardSchema field.

Has anyone found a nice way to get a list of BAQs that are in use by other things?

Thanks.

2

In the BAQ designer there is a tab called “Where Used”. Open up a BAQ that you know of. Then look at this tab. See where the BAQ is used. There are five options, and three are the most important. Dashboard, Quick Search, and BAQ Report. Now open Help > Field Help. Click the ID field in the tab you are interested in. There you can see the table the ID is stored in. In my testing, I have a BAQ that feeds a dashboard. So, I can make a new BAQ to query the DashBdBAQ table for QueryIDs. If you figure out the tables for the other three categories, then you can union all these results together in a single BAQ that shows all the BAQs, and which (if any) elements are using them. It is also helpful to look at the last updated date for the BAQ. You can see this when you search for a BAQ to open at the far-right side of the columns. I have so many versions of the same BAQ that I often use this to tell which was the last BAQ I was working on. Use the QueryHdr to get a list of all the BAQs.

2 Likes
3

Thanks, that’s what I was looking for!

Based on that, I wrote some SQL which shows if a BAQ is used for any of a Dashboard, a BAQ Report, or a Quick Search. I also pulled in the extra fields that I thought where interesting. This would probably make a good dashboard one day.

with inUse as (
	select
	query.QueryID,
	query.[Description] as 'queryDescription',
	query.IsGlobal,
	query.IsShared,
	query.Updatable,
	query.ExtQuery,
	query.SystemFlag,
	dashBdBAQ.DefinitionID as 'dashID',
	dashDef.[Description] as 'dashDescription',
	br.BAQRptID,
	br.[Description] as 'BAQReportDescription',
	br.SSRSReportName,
	qs.QuickSearchID,
	qs.[Description] as 'QuickSearchDescription',
	qs.LIkeDataFieldTableID,
	qs.LikeDataFieldName,
	qs.CallFrom
	from Ice.QueryHdr as query
	left join Ice.dashBdBAQ on query.QueryID = dashBdBAQ.QueryID
	left join Ice.DashBdDef as dashDef on dashDef.DefinitionID = dashBdBAQ.DefinitionID
	left join Ice.BaqReport as br on br.ExportID = query.QueryID
	left join Ice.QuickSearch as qs on qs.ExportID = query.QueryID
	where 
	query.SystemFlag = 0
)
select 
inUse.*,
case 
	when dashID is null then 0 else 1
end as 'inUse_dashboard',
case
	when BAQRptID is null then 0 else 1
end as 'inUse_BAQReport',
case
	when QuickSearchID is null then 0 else 1
end as 'inUse_QuickSearch',
case 
	when (dashID is null and BAQRptID is null and QuickSearchID is null) then 0 else 1
end as 'inUse_Any'
from inUse
order by inUse.queryID
3 Likes
4

The only thing to watch for (unless I’m mistaken) is if you’re using a BAQ in a BAQ DataView… I don’t think it appears in the “BAQ Where Used”. Any thoughts on how to verify those?

5

True! If your customization points to a BAQ to populate a BAQ dataview, then all you have is the BAQ name to reference against. In these cases, it would be wise to review all the customizations where a BAQ dataview is used, then check the BAQs used by those views. This is manual, but the list should be small.

6

Right. Just wanted to throw it out as a caution since @Potato was looking to “clean up” BAQs in their system. May delete one that is tied to a View somewhere and then realize too late that grids stop populating and/or errors start flying.

1 Like
7

bird delete this GIF by Skab

8

Would mean extra steps… but to be cautious… I would copy any BAQ you think you can delete and add XXX to the name. Then delete the original. Wait a couple months and if nothing breaks, you can delete the copies.

But this way, if something DOES break, you could hopefully make a new copy of the “XXX” copy and revert back to the original name… which should hopefully reconnect any broken Views.

A BAQ back-up if you will. Better than having to rebuild from scratch.

2 Likes