Combobox DefaultSelectedItems Multiselect From SQL Database Needs combobox refresh to work

(2) Share

Report

Posted on by tjestesjr

145

I have a combo box called cmbBrandItemsFinEdit. Its DefaultSelectedItems = colDefSelectedItems. Its Items is set to colBrandItemDD which I set on app start.

ClearCollect(
    colBrandItemDD,
    ShowColumns(
        'FCS.CS_BrandItemsDropDown',
        BrandItemNumber,
        ItemDesc
    )
);In my OnVisible I run:
Concurrent(
ClearCollect(
    colFinEdit,
    FCS_GetCS_Header.Run(varHeaderId)
),
ClearCollect(
    colSelectedItems,
    FCS_Get_CS_BrandItems.Run(varHeaderId)
));
ClearCollect(
    colDefSelectedItems,
    Filter(
        colBrandItemDD,
        BrandItemNumber in colSelectedItems.BrandItemNumber
    )
);FCS_Get_CS_BrandItems runs a simple stored procedure that returns the unique BrandItem numbers for a given header record.
 
My colDefSelectedItems looks like this:

As I said, I set my DefaultSelectedItems = colDefSelectedItems and it works but I actually have to add a button to the gallery and run

Reset(cmbBrandItemsFinEdit) in order for it to display the values.

How do I get the data to display as expected on page visible?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Sort by

WarrenBelz 154,924 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @tjestesjr,

Try this in the DefaultSelectedItems
ForAll( colSelectedItems As _Items, {BrandItemNumber: _Items.BrandItemNumber} )

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
tjestesjr 145 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

I tried your suggestion and it still does not populate the items as expected, also my button in the gallery to force a refresh with

Reset(cmbBrandItemsFinEdit) no longer works. Do you have any other ideas?

Was this reply helpful? Yes No
WarrenBelz 154,924 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @tjestesjr,

OK - a couple of questions I should have asked first as this is SQL.

What type of field is BrandNumber in your SQL Table

How are you writing these multiple values to the Table (what is the Update of your Data Card or your Patch code)

What does the data look like in your SQL Table (screenshot please)

What criteria does your procedure use to query the required items (where is the data coming from- I assume the items in the relevant record field ?)-this aligns with the question above.

When/how do you run those collections and do you update them after writing to a record ?

Was this reply helpful? Yes No

tjestesjr

145 on at

Like (0)

Report

Copy link

Link copied!

What type of field is BrandNumber in your SQL Table --> VARCHAR(15)

CREATE OR ALTER    PROCEDURE [FCS].[Get_CS_BrandItems] (@Header INT)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Results TABLE (
	[BrandItemNumber] VARCHAR(15));

	INSERT INTO @Results
	SELECT bi.BrandItemNumber  as [BrandItemNumber]    
    FROM [FCS].[CS_BrandItems] bi
	WHERE bi.[HeaderId] = @Header;

	IF @@ROWCOUNT > 0
	SELECT *
	FROM @results
	ELSE
	SELECT '' [BrandItemNumber]
		;
	
END

I am writing the multiple values to the database via JSON in the script [FCS].[Insert_CS_Header] below. It is part of a bigger script, but the relevant piece is the JSON.

Collect(
    colBrandItems,
    {Value: cmbBrandItems.SelectedItems}.Value
);
Set (
    BrandItemJSON,
    JSON(
        colBrandItems,
        JSONFormat.Compact
    )
);

ALTER
	

 PROCEDURE [FCS].[Insert_CS_Header] (
	@CS_Header_JSON AS NVARCHAR(max)
	,@BrandItemJSON AS NVARCHAR(max)
	,@PrimaryEmail AS VARCHAR(100) 
	,@DB AS VARCHAR(25)
	)
AS
SET NOCOUNT ON;

DECLARE @statement AS NVARCHAR(MAX)
	,@HeaderId INT
	,@insertid INT
	,@ProjectId VARCHAR(10)
	,@RoleId INT
	,@CountBrndItems INT;

BEGIN
	SELECT *
	INTO #CS_Header
	FROM OPENJSON(@CS_Header_JSON) WITH (
			HeaderId INT
			,RoleId INT
			,ProjectTitle VARCHAR(150)
			,ProjectTypeId INT
			,ProjectPhaseId INT
			,ImplmntChncGT50Ptct BIT
			,OwnerContactId INT
			,ProjectOriginId INT
			,ProjectDescription VARCHAR(2000)
			,InBudgetCOGSId INT
			,TotSavingsLowEnd VARCHAR(9)
			,TotSavingsHighEnd VARCHAR(9)
			,EstTotalSavings VARCHAR(9)
			,IsTBD BIT
			,EstStartDate [DATE]
			,EstCompletionDate [DATE]
			,SavingsPriceTypesId INT
			,HowIsSavingsMeasured VARCHAR(2000)
			,KED_MfgrItemCodes VARCHAR(2000)
			,AppUser VARCHAR(100)
			);	

	SELECT *
	INTO #BrandItems
	FROM OPENJSON(@BrandItemJSON) WITH (
			[BrandItemNumber] VARCHAR(15)
			,[ItemDesc] VARCHAR(75)
			);	

	SELECT @CountBrndItems = COUNT(*)
	FROM #BrandItems;	

	SELECT @HeaderId = HeaderId
	FROM OPENJSON(@CS_Header_JSON) WITH (HeaderId INT);

	SELECT @RoleId = RoleId
	FROM OPENJSON(@CS_Header_JSON) WITH (RoleId INT);
	
	BEGIN TRY
		BEGIN TRANSACTION MergeCS_Header

		IF NULLIF(@HeaderId, '') IS NULL
		BEGIN
			SET @statement = '
		 INSERT INTO [' + @DB + 
				'].[FCS].[CS_Header] (				
			[ProjectTitle]
			,[ProjectTypeId]
			,[ProjectPhaseId]
			,[ImplmntChncGT50Ptct]
			,[OwnerContactId]
			,[ProjectOriginId]
			,[ProjectDescription]
			,[InBudgetCOGSId]
			,[TotSavingsLowEnd]
			,[TotSavingsHighEnd]
			,[EstTotalSavings]
			,[IsTBD]
			,[EstStartDate]
			,[EstCompletionDate]
			,[SavingsPriceTypesId]
			,[HowIsSavingsMeasured]
			,[KED_MfgrItemCodes]
			,[Created]
			,[Creator]
			,[Modified]
			,[Modifier]
			)
			SELECT NULLIF(csh.ProjectTitle, '''')	as ProjectTitle	
				,TRY_CAST(NULLIF(csh.ProjectTypeId, '''') AS INT) as ProjectTypeId
				,TRY_CAST(NULLIF(csh.ProjectPhaseId, '''') AS INT) as ProjectPhaseId
				,csh.ImplmntChncGT50Ptct as ImplmntChncGT50Ptct
				,TRY_CAST(NULLIF(csh.OwnerContactId, '''') AS INT) as OwnerContactId
				,TRY_CAST(NULLIF(csh.ProjectOriginId, '''') AS INT)	as ProjectOriginId			
				,NULLIF(csh.ProjectDescription, '''') as ProjectDescription
				,TRY_CAST(NULLIF(csh.InBudgetCOGSId, '''') AS INT)	as InBudgetCOGSId
				,TRY_CAST(NULLIF(csh.TotSavingsLowEnd, '''') AS DECIMAL(9,0)) as TotSavingsLowEnd
				,TRY_CAST(NULLIF(csh.TotSavingsHighEnd, '''')AS DECIMAL(9,0)) as TotSavingsHighEnd
				,TRY_CAST(NULLIF(csh.EstTotalSavings, '''')AS DECIMAL(9,0)) as EstTotalSavings
				,csh.IsTBD	as 	IsTBD		
				,NULLIF(csh.EstStartDate, '''') as EstStartDate
				,NULLIF(csh.EstCompletionDate, '''') as EstCompletionDate
				,TRY_CAST(NULLIF(csh.SavingsPriceTypesId, '''') AS INT) as SavingsPriceTypesId
				,NULLIF(csh.HowIsSavingsMeasured, '''') as HowIsSavingsMeasured
				,NULLIF(csh.KED_MfgrItemCodes, '''') as KED_MfgrItemCodes
				,GETDATE() as Created
				,csh.AppUser as Creator
				,GETDATE() as Modified
				,csh.AppUser as Modifier				
			FROM #CS_Header csh; SELECT @insertid = SCOPE_IDENTITY()'

			EXECUTE sp_executesql @statement
				,N'@insertid INTEGER OUTPUT'
				,@insertid OUTPUT		

			IF @insertid > 0
			BEGIN
				SET @statement = 'SELECT @P = [Identifier] + CAST([Value] AS VARCHAR) FROM [' + @DB + '].[FCS].[CS_SmartNumber] WHERE RoleId = @RoleId'

				EXEC sp_executesql @Statement
					,N'@RoleId INT,@P VARCHAR(10) OUTPUT'
					,@RoleId = @RoleId
					,@P = @ProjectId OUTPUT
				
				SET @statement = 'UPDATE [' + @DB + '].[FCS].[CS_Header] SET [ProjectId] = @ProjectId WHERE [HeaderId] = @insertid AND [ProjectId] IS NULL;'

				EXEC sp_executesql @statement
					,N'@ProjectId VARCHAR(10), @insertid INT'
					,@ProjectId = @ProjectId
					,@insertid = @insertid

				SET @statement = 'UPDATE [' + @DB + '].[FCS].[CS_SmartNumber] SET [Value] = [Value] + 1 WHERE RoleId = @RoleId'

				EXEC sp_executesql @statement
					,N'@RoleId INT'
					,@RoleId = @RoleId

				IF @CountBrndItems > 0
				BEGIN
					SET @statement = 'INSERT INTO [' + @DB + '].[FCS].[CS_BrandItems]	(
			[HeaderId]
			,[BrandItemNumber]
			,[ItemDesc]
			)
			SELECT @insertid
			,[BrandItemNumber] 
			,[ItemDesc]
			FROM #BrandItems;'

					EXEC sp_executesql @statement
						,N'@insertid INT'
						,@insertid = @insertid
				END
			END
		END;

		COMMIT TRANSACTION MergeCS_Header
	END TRY

	BEGIN CATCH
		IF (@@TRANCOUNT > 0)
			ROLLBACK TRANSACTION MergeCS_Header;

		THROW;
	END CATCH
	SELECT @ProjectId as ProjectId, @insertid as HeaderId;
END

Was this reply helpful? Yes No

WarrenBelz 154,924 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @tjestesjr,

Firstly I need to note that I do not use SQL with Power Apps, so my thoughts are more on structure - I believe you need to re-collect colSelectedItems each time you write a value to the field, or this collection will not reflect the new-written value/s and as a consequence neither will the DefaultSelectedItems.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Suggested answer

tjestesjr 145 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz,

Thank you for all of your suggestions. I was finally able to get the combobox to perform as expected with the following setup.

I moved the combobox out of the gallery and to the bottom of the screen.

I set the Sreen OnVisible:

Set(

varIsLoading,

true

);

Concurrent(

ClearCollect(

colFinEdit,

FCS_GetCS_Header.Run(varHeaderId)

),

ClearCollect(

colSelectedItems,

FCS_Get_CS_BrandItems.Run(varHeaderId)

)

);

ClearCollect(

colDefSelectedItems,

Filter(

colBrandItemDD,

BrandItemNumber in colSelectedItems.BrandItemNumber

)

);

//Force the combobox to refresh with current colDefSelectedItems

Reset(cmbBrandItemsFinEdit);

Set(

varIsLoading,

false

);

I added a spinner box to the center of the screen that is only visible while the screen is loading. This gives a visual clue as the Reset(cmbBrandItemsFinEdit); is not always rapid.

Was this reply helpful? Yes No
WarrenBelz 154,924 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @tjestesjr,

Yes - as I posted - you needed to refresh the collection to get the current value. I assume this is now solved.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
WarrenBelz 154,924 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @tjestesjr,

A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No