Unanswered

Concatenate on lookup column values to populate a text column used for sorting a gallery

(0) Share

Report

Posted on by Alichson

Objective: sort a SharePoint List of tasks in a gallery by sort ID that is a combination of project ID and staff ID.

Challenge: project ID and staff ID are lookup columns taken from a list of projects and a list of staff; SharePoint Lists cannot create a calculated column using lookup columns; Power Apps cannot run SortByColumns on calculated or lookup columns; therefore, the sort ID column must be a text column.

Approach: populate the sort ID column using a non-visible DataCard in the Power App edit screen; set the Default and Update fields of the DataCard to concatenate the project ID DataCard and staff ID DataCard, as follows. (Note: the underlined object names have been adjusted to show context.)

Concatenate(First(DataCardValueStaffID.SelectedItems).Value,Text("-"),First(DataCardValueProjectID.SelectedItems).Value)

Issue: the sort ID column is sometimes not correctly populated when an item is created in the Power App in Teams; the result is just the joining hyphen "-"; however, it is populated if the item is updated later.

Measures: changed the DelayOutput field of the DataCardValue to false, but no improvement.

Alternatives: possible to create a Power Automate flow to concatenate columns, but I would prefer to only have the Power App manipulating the list data.

Questions: Any idea why the Concatenate formula sometimes does not populate the column?

Also, can anyone suggest an alternative approach to either creating a concatenated text column in SharePoint Lists or sorting a gallery using lookup column data?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report
Hi @Alichson ,

You need to use AddColumns as below - I have only used the With() statement as I try to keep calculations outside of the main code body.

With( { wSort: First( DataCardValueStaffID.SelectedItems ).Value & "-" & First( DataCardValueProjectID.SelectedItems ).Value }, Sort( AddColumns( Filter( SPList, . . .YourFilterHere . . . ), "SortField", wSort ), SortField ) )

Please click Accept as solution 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 giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Alichson 22 on at

Like (0)

Report
Hi @WarrenBelz ,
I really appreciate your quick response. I gather that you are suggesting an alternative to sorting a gallery using lookup column data. I have seen you recommend a similar coding approach on another thread, but I was unsuccessful in applying it. Can you check my understanding of it with the ##comments## below?
With( ##Does this allow you do define the wSort variable before running the Sort command?## { wSort: ##I am not sure, but this looks like a new temporary variable## First( DataCardValueStaffID.SelectedItems ).Value & "-" & First( DataCardValueProjectID.SelectedItems ).Value }, Sort( AddColumns( ##Is this adding the data from wSort to the SortField column?## Filter( SPList, . . .YourFilterHere . . . ), "SortField", ##Target column: can this be an existing text column?## wSort ##This is calling the variable for writing to the target column, right?## ), SortField ##This is sorting the list by that newly added column, right?## ) )

P.S. Your blog is fantastic! I wish I had read it before starting out with this company-wide task management app. I think that maybe using Lookup fields in my SharePoint list was a mistake. Do you think I should change them to Text fields and do the ID lookup in the Power App? Also, I need to revisit my data filters approach. 😅

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report

@Alichson ,

I am not totally sure on the direction of your question, but based on what you posted, to sort a gallery , the sort field needs to be in the Items of the gallery and AddColumns is the best way of doing that. As I mentioned, the With() at the top simply sets the values pre-calculated rather than doing it in the body of the Sort/Filter.

As for Lookup columns, unless you are using them directly in SharePoint grid view edit, they are rarely useful and actually a hindrance (I do not use them)

Was this reply helpful? Yes No
Alichson 22 on at

Like (0)

Report
@WarrenBelz , thank you for your response and sorry for my unclear question. I was trying to check my understanding of your code before I applied it.
I attempted to apply your code, but I needed to change DataCardValueStaffID and DataCardValueProjectID because they are DataCards in the EditForm that is related to the gallery. I tried changing them to the Lookup columns Staff:StaffID and Project:ProjectNo in the TaskList, but the First functions are throwing errors because they have not been instructed to reference the TaskList. I do not know how to call these values correctly. Is it possible?

With( { wSort: First( 'Staff:StaffID'.SelectedItems ).Value & "-" & First( 'Project:ProjectNo'.SelectedItems ).Value }, ...

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report

@Alichson

That is why I had the comment on multiple selections. Are these single select ? I so you just need .Selected.Value and without the First() reference

Was this reply helpful? Yes No
Alichson 22 on at

Like (0)

Report
@WarrenBelz , thank you again for your guidance. Yes, these are single-select Lookup columns in the List.
I changed the code to .Selected.Value, got the error "Name isn't valid. 'Staff:StaffID' isn't recognized" and found that the colon needed to be represented as _x003a_. I changed to the code shown below, but I am getting "Invalid use of "."" on .Selected.Value and a message saying that there is an error in "Staff_x003a_StaffID" and that data type: text.

With( { wSort: "Staff_x003a_StaffID".Selected.Value & "-" & "Project_x003a_ProjectNo".Selected.Value }, ...

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (1)

Report
@Alichson ,

You do not need the quotes around the control name - I assume those are dropdown controls.

With( { wSort: Staff_x003a_StaffID.Selected.Value & "-" & Project_x003a_ProjectNo.Selected.Value },

Was this reply helpful? Yes No
Alichson 22 on at

Like (0)

Report

@WarrenBelz Thank you for your help. To be honest, I could not get this to work with the lookup columns in the SP List. In the end, I created text columns to replace the lookup columns and implemented the lookup function in the PowerApp rather than the SP List. This solved the population issue with the sort ID column.

Next, I will reference your solution using the With() function to try to eliminate my delegation warnings. This post also seems relevant.
https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/With-Statement-to-include-non-Delegable-filters/ba-p/660997

Was this reply helpful? Yes No