concatenate data for grid display - use a db stored proc

Contains tips for configurators working with Aware IM
Post Reply
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

concatenate data for grid display - use a db stored proc

Post by ddumas »

This technique allows you to have multiple independent search selections using a (search) form attached to a query, and also display the concatenated result in a single string attribute. This is particularly useful for query grids (screenshot attached) where you want to show a concatenated result set from a relatively small set of flag attributes. To have multiple independent search selections for a search form, it's also important to implement these as yes/no flags inside the BO, and NOT using "multiple allowed" type columns into a separate child BO. As far as I an tell, query Search forms do not render "Multiple allowed" type attributes in combo boxes.

I have a string attribute DaysConcat, in my Member BO, set to calculated. When I save the Member form, screenshot attached, the process UpdateMemberProfile is called.

In that process, I have a single rule: EXEC_SP 'usp_UpdateMemberProfile' WITH '@MemberID'=Member.ID

That calls the stored procedure below, in his case, a SQL Server stored procedure. Then I have a query grid using a search form, which displays that attribute as a column in the grid.

Enjoy!

Dave


create PROCEDURE [dbo].[usp_UpdateMemberProfile]
@MemberID int -- null

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @now datetime = getdate()

update Member
SET
[DaysConcat] =
case when [IsDayAvail_Monday] = 'true' then 'Mon' else '' end +
case when [IsDayAvail_Tuesday] = 'true' then ' Tue' else '' end +
case when [IsDayAvail_Wednesday] = 'true' then ' Wed' else '' end +
case when [IsDayAvail_Thursday] = 'true' then ' Thu' else '' end +
case when [IsDayAvail_Friday] = 'true' then ' Fri' else '' end +
case when [IsDayAvail_Saturday] = 'true' then ' Sat' else '' end +
case when [IsDayAvail_Sunday] = 'true' then ' Sun' else '' end
WHERE ID = @MemberID


END
GO
Attachments
DaysAvailable.PNG
DaysAvailable.PNG (4.28 KiB) Viewed 8521 times
updateMemberProfile.PNG
updateMemberProfile.PNG (45.9 KiB) Viewed 8521 times
Post Reply