I have a classic UX dashboard that I have added a combo box tracker to filter ProdGrup.ProdCode. I have 2 grids showing the quote headers and another to show the quote details when the header is selected. The baq query for the quote header grid has a sales group field that is a calculated field which finds the ProdCode of the first detail line. I have added a combo box tracker to the quote header grid which prefills with al the product groups using a Erp.Adapters.Controls.ProdGrupCombo. This combo box fills correctly, but when I select a group to filter on, it takes a while to complete and return all quote details lines in the systems that belong to the selected product group, the quote header grid remains empty. Is there a better way to do this? Why is the tracker on one grid filtering the other grid?
If you are using the same query, (I can’t see your dashboard elements, so I don’t know what you have set up), a tracker is for the query, not for the grid. So if you want those separately filtered, you need to bring in the same query twice.
As far as the drop down, I would see if you can set the like property in the BAQ and see if the dashboard automatically gives you the drop down.
If that doesn’t work I would still do the changes in the BAQ by making the BAQ update able, make it “Advanced BPM Only” then you have access to the advanced column editor, and you can set the acceptable fields there. I usually use another BAQ.
I usually avoid “Customizing” the dashboard for drop downs, because it makes it hard to change later, and it’s actually easier to just make the adjustments in the BAQ.
Here’s how to make a BAQ automatically give custom drop downs. This will work for the tracker filters, even if you don’t use any update functionality.
1 Like
Sorry, I didn’t give all the information.The top query list all quotes based on the trackers. The bottom query displays the details of the selected quote from the top query. Selecting the Sales Group tracker combo box is filtering the bottom query, returning all quote detail lines that match the selected product group. The top quote list is empty.
Quote List Query
select [SalesRep].[Name] as [SalesRep_Name],
[QuoteHed].[Quoted] as [QuoteHed_Quoted],
[QuoteHed].[FollowUpDate] as [QuoteHed_FollowUpDate],
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[QuoteHed].[ParentQuoteNum] as [QuoteHed_ParentQuoteNum],
[CustGrup].[GroupDesc] as [CustGrup_GroupDesc],
((
(
select top (1)
[ProdGrup].[Description] as [ProdGrup_Description]
from Erp.QuoteDtl as QuoteDtl3
left outer join Erp.ProdGrup as ProdGrup
on QuoteDtl3.Company = ProdGrup.Company
and QuoteDtl3.ProdCode = ProdGrup.ProdCode
where (QuoteDtl3.QuoteNum = QuoteHed.QuoteNum)
)
)
) as [Calculated_SalesGroup],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[QuoteHed].[CustRef_c] as [QuoteHed_CustRef_c],
(IsNull(QuoteCnt.Name, '')) as [Calculated_CustomerContact],
[QuoteHed].[DueDate] as [QuoteHed_DueDate],
[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
[QuoteHed].[ExpirationDate] as [QuoteHed_ExpirationDate],
[QuoteHed].[ConfidencePct] as [QuoteHed_ConfidencePct],
[QuoteHed].[CurrencyCode] as [QuoteHed_CurrencyCode],
[QuoteHed].[QuoteAmt] as [QuoteHed_QuoteAmt],
[QuoteHed].[DocTotalPotential] as [QuoteHed_DocTotalPotential],
[SubQuery3].[Calculated_FreightPackagingPrice] as [Calculated_FreightPackagingPrice],
[SubQuery3].[Calculated_DocFreightPackagingPrice] as [Calculated_DocFreightPackagingPrice],
[SubQuery2].[Calculated_totalMtlCost] as [Calculated_totalMtlCost],
[SubQuery2].[Calculated_totalMtlBurCost] as [Calculated_totalMtlBurCost],
[SubQuery4].[Calculated_totalLabourCost] as [Calculated_totalLabourCost],
[SubQuery4].[Calculated_totalBurdenCost] as [Calculated_totalBurdenCost],
[SubQuery4].[Calculated_totalSubcontractCost] as [Calculated_totalSubcontractCost],
((IsNull(SubQuery4.Calculated_totalBurdenCost, 0) + IsNull(SubQuery4.Calculated_totalLabourCost, 0)
+ IsNull(SubQuery2.Calculated_totalMtlCost, 0) + IsNull(SubQuery4.Calculated_totalSubcontractCost, 0)
+ IsNull(SubQuery2.Calculated_totalMtlBurCost, 0)
)
) as [Calculated_totalCost],
((QuoteHed.QuoteAmt - IsNull(SubQuery3.Calculated_FreightPackagingPrice, 0) - totalCost)) as [Calculated_EstMarginCost],
((case
when (QuoteHed.QuoteAmt - IsNull(SubQuery3.Calculated_FreightPackagingPrice, 0)) > 0 then
EstMarginCost / (QuoteHed.QuoteAmt - IsNull(SubQuery3.Calculated_FreightPackagingPrice, 0))
else
0
end
)
) as [Calculated_EstMarginPerc],
[QuoteHed].[CurrentStage] as [QuoteHed_CurrentStage],
((case
when QuoteHed.Ordered = 1 then
'WON'
else
''
end
)
) as [Calculated_FlagWon],
(case
when IsNull(SubQuery10.Task_Conclusion, 'NEXT') = 'WIN'
and QuoteHed.Ordered = 1 then
'WON'
when IsNull(SubQuery10.Task_Conclusion, 'NEXT') = 'WIN'
and QuoteHed.Ordered = 0 then
'LOST'
when IsNull(SubQuery10.Task_Conclusion, 'NEXT') = 'LOSE' then
'LOST'
when IsNull(SubQuery10.Task_Conclusion, 'NEXT') = 'NEXT' then
'OPEN'
else
IsNull(SubQuery10.Task_Conclusion, 'NEXT')
end
) as [Calculated_OrderStatus],
[SubQuery10].[Calculated_TaskReason] as [Calculated_TaskReason],
[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[GroupCode] as [Customer_GroupCode],
((case
when (QuoteHed.QuoteAmt - IsNull(SubQuery3.Calculated_FreightPackagingPrice, 0)) > 0 then
EstMarginCost / (QuoteHed.QuoteAmt - IsNull(SubQuery3.Calculated_FreightPackagingPrice, 0)) * 100
else
0
end
)
) as [Calculated_MarginPerc],
(IsNull(UDCodes.CodeDesc, '')) as [Calculated_QuoteType],
[QuoteHed].[EntryDate] as [QuoteHed_EntryDate],
(datepart(year, QuoteHed.EntryDate)) as [Calculated_EntryYear]
from Erp.QuoteHed as QuoteHed
left outer join Erp.QSalesRP as QSalesRP
on QuoteHed.Company = QSalesRP.Company
and QuoteHed.QuoteNum = QSalesRP.QuoteNum
and (QSalesRP.PrimeRep = 1)
inner join Erp.SalesRep as SalesRep
on QSalesRP.Company = SalesRep.Company
and QSalesRP.SalesRepCode = SalesRep.SalesRepCode
inner join Erp.Customer as Customer
on QuoteHed.Company = Customer.Company
and QuoteHed.CustNum = Customer.CustNum
left outer join Erp.CustGrup as CustGrup
on Customer.Company = CustGrup.Company
and Customer.GroupCode = CustGrup.GroupCode
left outer join Erp.QuoteCnt as QuoteCnt
on QuoteHed.Company = QuoteCnt.Company
and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
and (QuoteCnt.PrimeContact = 1)
inner join
(
select [QuoteHed1].[QuoteNum] as [QuoteHed1_QuoteNum],
(Sum(QuoteDtl1.DocExtPriceDtl)) as [Calculated_DocFreightPackagingPrice],
(Sum(QuoteDtl1.ExtPriceDtl)) as [Calculated_FreightPackagingPrice]
from Erp.QuoteHed as QuoteHed1
left outer join Erp.QuoteDtl as QuoteDtl1
on QuoteHed1.Company = QuoteDtl1.Company
and QuoteHed1.QuoteNum = QuoteDtl1.QuoteNum
and (
QuoteDtl1.PartNum = 'FRTI'
or QuoteDtl1.PartNum = 'FRTP'
)
group by [QuoteHed1].[QuoteNum]
) as SubQuery3
on QuoteHed.QuoteNum = SubQuery3.QuoteHed1_QuoteNum
left outer join
(
select [QuoteDtl2].[QuoteNum] as [QuoteDtl2_QuoteNum],
(Sum(QuoteOpr.ProdLabRate * QuoteOpr.QtyPer * QuoteOpr.ProdStandard * QuoteDtl2.SellingExpectedQty)) as [Calculated_totalLabourCost],
(sum(QuoteOpr.ProdBurRate * QuoteOpr.QtyPer * QuoteOpr.ProdStandard * QuoteDtl2.SellingExpectedQty)) as [Calculated_totalBurdenCost],
(Sum(QuoteOpr.EstUnitCost * QuoteOpr.QtyPer * QuoteDtl2.SellingExpectedQty)) as [Calculated_totalSubcontractCost]
from Erp.QuoteOpr as QuoteOpr
inner join Erp.QuoteDtl as QuoteDtl2
on QuoteOpr.Company = QuoteDtl2.Company
and QuoteOpr.QuoteNum = QuoteDtl2.QuoteNum
and QuoteOpr.QuoteLine = QuoteDtl2.QuoteLine
group by [QuoteDtl2].[QuoteNum]
) as SubQuery4
on QuoteHed.QuoteNum = SubQuery4.QuoteDtl2_QuoteNum
left outer join
(
select [QuoteMtl].[QuoteNum] as [QuoteMtl_QuoteNum],
(Sum(QuoteMtl.QtyPer * QuoteMtl.EstMtlUnitCost / SubQuery6.Calculated_ConvToBase
* SubQuery7.Calculated_ConvFromBase * QuoteDtl.SellingExpectedQty
)
) as [Calculated_totalMtlCost],
(Sum(quotemtl.qtyper * quotemtl.EstMtlBurUnitCost / SubQuery6.Calculated_ConvToBase
* SubQuery7.Calculated_ConvFromBase * QuoteDtl.SellingExpectedQty
)
) as [Calculated_totalMtlBurCost]
from Erp.QuoteMtl as QuoteMtl
left outer join
(
select distinct
[QuoteMtl1].[Company] as [QuoteMtl1_Company],
[QuoteMtl1].[QuoteNum] as [QuoteMtl1_QuoteNum],
[QuoteMtl1].[PartNum] as [QuoteMtl1_PartNum],
[Part].[IUM] as [Part_IUM],
(IsNull(PartUOM.ConvFactor, 1)) as [Calculated_ConvToBase]
from Erp.PartUOM as PartUOM
right outer join Erp.Part as Part
on PartUOM.Company = Part.Company
and PartUOM.PartNum = Part.PartNum
and PartUOM.UOMCode = Part.IUM
right outer join Erp.QuoteMtl as QuoteMtl1
on Part.Company = QuoteMtl1.Company
and Part.PartNum = QuoteMtl1.PartNum
) as SubQuery6
on QuoteMtl.Company = SubQuery6.QuoteMtl1_Company
and QuoteMtl.QuoteNum = SubQuery6.QuoteMtl1_QuoteNum
and QuoteMtl.PartNum = SubQuery6.QuoteMtl1_PartNum
left outer join
(
select distinct
[QuoteMtl4].[Company] as [QuoteMtl4_Company],
[QuoteMtl4].[QuoteNum] as [QuoteMtl4_QuoteNum],
[QuoteMtl4].[PartNum] as [QuoteMtl4_PartNum],
[QuoteMtl4].[IUM] as [QuoteMtl4_IUM],
(IsNull(PartUOM1.ConvFactor, 1)) as [Calculated_ConvFromBase]
from Erp.QuoteMtl as QuoteMtl4
left outer join Erp.PartUOM as PartUOM1
on QuoteMtl4.Company = PartUOM1.Company
and QuoteMtl4.PartNum = PartUOM1.PartNum
and QuoteMtl4.IUM = PartUOM1.UOMCode
) as SubQuery7
on QuoteMtl.Company = SubQuery7.QuoteMtl4_Company
and QuoteMtl.QuoteNum = SubQuery7.QuoteMtl4_QuoteNum
and QuoteMtl.PartNum = SubQuery7.QuoteMtl4_PartNum
and QuoteMtl.IUM = SubQuery7.QuoteMtl4_IUM
inner join Erp.QuoteDtl as QuoteDtl
on QuoteMtl.Company = QuoteDtl.Company
and QuoteMtl.QuoteNum = QuoteDtl.QuoteNum
and QuoteMtl.QuoteLine = QuoteDtl.QuoteLine
group by [QuoteMtl].[QuoteNum]
) as SubQuery2
on QuoteHed.QuoteNum = SubQuery2.QuoteMtl_QuoteNum
left outer join
(
select [Task].[Company] as [Task_Company],
[Task].[Key1] as [Task_Key1],
[Task].[TaskQuoteNum] as [Task_TaskQuoteNum],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[Conclusion] as [Task_Conclusion],
(IsNull(Reason.Description, '')) as [Calculated_TaskReason]
from Erp.Task as Task
inner join
(
select [Task1].[Company] as [Task1_Company],
[Task1].[Key1] as [Task1_Key1],
(Max(Task1.TaskSeqNum)) as [Calculated_MaxSeq]
from Erp.Task as Task1
where (Task1.RelatedToFile = 'QuoteHed')
group by [Task1].[Company],
[Task1].[Key1]
) as SubQuery9
on Task.Company = SubQuery9.Task1_Company
and Task.Key1 = SubQuery9.Task1_Key1
and Task.TaskSeqNum = SubQuery9.Calculated_MaxSeq
left outer join Erp.Reason as Reason
on Task.Company = Reason.Company
and Task.ReasonCode = Reason.ReasonCode
) as SubQuery10
on QuoteHed.Company = SubQuery10.Task_Company
and QuoteHed.QuoteNum = SubQuery10.Task_TaskQuoteNum
left outer join Ice.UDCodes as UDCodes
on QuoteHed.QuoteType_c = UDCodes.CodeID
and (UDCodes.CodeTypeID = 'QuoteType')
order by QuoteHed.FollowUpDate,
Customer.Name,
QuoteHed.QuoteNum
Quote Detail Query
select [QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
[QuoteDtl].[SellingExpectedQty] as [QuoteDtl_SellingExpectedQty],
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteHed].[CurrencyCode] as [QuoteHed_CurrencyCode],
[QuoteDtl].[DocExtPriceDtl] as [QuoteDtl_DocExtPriceDtl],
((case
when QuoteDtl.ExtPriceDtl > 0 then
(QuoteDtl.ExtPriceDtl - totalCost) / QuoteDtl.ExtPriceDtl
else
0
end
)
) as [Calculated_EstMarginPerc],
(QuoteDtl.SellingExpectedQty
* (IsNull(SubQuery4.Calculated_totalBurdenCost, 0) + IsNull(SubQuery4.Calculated_totalLabourCost, 0)
+ IsNull(SubQuery2.Calculated_totalMtlCost, 0) + IsNull(SubQuery4.Calculated_totalSubcontractCost, 0)
+ IsNull(SubQuery2.Calculated_totalMtlBurCost, 0)
)
) as [Calculated_totalCost],
[SubQuery4].[Calculated_totalLabourHrs] as [Calculated_totalLabourHrs],
((QuoteDtl.ExtPriceDtl - totalCost)) as [Calculated_EstMarginCost],
((case
when IsNull(SubQuery4.Calculated_totalLabourHrs, 0) > 0 then
QuoteDtl.ExtPriceDtl / SubQuery4.Calculated_totalLabourHrs
else
0
end
)
) as [Calculated_shippedPerHr],
((case
when IsNull(SubQuery4.Calculated_totalLabourHrs, 0) > 0 then
EstMarginCost / SubQuery4.Calculated_totalLabourHrs
else
0
end
)
) as [Calculated_MarginPerHr],
((case
when QuoteDtl.ExtPriceDtl > 0 then
(QuoteDtl.ExtPriceDtl - totalCost) / QuoteDtl.ExtPriceDtl * 100
else
0
end
)
) as [Calculated_MarginPerc]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed
on QuoteDtl.Company = QuoteHed.Company
and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
left outer join
(
select [QuoteHed3].[QuoteNum] as [QuoteHed3_QuoteNum],
[QuoteMtl].[QuoteLine] as [QuoteMtl_QuoteLine],
(Sum(QuoteMtl.QtyPer * QuoteMtl.EstMtlUnitCost / SubQuery6.Calculated_ConvToBase
* SubQuery7.Calculated_ConvFromBase
)
) as [Calculated_totalMtlCost],
(Sum(quotemtl.qtyper * quotemtl.EstMtlBurUnitCost / SubQuery6.Calculated_ConvToBase
* SubQuery7.Calculated_ConvFromBase
)
) as [Calculated_totalMtlBurCost]
from Erp.QuoteHed as QuoteHed3
inner join Erp.QuoteMtl as QuoteMtl
on QuoteHed3.Company = QuoteMtl.Company
and QuoteHed3.QuoteNum = QuoteMtl.QuoteNum
left outer join
(
select distinct
[QuoteMtl1].[Company] as [QuoteMtl1_Company],
[QuoteMtl1].[QuoteNum] as [QuoteMtl1_QuoteNum],
[QuoteMtl1].[QuoteLine] as [QuoteMtl1_QuoteLine],
[QuoteMtl1].[PartNum] as [QuoteMtl1_PartNum],
[Part].[IUM] as [Part_IUM],
(IsNull(PartUOM.ConvFactor, 1)) as [Calculated_ConvToBase]
from Erp.PartUOM as PartUOM
right outer join Erp.Part as Part
on PartUOM.Company = Part.Company
and PartUOM.PartNum = Part.PartNum
and PartUOM.UOMCode = Part.IUM
right outer join Erp.QuoteMtl as QuoteMtl1
on Part.Company = QuoteMtl1.Company
and Part.PartNum = QuoteMtl1.PartNum
) as SubQuery6
on QuoteMtl.Company = SubQuery6.QuoteMtl1_Company
and QuoteMtl.QuoteNum = SubQuery6.QuoteMtl1_QuoteNum
and QuoteMtl.QuoteLine = SubQuery6.QuoteMtl1_QuoteLine
and QuoteMtl.PartNum = SubQuery6.QuoteMtl1_PartNum
left outer join
(
select distinct
[QuoteMtl4].[Company] as [QuoteMtl4_Company],
[QuoteMtl4].[QuoteNum] as [QuoteMtl4_QuoteNum],
[QuoteMtl4].[QuoteLine] as [QuoteMtl4_QuoteLine],
[QuoteMtl4].[PartNum] as [QuoteMtl4_PartNum],
[QuoteMtl4].[IUM] as [QuoteMtl4_IUM],
(IsNull(PartUOM1.ConvFactor, 1)) as [Calculated_ConvFromBase]
from Erp.QuoteMtl as QuoteMtl4
left outer join Erp.PartUOM as PartUOM1
on QuoteMtl4.Company = PartUOM1.Company
and QuoteMtl4.PartNum = PartUOM1.PartNum
and QuoteMtl4.IUM = PartUOM1.UOMCode
) as SubQuery7
on QuoteMtl.Company = SubQuery7.QuoteMtl4_Company
and QuoteMtl.QuoteNum = SubQuery7.QuoteMtl4_QuoteNum
and QuoteMtl.QuoteLine = SubQuery7.QuoteMtl4_QuoteLine
and QuoteMtl.PartNum = SubQuery7.QuoteMtl4_PartNum
group by [QuoteHed3].[QuoteNum],
[QuoteMtl].[QuoteLine]
) as SubQuery2
on QuoteDtl.QuoteNum = SubQuery2.QuoteHed3_QuoteNum
and QuoteDtl.QuoteLine = SubQuery2.QuoteMtl_QuoteLine
left outer join
(
select [QuoteHed1].[QuoteNum] as [QuoteHed1_QuoteNum],
[QuoteDtl1].[QuoteLine] as [QuoteDtl1_QuoteLine],
(Sum(QuoteDtl1.DocExtPriceDtl)) as [Calculated_DocFreightPackagingPrice],
(Sum(QuoteDtl1.ExtPriceDtl)) as [Calculated_FreightPackagingPrice]
from Erp.QuoteHed as QuoteHed1
left outer join Erp.QuoteDtl as QuoteDtl1
on QuoteHed1.Company = QuoteDtl1.Company
and QuoteHed1.QuoteNum = QuoteDtl1.QuoteNum
and (
QuoteDtl1.PartNum = 'FRTI'
or QuoteDtl1.PartNum = 'FRTP'
)
group by [QuoteHed1].[QuoteNum],
[QuoteDtl1].[QuoteLine]
) as SubQuery3
on QuoteDtl.QuoteNum = SubQuery3.QuoteHed1_QuoteNum
and QuoteDtl.QuoteLine = SubQuery3.QuoteDtl1_QuoteLine
left outer join
(
select [QuoteHed2].[QuoteNum] as [QuoteHed2_QuoteNum],
[QuoteOpr].[QuoteLine] as [QuoteOpr_QuoteLine],
(Sum(QuoteOpr.ProdLabRate * QuoteOpr.QtyPer * QuoteOpr.ProdStandard)) as [Calculated_totalLabourCost],
(sum(QuoteOpr.ProdBurRate * QuoteOpr.QtyPer * QuoteOpr.ProdStandard)) as [Calculated_totalBurdenCost],
(Sum(QuoteOpr.EstUnitCost * QuoteOpr.QtyPer)) as [Calculated_totalSubcontractCost],
(Sum(QuoteOpr.QtyPer * QuoteOpr.ProdStandard)) as [Calculated_totalLabourHrs]
from Erp.QuoteHed as QuoteHed2
inner join Erp.QuoteOpr as QuoteOpr
on QuoteHed2.Company = QuoteOpr.Company
and QuoteHed2.QuoteNum = QuoteOpr.QuoteNum
group by [QuoteHed2].[QuoteNum],
[QuoteOpr].[QuoteLine]
) as SubQuery4
on QuoteDtl.QuoteNum = SubQuery4.QuoteHed2_QuoteNum
and QuoteDtl.QuoteLine = SubQuery4.QuoteOpr_QuoteLine
So when you return nothing on the publishing grid, that’s like not filtering the subscribed grid at all. So you’re drop down isn’t filtering the top grid.
It’s probably because your value is the code, but the displayed is the description and it’s not matching up somewhere. The second query showing all is a symptom of your original problem in that the filter isn’t correctly.
If it were me, I would delete the customized tracker, start a new one with just the basic selections in the tracker setup screen. For the drop downs you want, use the BAQ adjustment that I showed you. First, try the like fields, if those don’t work, then the updateable trick.
Thank You. I never knew about this, what a time saver and no frustration using customized trackers.
1 Like