Trouble with a tricky concatenate requirement with a collection

(0) Share

Report

Posted on by simms7400

829

Hello Folks -

I'm having trouble with a new business requirement and hoping you can help me solve?

I have a Form for my "Assets" and then another form containing "Investments". The Asset is the Parent and Investment is the child (think hierarchy) and there can be many Investments rolling up into an Asset.

My source is SharePoint Lists and I have included the "Asset" information in the "Investment" table for joining purposes.

During "On Visible" on my Asset Form, I'm trying to collect values from the Investment table in comma delimted form (if applicable, otherwise just return a single distinct record) and display that value on my Asset form.

Here is the table structure to better illustrate:

Asset Table:

AssetID	AssetAlias
4568	test
3456	test2

Investment Table:

Investment ID	AssetID	AssetAlias	PartnershipAlias
4568-2222	4568	test	ChickenParm
4568-2223	4568	test	ChickenParm
4568-2224	4568	test	ChickenParm
3456-1234	3456	test2	Veal Parm
3456-1234	3456	test2	Sausage Parm
3456-1234	3456	test2	Sausage Parm

What I'm trying to do is join on the AssetID and then return the "PartnershipAlias" value. The caveat is if there are duplicates, I want return just the single value, but if there are multiple instance, I need to return the distinct values in comma delimited form.

So on my Asset Form, the field I am trying to use will display Chicken Parm for AssetID 4568 while Asset ID 3456 will display Veal Parm, Sausage Parm.

Is this possible? Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

CU-18081211-6 9,270 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @simms7400 ,

Well, on On Visible property of the screen set an context variable as:

UpdateContext( {associatedInvestment: Concat( Distinct( Filter(Investments,AssetID=selectedAssetID), PartnershipAlias ), Result&",") } )

where selectedAssetId is the selected AssetID from Asset Table probably in one of your previous screen (or the record AssetId value of form's item property).

Use this context variable in the control you want to display the info.

Hope it helps !

Was this reply helpful? Yes No
simms7400 829 on at

Like (0)

Report
Copy link

Link copied!

@gabibalaban

Thank you!! That basically works. But is there a way to only display the "PartnershipAlias" value, I don't need the AssetID value displayed.

Thank you!

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report
Copy link

Link copied!

@simms7400 ,

Just hide (set the Visible property to false) or delete the control with AssetID from the form, the context variable gives you only the "PartnershipAlias" value.

May a screen shot will help me to figure it out what you need ...

Was this reply helpful? Yes No