BAQ, ProjectTask.PersonID field is empty

Kinetic ERP
,
1

How can I populate the concatenated ProjectTask.PersonIDs into a field on a single row like base Epicor has it in the ProjectEntry->Checklist Tasks->List->DspPersonList field?

I tried the several suggestions in the topic: BAQ: Calculated Field Concatenate Rows to single field but couldn’t get any to work.

This is how it looks in the Project Entry screen and the DspPersonList is what I’m trying to get working in a BAQ so I can display it as a single row in a dashboard.

image
image792×585 140 KB

There is a field in the ProjectTask table called PersonList, but it always returns empty in the BAQ. I added the ProjectTaskPerson.PersonID field and it shows the results but on separate lines.

image
image1048×423 168 KB

BAQ Query Phrase:

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[Project].[ProjectID] as [Project_ProjectID],
	[Project].[Description] as [Project_Description],
	[ProjectTask].[ProjectID] as [ProjectTask_ProjectID],
	[Project].[EndDate] as [Project_EndDate],
	[ProjPhase].[Description] as [ProjPhase_Description],
	[ProjectTask].[Description] as [ProjectTask_Description],
	[ProjectTask].[CommentText] as [ProjectTask_CommentText],
	[ProjectTask].[StartDate] as [ProjectTask_StartDate],
	[ProjectTask].[DueDate] as [ProjectTask_DueDate],
	[ProjectTask].[OrigDueDate_c] as [ProjectTask_OrigDueDate_c],
	[ProjectTask].[Duration] as [ProjectTask_Duration],
	[ProjectTask].[DurationType] as [ProjectTask_DurationType],
	[ProjectTask].[PercentComplete] as [ProjectTask_PercentComplete],
	[ProjectTask].[TaskStatus] as [ProjectTask_TaskStatus],
	[ProjectTask].[DateComplete] as [ProjectTask_DateComplete],
	[ProjectTask].[PersonID] as [ProjectTask_PersonID],
	[Person1].[Name] as [Person1_Name],
	[ProjectTaskPerson].[PersonID] as [ProjectTaskPerson_PersonID],
	[Person].[Name] as [Person_Name],
	[ProjectTask].[PhaseID] as [ProjectTask_PhaseID],
	[ProjectTask].[TaskID] as [ProjectTask_TaskID],
	(Case When ProjectTask.Description like 'EOAT%' Then 'True' Else 'False' End) as [Calculated_EOATTask],
	[ProjectTask].[PersonList] as [ProjectTask_PersonList],
	((case when (ProjectTask.Description like 'SW Model Complete%' and ProjectTask.TaskStatus = 'C' ) then 1 else 0 end)) as [Calculated_SWModelCompl]
from Erp.ProjectTask as ProjectTask
inner join Erp.Project as Project on 
	ProjectTask.Company = Project.Company
	and ProjectTask.ProjectID = Project.ProjectID
	and ( Project.ActiveProject = True  )

inner join Erp.ProjPhase as ProjPhase on 
	ProjectTask.Company = ProjPhase.Company
	and ProjectTask.ProjectID = ProjPhase.ProjectID
	and ProjectTask.PhaseID = ProjPhase.PhaseID
left outer join Erp.ProjectTaskPerson as ProjectTaskPerson on 
	ProjectTask.Company = ProjectTaskPerson.Company
	and ProjectTask.ProjectID = ProjectTaskPerson.ProjectID
	and ProjectTask.TaskID = ProjectTaskPerson.TaskID
left outer join Erp.Person as Person on 
	ProjectTaskPerson.Company = Person.Company
	and ProjectTaskPerson.PersonID = Person.PersonID
left outer join Erp.Person as Person1 on 
	ProjectTask.Company = Person1.Company
	and ProjectTask.PersonID = Person1.PersonID
where (ProjectTask.ProjectID like '24%')
order by Project.ProjectID, ProjectTask.PhaseID, ProjectTask.TaskID
2

You need to do a subquery with the ProjectTaskPerson table, and do an aggregate function to put them on a single row.

Include the fields that you will need to join it to project (probably company and projectID) group by those fields, and create this calculated field. (you can use whatever you want for the delimiter, I just shoved ~ in there, because that’s what on the screen.

string_agg( ProjectTaskPerson.PersonID,'~')

The bring that subquery into your top level and join it up.

FYI, that link you posted to the For_XML() is before String_Agg() was a thing. It was a hack to do what string_agg() now does, and actually I don’t even think will work anymore as Epicor made it so you can’t use that function.

3

Thank you Brandon! I’ll give that a try.

4

Thanks for your advice Brandon! You’re instructions were easy to follow and that solution worked like a charm! Happy Holidays to you!

1 Like