cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hnguy71
Level 8

Concatenate with Conditional Statement

Good morning!

 

I want to create a collection in which combines 4 columns: Geomarket, Sub Geomarket, Product Line, Sub Product Line using concatenate function.

 

Powerapps is telling me that my IF statement is wrong or is returning the wrong values. What am I doing wrong?

 

ClearCollect(_UniqueCombination,
Concatenate(
    'Team Registration'.Geomarket, "|",
    If(IsBlank('Team Registration'.'Sub Geomarket'), "NON", 'Team Registration'.'Sub Geomarket'), "|",
    'Team Registration'.'Product Line', "|",
    If(IsBlank('Team Registration'.'Sub Product Line'), "NON", 'Team Registration'.'Sub Product Line')))

collection_wrong_values.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Concatenate with Conditional Statement

@hnguy71 

So, this is going to get back to the datasource and how you are gathering the information to start with, but, during that process (or later), you can add a "Concatenated" column.

Something like this would work for you:

ClearCollect(myTeamRegistrationCollection,
AddColumns('Team Registration',
"ConcatenatedColumn", Geomarket & "|" & If(IsBlank('Sub Geomarket'), "NON", 'Sub Geomarket') & "|" & 'Product Line' & "|" & If(IsBlank('Sub Product Line'), "NON", 'Sub Product Line')
)
)

This will give you a full collection with all the Concatenated Columns

 

@timl I think you might be confusing Concat with Concatenate in this case...for there you certainly need a table 😉

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Concatenate with Conditional Statement

@hnguy71 

What kind of columns are Sub Geomarket and Sub product line?

If they are choices or lookups, you will need to put a .Value at the end of each ex. 'Sub Geomarket'.Value

 

I hope that helps you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Concatenate with Conditional Statement

They're all text fields. Even so, I'll try that.

 

EDIT: Doesn't work still.

Super User
Super User

Re: Concatenate with Conditional Statement

@hnguy71 

Ah...actually, I didn't even notice on the first reply that this is all in a ClearCollect statement....

Yep, this is not going to work.

Can you tell me what you are trying to get to...a collection of one record with the concatenated value, a collection of a source of records with concatenated values, or something else?

 

If you're trying to set a variable - you could do a Set(_UniqueCombination, Concatenate(....

That would give you a single string of the concatenated values.

 

But, you are trying to assign this to a collection, so I need to know more about that collection and what you expect from it.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
hnguy71
Level 8

Re: Concatenate with Conditional Statement

Hi @RandyHayes,

 

Hopefully this makes sense. The idea is to build a record for each row in a SharePoint list. The collection will then be used to cross check to see if the current submitted PowerApps form contains the same values as the row record. If it exists, notify user that there's already an entry with the same key combination. If it does not exist, proceed to creating a new list item.

 

In theory, there should be no one exact combination of Geomarket, Sub Geomarket, and Product Line. If there is a better alternative / idea I'm definitely open. 

 

concat_idea.PNG

 

Here's what I am looking for within the conditional concat: If Sub Geomarket is empty then give me "NON" (or some other value) else give me what's inside Sub Geomarket. Then I need to find a way to compare the current selected items in the form to see if they match or not match before submitting.

Super User
Super User

Re: Concatenate with Conditional Statement

Hi @hnguy71 

 

I think what you're looking for would look more like this:

 

ClearCollect(_UniqueCombination,
Concatenate(
    'Team Registration', 
Geomarket, "|", If(IsBlank('Sub Geomarket'), "NON", 'Sub Geomarket'), "|", 'Product Line', "|", If(IsBlank('Sub Product Line'), "NON", ''Sub Product Line')))

The key thing is that the first argument to the Concatenate should be a table, rather than a field.

hnguy71
Level 8

Re: Concatenate with Conditional Statement

I'm still seeing errors =(

 

concat_invalid.png

Highlighted
Super User
Super User

Re: Concatenate with Conditional Statement

@hnguy71 

So, this is going to get back to the datasource and how you are gathering the information to start with, but, during that process (or later), you can add a "Concatenated" column.

Something like this would work for you:

ClearCollect(myTeamRegistrationCollection,
AddColumns('Team Registration',
"ConcatenatedColumn", Geomarket & "|" & If(IsBlank('Sub Geomarket'), "NON", 'Sub Geomarket') & "|" & 'Product Line' & "|" & If(IsBlank('Sub Product Line'), "NON", 'Sub Product Line')
)
)

This will give you a full collection with all the Concatenated Columns

 

@timl I think you might be confusing Concat with Concatenate in this case...for there you certainly need a table 😉

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Super User
Super User

Re: Concatenate with Conditional Statement

Thanks @RandyHayes! Yes, I was thinking of Concat.

hnguy71
Level 8

Re: Concatenate with Conditional Statement

@RandyHayes,

 

This works perfectly! I'm returning all the concatenated records that I need. Is there an easy way for me to get selectedvalues from each field and compare it with any/all of the records?

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (6,032)