I’m trying to figure out what’s different between two different queries in a dashboard that would make the context menus behave differently. Both are pulling the same field from the same table (it’s a UD table with the like values set up) one works, the other does not. It’s a job number.
The one that works has most of the stuff on the top level, which is why I suspect it works. The second has most of the stuff on one sub-query down because of the way I needed to filter the related operation so I could get all of my rows.
Below is the SQL if it helps. It seems like somewhere it just loses it’s like property (in a dashboard, it works fine in the query). But only on the one with the extra level. Do I need to go into the extended attributes in the a BAQ to get them to work again? Looking for PartNum and JobNum to work again in all places in the dashboard.
Any pointers would be appreciated.
Working
select
[UD02].[CreatedAt_c] as [UD02_CreatedAt_c],
[UD02].[JobNum_c] as [UD02_JobNum_c],
[UD02].[Num_c] as [UD02_Num_c],
[UD02].[Key1] as [UD02_Key1],
[UD02].[TagNum_c] as [UD02_TagNum_c],
[UD02].[UpdatedAt_c] as [UD02_UpdatedAt_c],
[UD02].[Weight_c] as [UD02_Weight_c],
(EmpBasic.FirstName + ' ' + EmpBasic.LastName) as [Calculated_AddedBy],
[UD02].[TruckID_c] as [UD02_TruckID_c],
[PartCount].[Calculated_Totalparts] as [Calculated_Totalparts],
[PartCount].[Calculated_UniqueNum] as [Calculated_UniqueNum],
[UD04].[ShippedAt_c] as [UD04_ShippedAt_c],
((case when UD04.ShippedAt_c is not null then 1 else 0 end)) as [Calculated_Shipped],
[UD04].[Num_c] as [UD04_Num_c],
[UD05].[Text_c] as [UD05_Text_c],
[PhotoCount].[Calculated_PhotoCount] as [Calculated_PhotoCount]
from Ice.UD02 as UD02
left outer join Ice.UD04 as UD04 on
UD02.Company = UD04.Company
and UD02.JobNum_c = UD04.JobNum_c
and UD02.TruckID_c = UD04.Key1
left outer join Erp.EmpBasic as EmpBasic on
UD02.Company = EmpBasic.Company
and UD02.AddedToTruckByEmpID_c = EmpBasic.EmpID
left outer join (select
[UD03].[Company] as [UD03_Company],
[UD03].[LiftID_c] as [UD03_LiftID_c],
(sum( UD03.Amount_c )) as [Calculated_Totalparts],
(count( UD03.PartNum_c )) as [Calculated_UniqueNum]
from Ice.UD03 as UD03
group by [UD03].[Company],
[UD03].[LiftID_c]) as PartCount on
UD02.Company = PartCount.UD03_Company
and UD02.Key1 = PartCount.UD03_LiftID_c
left outer join Ice.UD05 as UD05 on
UD02.Company = UD05.Company
and UD02.Key1 = UD05.CommentableID_c
and ( UD05.Visibility_c = 'public' )
left outer join (select
[UD06].[Company] as [UD06_Company],
[UD06].[PhotoableID_c] as [UD06_PhotoableID_c],
(count( UD06.Key1 )) as [Calculated_PhotoCount]
from Ice.UD06 as UD06
group by [UD06].[Company],
[UD06].[PhotoableID_c]) as PhotoCount on
UD02.Company = PhotoCount.UD06_Company
and UD02.Key1 = PhotoCount.UD06_PhotoableID_c
Not Working
select
[OldTop].[UD03_Amount_c] as [UD03_Amount_c],
[OldTop].[UD03_CreatedAt_c] as [UD03_CreatedAt_c],
[OldTop].[UD03_EmpID_c] as [UD03_EmpID_c],
[OldTop].[UD03_LiftID_c] as [UD03_LiftID_c],
[OldTop].[UD03_PartNum_c] as [UD03_PartNum_c],
[OldTop].[UD03_PartUOM_c] as [UD03_PartUOM_c],
[OldTop].[UD03_UpdatedAt_c] as [UD03_UpdatedAt_c],
[OldTop].[UD02_JobNum_c] as [UD02_JobNum_c],
[mtlparts].[JobMtl_Company] as [JobMtl_Company],
[mtlparts].[JobMtl_JobNum] as [JobMtl_JobNum],
[mtlparts].[JobMtl_PartNum] as [JobMtl_PartNum],
[mtlparts].[JobOper_OpCode] as [JobOper_OpCode],
[OldTop].[Calculated_LiftNum] as [Calculated_LiftNum],
[OldTop].[Calculated_Name] as [Calculated_Name],
[OldTop].[UD02_Num_c] as [UD02_Num_c],
[OldTop].[UD03_Company] as [UD03_Company],
[OldTop].[UD04_Num_c] as [UD04_Num_c],
[OldTop].[UD05_Text_c] as [UD05_Text_c]
from (select
[UD03].[Company] as [UD03_Company],
[UD03].[Amount_c] as [UD03_Amount_c],
[UD03].[CreatedAt_c] as [UD03_CreatedAt_c],
[UD03].[EmpID_c] as [UD03_EmpID_c],
[UD03].[LiftID_c] as [UD03_LiftID_c],
[UD03].[PartNum_c] as [UD03_PartNum_c],
[UD03].[PartUOM_c] as [UD03_PartUOM_c],
[UD03].[UpdatedAt_c] as [UD03_UpdatedAt_c],
(EmpBasic.FirstName + ' ' + EmpBasic.LastName) as [Calculated_Name],
[UD02].[JobNum_c] as [UD02_JobNum_c],
('lift' +' ' + convert(varchar,UD02.Num_c)) as [Calculated_LiftNum],
[UD02].[Num_c] as [UD02_Num_c],
[UD04].[Key1] as [UD04_Key1],
[UD04].[Num_c] as [UD04_Num_c],
[UD05].[Text_c] as [UD05_Text_c],
[UD05LiftComments].[Text_c] as [UD05LiftComments_Text_c]
from Ice.UD03 as UD03
inner join Erp.EmpBasic as EmpBasic on
UD03.Company = EmpBasic.Company
and UD03.EmpID_c = EmpBasic.EmpID
inner join Ice.UD02 as UD02 on
UD03.Company = UD02.Company
and UD03.LiftID_c = UD02.Key1
left outer join Ice.UD04 as UD04 on
UD02.Company = UD04.Company
and UD02.TruckID_c = UD04.Key1
left outer join Ice.UD05 as UD05LiftComments on
UD02.Company = UD05LiftComments.Company
and UD02.Key1 = UD05LiftComments.CommentableID_c
left outer join Ice.UD05 as UD05 on
UD03.Company = UD05.Company
and UD03.Key1 = UD05.CommentableID_c) as OldTop
left outer join (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobMtl as JobMtl
inner join Erp.JobAsmbl as JobAsmbl1 on
JobMtl.Company = JobAsmbl1.Company
and JobMtl.JobNum = JobAsmbl1.JobNum
and JobMtl.AssemblySeq = JobAsmbl1.AssemblySeq
inner join Erp.JobOper as JobOper on
JobMtl.JobNum = JobOper.JobNum
and JobMtl.AssemblySeq = JobOper.AssemblySeq
and JobMtl.RelatedOperation = JobOper.OprSeq
inner join Erp.JobOper as JobOper
and
JobAsmbl1.Company = JobOper.Company
and JobAsmbl1.JobNum = JobOper.JobNum
and JobAsmbl1.AssemblySeq = JobOper.AssemblySeq
union
select
[JobAsmbl].[Company] as [JobAsmbl_Company],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobOper1].[OpCode] as [JobOper1_OpCode]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobOper as JobOper1 on
JobAsmbl.Company = JobOper1.Company
and JobAsmbl.JobNum = JobOper1.JobNum
and JobAsmbl.Parent = JobOper1.AssemblySeq
and JobAsmbl.RelatedOperation = JobOper1.OprSeq) as mtlparts on
OldTop.UD03_Company = mtlparts.JobMtl_Company
and OldTop.UD03_PartNum_c = mtlparts.JobMtl_PartNum
and OldTop.UD02_JobNum_c = mtlparts.JobMtl_JobNum
and ( mtlparts.JobOper_OpCode = 'ASSY' )