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
concatenate data for grid display - use a db stored proc
concatenate data for grid display - use a db stored proc
- Attachments
-
- DaysAvailable.PNG (4.28 KiB) Viewed 8720 times
-
- updateMemberProfile.PNG (45.9 KiB) Viewed 8720 times