Concatenate look up fields

[tested1]

Hello, 

I have a what seems like a complex problem I will try to explain. Please if you dont get any of it let me know. I will post a quick summary first here and then explain it. I have put some pictures below. Please let me know if more is needed

Quick Summary:

1. I have two SharePoint lists. One a powerapps form for users to fill in. The second contains a master list which contains all the choices and the first list uses this as a look up column
2. I want to combine a field from the first list with one from the second look up column.

Scenario.

1. I have a powerapps Form on a SharePoint List for users to fill in
2. One column is say 'Country' and options could be Australia (AUS)...India (IND) etc. To make the Austriala (Aus) choice I created a second list, made columns country, country code and then a concatenate column using a calculated column to combine the two columns (country+code) (See pic 1). I then used a look up column on my original form that gets this 'Concatenate field) for the user
3. I wanted to keep the country code separate to make up the Name column
4. Another column is 'Name' on the original list/form for the user to fill in
5. What I want to do is combine the Name field with the Country field to give say Name_Country. BUT I only want to combine the 'Country Code' column in the second list with the 'Name' column in the original list. So it could be 'Team_AUS' I dont want to combine the Name and the country column because it would be Name_Austriala (AUS)...I only want to combine the country code with the name in a new column called.

Pic 1 shows the second list where I have created the concatenate column which is used as a look up column in my powerapps form (list 1)

Pic 2 shows the 'Country' Column in my Powerapps form (list 1). Here I have chosen to look up the concatenate column but also bring the 'code' column which is hidden from the user (views)

Pic 3 shows my Powerapp form (list 1) but filtered to just show the columns I want to combine. Here you can see I need to combine the 'Name' column with the 'Country:Code' Column 

Please if there is any help or tips I would really appreciate it. Sorry this is very difficult to explain. Can it be done in Powerapps to have this autopopulated 'Name' Column which has the 2 columns/look up field combined?

I am unsure if this should be in the Microsoft Flow community or SharePoint..but saw similar posts for Powerapps community!

123

[RandyHayes]

@tested1 

To be clear...are you looking for this to be done automatically through SharePoint, or are you looking to have this appear in your PowerApp?

I personally always consider the business logic of what I am doing and the importance of where it lives.  If you put your logic into a PowerApp, then any entry to your SharePoint list will then be through the PowerApp - not an issue, just a point (although rules of design say that business logic should not exist in the front-end).

If you make the logic happen in Flow, then your SharePoint list can be manipulated through any source and your logic will apply (not exactly putting the logic on the back-end, but at least putting a governor in place to apply your rule).

What I am leading to in short is that point in time when you look at a column that doesn't have what you want in it and trace it back to - "oh, that was not entered in PowerApps" - Which, BTW, would be any existing data unless you put in logic in a PowerApp (or Flow for that matter) to "apply" your rule to all other records that already exist.

So, what I'm driving at is - are you looking to 1) "sweep" your list and apply this rule, 2) apply this rule for every item created regardless of creation source, or 3) apply the rule for any new entries made through a custom App/Form?

If so, the answers are 1) Flow or PowerApp  2) Flow  3) PowerApp

Now these are just suggestions.  The purists would point to obvious other combinations of these, but these are at least the most simplistic starting points to consider.

Hope this is helpful.

[tested1]

Hello @RandyHayes . Thank you for the reply!!

What do you suggest would be the best way or would be the most pragmatic method?

What I was planning to do is hide the 'Name' form or make it Read only in the PowerApps form and have it auto populate once the user has filled in the rest of the form..it could happen once they have clicked submit on the form or automatically while they pick options..whatever is best or easier to do?

A user would click new item in the SharePoint list and the powerapp form would appear for them to fill in.

P.S there are no existing items on the form, as the form is still in development stage. So everything will be new entries. I think it would be 3? Im not too sure on what you mean by 1 and 2 but I just wanted the concatenate column to appear on the sharepoint list and/or the PowerApp form

Thanks. Please let me know if you want more details 🙂

[RandyHayes]

@tested1 

So if you're just starting this fresh and are going to be the Interface to your data, then a PowerApp is a fine thing to have.  

What I was planning to do is hide the 'Name' form or make it Read only in the PowerApps form and have it auto populate once the user has filled in the rest of the form..it could happen once they have clicked submit on the form or automatically while they pick options..whatever is best or easier to do?

---

This is completely feasible.  For the most part, since you are just filling in the column with values, it only makes a difference at the point of SubmitForm.  So, in this case, you will set the visibility of the datacard for the Name column to false (or I recommend setting it to the value of a toggle control while you're testing - so you can hide and unhide as you need).  Then you will focus on the Update property of the Name datacard and make sure it is providing the values you want to be submitted back to the list.

P.S there are no existing items on the form, as the form is still in development stage. So everything will be new entries. I think it would be 3? Im not too sure on what you mean by 1 and 2 but I just wanted the concatenate column to appear on the sharepoint list and/or the PowerApp form

Yes, sounds like you fall into situation #3 - Go PowerApps!!  

I think you're in the right starting point.  Get yourself up to speed on the EditForm.  Focus on DataCards and the primary properties - DataField, Default, Update.  And then post back here with any questions or problems you run into.

[tested1]

Hi @RandyHayes 

Thank you for the reply!

The main problem is how to create the combined(from multiple other columns including a look up column) column/datafield which auto populates..Question:

1. I am quite new to PowerApps..Do you have any steps or guides on how I would go about setting up this 'combined/concatenated column' from look up columns/datasources in the SharePoint List? I have no idea where to start to try to create a combined auto-populated column from look up column :S.

I really wanted to get this column created asap but its quite difficult to explain what I wanted.

Do you understand what I am trying to achieve? Just checking if its clear/viable what I hoped to create

Thank you!

[RandyHayes]

@tested1 

So, I can't give you a specific formula to slam-dunk this for you because I don't know your columns, lookups, data, etc.

But, we can start to get close and you can take it from there hopefully, or continue to get some guidance from the posts here.

In general, let's imagine that you have a New EditForm.  Let's say we hide the Name DataCard in the form.

Also in the form you have a DropDown selection of the Country.

You can easily do a lookup on the Country list:

   Lookup(CountryList, Title=thisDropDown.Value).Code

Then, again in general, you would simply have (for your Update property of the Name DataCard)

   yourTeamNameDataValue & Lookup(CountryList, Title=thisDropDown.Value).Code

This would give you, as in your example, Test_01_BRA if, for example, the Brazil country is selected in the DropDown.

So...your first step is to brush up on all of the items in this post in Red.

Head on over to the Docs and look into these.

Then, once you get the idea down.  Figure out if you are doing an App (standalone with SharePoint Connection) or a Customized form (within SharePoint interface).

Then let's see what questions roll out from that.

I hope this has given some fuel for thought and a boost to the next step.

[tested1]

Thank you @RandyHayes  I will be getting my head around this.. In the mean time just to clarify the steps you mentioned

1. I have a powerapps form connected to a sharepoint list already created with many columns. 
2. Column one is name - user enters a name e.g. Test in the form
3. Column two is country - User selects a country. Option could be 'Brazil (BRA)'
4. Column three is 'Combined' - This would be the steps you said and would hopefully autopopulate to give e.g. 'BRA_Test' by using the lookup in the other list called Country which contains columns, Country name, Country code, Combined (so Brazil, BRA, and Brazil (BRA). This combined column should be read only or hidden from the user

Thats the steps I want to take and will use your reply to drive 🙂 P.S the combined column, 'BRA_Test' I have a few more lookups I wanted to add to it so say BRA_XXX_ZZZ_Test, just more look up columns. But I will start with One (Country code) Since rest will be same steps. This is no problem right/just follow the same steps once I have it working for one column?

P.s. do you have a link for the docs hyperlink you mentioned? it says 404 error when I click it

Thank you! 🙂

[RandyHayes]

@tested1 

So I had to re-read the post again...it seems that, what I was thinking was, you wanted to hide the Name field from them.  But, perhaps this is all much simpler - you want to just combine the Name and the Country code into the concatenate column...is this correct?

If so, then yes, it is quite straight-forward.

You would only need to do the following on the Update property of the DataCard for the concatenate column:

 ThisItem.Name & "_" & Lookup(CountryList, Title=dropDownBox.Selected.Value).Code

You could hide the concatenate column from the EditForm if you need.

I hope my assumption after closer inspection of your posting is accurate and this is more what you are looking for.

Also...I updated the post with the link to the Docs.  Here it is again for reference. That is the functions list.  To read up more on the Controls in PowerApps, take a look over these documents.

[tested1]

hi @RandyHayes ,

That sounds correct! I tried it but received some errors.. The key issue is I need to do the combined column from 2 lists. Is that possible? So one field is in my original list and the second field which is the code is in a separate second list..

Would I click on my form and add the second list as a data source? Wont this mess up the existing original form data source? But I was able to select the second list from the second data source ok..so maybe its fine

Also the field (Code column)  I want to combine is in a separate column in the second list (See pic) I need to combine this  with my 'Title' column in list 1.. The error seems to be in the dropdownbox etc part. Is there a way to select the 'Code' Column in the second list? Thank you

[RandyHayes]

@tested1 

So again re-reading your question from the beginning, let me see if this is clear:

List 2 (Teams Request Form) is the list of Country, Code and concatenated columns.

List 1 (I don't know the name of this list) is the list you are working on that has a Lookup column to List 2 and creates two columns in your List 1 - concatenate and Code

You want:

1. To alter the Title column in List 1 to be CountryCode, "_", TeamName (found in the Name column)
2. The Title column will be hidden from users
3. Users will select a Country (based on values in List 2) from a drop down control.
4. The Title column must update with "CountryCodeSelected_TeamName"

So again, your steps will be this:

Step 1:  Add the List 2 as a DataSource in your App. (again, from what I see, you call this "Teams Request Form")

Step 2:  Set the Items of your DropDown control to this formula:

    dataSourceAddedInStep1

    But, to get a little "nicer" in your list, you might consider this instead:

    SortyByColumns(ShowColumns(dataSourceAddedInStep1), "Country"), "Country")  This will give you a sorted list of just countries.

Step 3: Hide your Title DataCard - Set Visible property to false

Step 4: Change your Update property on the Title DataCard to the following formula:

   Lookup(dataSourceAddedInStep1, Country=yourDropDownControl.Selected.Value).Code & "_" & ThisItem.Name

That will do what you are looking for (if I have assumed your situation correctly)

Hope this helps.