Concatenated Column in Edit form

[Anonymous]

Hi All,

I was creating a Power Apps whose data source is SharePoint list. There are 2 columns in SharePoint list say for E.g. ID and Title.

ID will consist values like 1,2,3,.. and Title will consist values like Title1, Title2, Title3,....

I needed a concatenated column so i created that in SharePoint using calculated column and now that column is having values like "1-Title1","2-Title2","3-Title3",...(I am just providing3 rows for eg. purpose).

I am creating a form in Power Apps, In first page we will create a new ticket and in second page we can edit those tickets (So mainly i am working inside create form, edit form and submit form)

Now my requirement is -

In first Page we will keep 2 columns alone - ID and Title. (First Page is for creating a new ticket)

If we go in Edit page i.e., second page (Second page is for editing the ticket and submitting it) and if i perform Edit operation in ID 1 then it should show all the 3 columns - ID , Title and Concatenated column. -- Concatenated Column should be present in dropdown(i bought concatenated column in dropdown manner). and i need to select "3-Title3" in dropdown against ID 1 and then need to submit that ticket.

The End result ID 1 after we completed our edit operation and press the submit button then it should display as --> 1 in ID Column , Title1 in Title column, "3-Title3" in Concatenated Column.(Currently it is showing "1-Title1" for concatenated column)

As of now i can able to see all the 3 values in dropdown but when i select "3-Title3" and submit then it is not getting submit properly and not reflecting in gallery. When i go again and Edit ID 1 then it is showing again "1-Title1" as default (Not "3-Title3")

Actually Ticket 3 is parent ticket of Ticket 1 that is why i am trying to select ID 3 in concatenated column if we edit ID 1.

Please help me on this. Concatenated column(3rd column) should behave like dropdown functionality where we can select whatever ID we want to.

Regards,

Prakash

[RandyHayes]

@Anonymous 

Calculated columns are special columns that evaluate their value in SharePoint - not in PowerApps.  So, I would recommend that you calculate the value in your App instead and assume the calculation on the SharePoint side.  Otherwise, you will need to refresh your datasource after every edit in order for PowerApps to "see" the new value that SharePoint calculated - that is a performance expense.

First question though that I would ask...do you need the calculated column in your list?  If your app is going to become the primary method of data entry into the list, I would just put in a regular text column in the list and then concat the value you want in the app.  That way it will always show and be up to date in your app without a refresh.

I hope this is helpful for you.

[Anonymous]

Hi @RandyHayes ,

Yes, first i tried in Power Apps only but unfortunately couldn't able to achieve it. So i though to try in SharePoint. I would take your suggestion and will try in Power Apps itself because that is the correct way.

Please help me on Concatenating 2 fields in edit form.

My Data Source Name is "New Data Request_2".

I just put a text column in list and went ahead by concatenating the 2 columns in apps.

I have provided the below formula in items of that dropdown(Concatenated Column):

AddColumns('New Data Request_2',"Concatenated",ID&"-"&Title)

The issue is in edit page i can able to select the values from dropdown but when i submit that after changing the values it is not getting reflected into the Gallery. Still i am seeing the old values only.

I am missing some steps i feel.

Regards,

Prakash

[RandyHayes]

@Anonymous 

So, you need to store the "calculated" value in your EditForm.  AddColumns is not what you want to do in PowerApps in this case because it AddColumns will only be relevant to the Dropdown (that is where your formula is).

YOUR OPTIONS:

1) You can change your Gallery Items property to 

         AddColumns('New Data Request_2',"Concatenated", ID & "-" & Title)

However - this will break any references you have to your Gallery through the Selected property.  Like, if you have an Item property on an EditForm set to : yourGallery.Selected then the above formula would break that (it is fixable, but be aware)

2) You can store the concatenated value in your list

So - Let's say you have the list - 'New Data Request_2'  

In that list you have 3 columns ID (which is always there), Title (a text column) and IDTitle (a text column - you can call this what you want, but for the purpose of this example, I have called it IDTitle)

In PowerApps, add an EditForm (let's call it Form1) and connect it to  'New Data Request_2' .  You will then have 2 columns - the Title and the IDTitle showing.  You can remove the IDTitle from the field list as you will not be working with this directly.

Most likely you will have some sort of button or something to submit the form information.  So, in that control, place the following Formula:  SubmitForm(Form1)

Now, go to the OnSuccess action of Form1 and set the following formula:

UpdateIf('New Data Request_2', ID=Form1.LastSubmit.ID, {IDTitle: Text(Form1.LastSubmit.ID) & "-" Form1.LastSubmit.Title})

That is all you will need for the submitting.  You will now have an active IDTitle column to use and it will update as needed.

See if either of these work for you.

[Anonymous]

Hi @RandyHayes ,

I almost got output but unfortunately got stuck in one point.

Before reaching out to you i tried #1 but i got many errors because of the dependency which you explained for #1. Since i am new to power apps i am not sure how to fix that.

So i followed your steps in #2.

IDTitle(3rd column) - do i need to give this as 'text' column or 'Choice' in SharePoint. I would love to give this as 'Choice' since it is making my job much more easier(If i give that as choice column than i can easily make that as Dropdown in edit form). But when i keep that as choice then i am getting the error in below formula.

UpdateIf('New Data Request_2', ID=Form1.LastSubmit.ID, {IDTitle: Text(Form1.LastSubmit.ID) & "-" Form1.LastSubmit.Title})

Error - In Compatible type, The 'IDTitle' column in the data source you are updating expects a 'Record' type and you are using a 'Text' type.

Can we change above formula for a 'Choice' Type instead of 'Text' Type?

Secondly i tried the 'IDTitle' with Text type, here in edit form i am doing several steps so that i can replace the text box to Dropdown but after replacing it to dropdown - for eg. when i select "3-Title3" and submit it but as a end result it is not getting updated properly.

I believe i am very close to getting output by having "IDTitle" Column as "Choice" in Sharepoint. So please let me how to change the above formula from text type to record type?

Thanks in Advance

Prakash

[kmistry]

I have similar issue where I can not retrieve concatenated value in dropdown Edit form from the backend sharepoint list. My dropdown is the custom lookup field in power app. 

In my dropdown the Items properties has following code

Sort(Concatenate(Impact.'score'," - ",Impact.Description),"score",SortOrder.Ascending) in the my dropdown 

In master sharepoint list called Register, 'Score' value stored from the 'Impact' list.

How can I retrieve concatenate value in edit form , as a selected value from the master SharePoint list -Register?