Appeal\Appeal record Node in Constituent Query looks in wrong table

When using Constituent view query , using Appeal\Appeal Record looks in wrong table and returns no results.
We are currently evaluating this issue and will update this article when we have more information.

Steps to Duplicate

User-added image

SQL view:

select  distinct   [V_QUERY_CONSTITUENT].[NAME] as [Name],
                [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal].[ID] as [Registrant\Event\Appeals\Appeal\System record ID],
[V_QUERY_CONSTITUENT].[ID] as [QUERYRECID]
from [dbo].[V_QUERY_CONSTITUENT] as [V_QUERY_CONSTITUENT]
left outer join [dbo].[V_QUERY_CONSTITUENTREGISTRANT] as [V_QUERY_CONSTITUENT\Registrant] on [V_QUERY_CONSTITUENT].[ID] = [V_QUERY_CONSTITUENT\Registrant].[CONSTITUENTID]
inner join [dbo].[V_QUERY_EVENT] as [V_QUERY_CONSTITUENT\Registrant\Event] on [V_QUERY_CONSTITUENT\Registrant].[EVENTID] = [V_QUERY_CONSTITUENT\Registrant\Event].[ID]
left outer join [dbo].[V_QUERY_EVENTAPPEAL] as [V_QUERY_CONSTITUENT\Registrant\Event\Appeals] on [V_QUERY_CONSTITUENT\Registrant\Event].[ID] = [V_QUERY_CONSTITUENT\Registrant\Event\Appeals].[EVENTID]
left outer join [dbo].[V_QUERY_APPEAL] as [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal] on [V_QUERY_CONSTITUENT\Registrant\Event\Appeals].[APPEALID] = [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal].[ID]
inner join [dbo].[V_QUERY_REVENUE] as [V_QUERY_CONSTITUENT\Revenue] on [V_QUERY_CONSTITUENT].[ID] = [V_QUERY_CONSTITUENT\Revenue].[CONSTITUENTID]
inner join [dbo].[V_QUERY_APPEAL] as [V_QUERY_CONSTITUENT\Revenue\Appeal] on [V_QUERY_CONSTITUENT\Revenue].[APPEALID] = [V_QUERY_CONSTITUENT\Revenue\Appeal].[ID]
where [V_QUERY_CONSTITUENT\Registrant\Event].[EVENTCATEGORYCODEID] = N'3842b215-414a-4ace-b8d6-c09dfe77d76d'
 and [V_QUERY_CONSTITUENT\Revenue\Appeal].[ID] = [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal].[ID]
 and [V_QUERY_CONSTITUENT\Revenue].[AMOUNT] > 0

The results come back as blank. This is because the query builder joins to the table highlighted above for event appeals, however this table is not populated. An AppealID exists in the event table and revenue table, so it could join straight to those instead:

select  distinct     [V_QUERY_CONSTITUENT].[NAME] as [Name],
       [V_QUERY_CONSTITUENT\Registrant\Event].[APPEALID] as [V_QUERY_CONSTITUENT\Registrant\Event\AppealID],
[V_QUERY_CONSTITUENT].[ID] as [QUERYRECID]
 
from [dbo].[V_QUERY_CONSTITUENT] as [V_QUERY_CONSTITUENT]
left outer join [dbo].[V_QUERY_CONSTITUENTREGISTRANT] as [V_QUERY_CONSTITUENT\Registrant] on [V_QUERY_CONSTITUENT].[ID] = [V_QUERY_CONSTITUENT\Registrant].[CONSTITUENTID]
inner join [dbo].[V_QUERY_EVENT] as [V_QUERY_CONSTITUENT\Registrant\Event] on [V_QUERY_CONSTITUENT\Registrant].[EVENTID] = [V_QUERY_CONSTITUENT\Registrant\Event].[ID]
left outer join [dbo].[V_QUERY_EVENTAPPEAL] as [V_QUERY_CONSTITUENT\Registrant\Event\Appeals] on [V_QUERY_CONSTITUENT\Registrant\Event].[ID] = [V_QUERY_CONSTITUENT\Registrant\Event\Appeals].[EVENTID]
left outer join [dbo].[V_QUERY_APPEAL] as [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal] on [V_QUERY_CONSTITUENT\Registrant\Event\Appeals].[APPEALID] = [V_QUERY_CONSTITUENT\Registrant\Event\Appeals\Appeal].[ID]
inner join [dbo].[V_QUERY_REVENUE] as [V_QUERY_CONSTITUENT\Revenue] on [V_QUERY_CONSTITUENT].[ID] = [V_QUERY_CONSTITUENT\Revenue].[CONSTITUENTID]
inner join [dbo].[V_QUERY_APPEAL] as [V_QUERY_CONSTITUENT\Revenue\Appeal] on [V_QUERY_CONSTITUENT\Revenue].[APPEALID] = [V_QUERY_CONSTITUENT\Revenue\Appeal].[ID]
 
where [V_QUERY_CONSTITUENT\Registrant\Event].[EVENTCATEGORYCODEID] = N'3842b215-414a-4ace-b8d6-c09dfe77d76d'
 and [V_QUERY_CONSTITUENT\Revenue\Appeal].[ID] = [V_QUERY_CONSTITUENT\Registrant\Event].[AppealID]
 and [V_QUERY_CONSTITUENT\Revenue].[AMOUNT] > 0

This produces the desired results.

Environment

 Blackbaud CRM
 4.0

Was this article helpful?