cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
txkimmers
Frequent Visitor

Output from Join not available in dynamic content

Hello, I am an upper-noob, lower intermediate PA user and I hope someone can help with this.  I am building a flow to combine columns from two SharePoint lists that have a single column in common to connect them as in a one-to-many relationship and then export the combined list data into a worksheet.  I actually have this working just fine. What is tripping me up is a column in one of the lists that is a multi-selection column.  I have used Apply-to-each to extract the column values from the list with Select and then connected them with a comma via Join and that is also fine.

My problem is that the output of the Join operation is not appearing in dynamic content so that I can place it in the row definition of the "Add a Row" connector.  I tried putting the Join output into a Compose action, but that output is not available either.  What am I missing in the flow to ensure that the Join output is available in dynamic content, or do I need to write an expression to grab this data, and if so, can I get help with that expression (I am definitely a noob with expression writing).

 

Thanks much in advance~

 

1 ACCEPTED SOLUTION

Accepted Solutions
txkimmers
Frequent Visitor

I managed to solve this problem by taking a different tack.  After creating the file and the getting the first SP list values, I set an Apply to each to loop through the SP list values, then looped select/join the values for the multi-choice column and then added a row to the spreadsheet.  So basically instead of trying to get all the values for the export lined up and ready to use after getting the matching sub-group from list 2, I am going to do the export in two stages.  Stage 1 will create the rows, add the concatenated string values into the correct column, then Stage 2 will be updating the rows with matching values from the other SP list.

Now I am working through updating the rows of that worksheet with the matching values from the second list.  Having some trouble since that is also new to me, but that is best saved for another ticket.

View solution in original post

9 REPLIES 9

I'm not entirely sure what you are doing since there seems to be a lot going on and multiple apply to each. 

 

But if you can't find something in the Dynamic Content, then it is because PA is trying to be "helpful" and provide options that it thinks are necessary. For example sometimes if you click into a datetime field, the dynamic content won't show any text/string options (sometimes). 

But the dynamic content still exists. 

 

You should put a Compose step above the Add Row or whatever you are populating. Put the Join dynamic content into the compose. 

 

Hover your mouse over the Join dynamic content in the compose and it will show the expression to use. 

Or you can highlight and copy the dynamic content then paste it into a separate notepad or word document.

 

It will look something like "@{item()['Column']} or whatever. Just remove the initial at@ symbol and curly brackets.

 

Go into the add row field you want to put the dynamic content and go to "Expression" fx box instead of dynamic content. Paste in that text as an expression.

 

It will use it just like if it was dynamic content.

Thank you for the quick response. Based on your notes, I was able to write the expression but when I put it in the "add a row" and tested it didn't return any data.  I tried putting the Join results into a string variable, but that unfortunately did what I expected it to do and just returned the first value in the Join array and repeated it for each row.  I can't use an array variable because Excel wants a string. 

I am starting to think I need a different approach to turning the SP multi-choice values into a string, so I am going to pursue that.  The underlying problem is that I do not completely understand the right way to shape the values from the SP column, so I will do some more research. 

--txk

Looking at the first screenshot you shared it looks like you are selecting an array property. This would create an array inside an array.

 

Look at the output of the select action. I don’t think you need the select action but I would need sample data to analyze it.

txkimmers
Frequent Visitor

Hello Matthy79:

Yes, it is an array within an array, and I am unsure how to make it available to loop through another SP list, create a mapped content item and have it available for the final Apply-to-Each.

I parsed the JSON of the first list, see below.  The array that I need to retrieve for each SP list record is SetOptions.items.value , that is the multi-select column from the list.  

After parsing the JSON from the list, I am uncertains how to write an expression that will retrieve the SetOption item values for each record that I am matching against in the second list.  I do have the parsed content items available as dynamic content in the Apply-to-each that will write each row of the Excel worksheet, but again I am not sure how to make use of them correctly.  I hope this information helps?  I am happy to post more screenshots of the flow steps too. 

Also: in this scenario, the term "Function" refers to sub-events of a meeting, not a programming term, so General Session is a function, Opening Reception is a function, and the "sets" refers to the actual set up needs for the event. 

 

 

 

 

{
  "type": "object",
  "properties": {
    "body": {
      "type": "object",
      "properties": {
        "value": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "@@odata.etag": {
                "type": "string"
              },
              "ItemInternalId": {
                "type": "string"
              },
              "ID": {
                "type": "integer"
              },
              "FunctionID": {
                "type": "string"
              },
              "Quantity": {
                "type": "string"
              },
              "Modified": {
                "type": "string"
              },
              "Editor": {
                "type": "object",
                "properties": {
                  "@@odata.type": {
                    "type": "string"
                  },
                  "Claims": {
                    "type": "string"
                  },
                  "DisplayName": {
                    "type": "string"
                  },
                  "Email": {
                    "type": "string"
                  },
                  "Picture": {
                    "type": "string"
                  },
                  "Department": {
                    "type": "string"
                  },
                  "JobTitle": {
                    "type": "string"
                  }
                }
              },
              "Editor#Claims": {
                "type": "string"
              },
              "Created": {
                "type": "string"
              },
              "Author": {
                "type": "object",
                "properties": {
                  "@@odata.type": {
                    "type": "string"
                  },
                  "Claims": {
                    "type": "string"
                  },
                  "DisplayName": {
                    "type": "string"
                  },
                  "Email": {
                    "type": "string"
                  },
                  "Picture": {
                    "type": "string"
                  },
                  "Department": {
                    "type": "string"
                  },
                  "JobTitle": {
                    "type": "string"
                  }
                }
              },
              "Author#Claims": {
                "type": "string"
              },
              "SetOptions": {
                "type": "array",
                "items": {
                  "type": "object",
                  "properties": {
                    "@@odata.type": {
                      "type": "string"
                    },
                    "Id": {
                      "type": "integer"
                    },
                    "Value": {
                      "type": "string"
                    }
                  },
                  "required": [
                    "@@odata.type",
                    "Id",
                    "Value"
                  ]
                }
              },
              "SetOptions@odata.type": {
                "type": "string"
              },
              "SetOptions#Id": {
                "type": "array",
                "items": {
                  "type": "integer"
                }
              },
              "SetOptions#Id@odata.type": {
                "type": "string"
              },
              "{Identifier}": {
                "type": "string"
              },
              "{IsFolder}": {
                "type": "boolean"
              },
              "{Thumbnail}": {
                "type": "object",
                "properties": {
                  "Large": {},
                  "Medium": {},
                  "Small": {}
                }
              },
              "{Link}": {
                "type": "string"
              },
              "{Name}": {
                "type": "string"
              },
              "{FilenameWithExtension}": {
                "type": "string"
              },
              "{Path}": {
                "type": "string"
              },
              "{FullPath}": {
                "type": "string"
              },
              "{HasAttachments}": {
                "type": "boolean"
              },
              "{VersionNumber}": {
                "type": "string"
              },
              "Title": {
                "type": "string"
              }
            },
            "required": [
              "@@odata.etag",
              "ItemInternalId",
              "ID",
              "FunctionID",
              "Quantity",
              "Modified",
              "Editor",
              "Editor#Claims",
              "Created",
              "Author",
              "Author#Claims",
              "SetOptions",
              "SetOptions@odata.type",
              "SetOptions#Id",
              "SetOptions#Id@odata.type",
              "{Identifier}",
              "{IsFolder}",
              "{Thumbnail}",
              "{Link}",
              "{Name}",
              "{FilenameWithExtension}",
              "{Path}",
              "{FullPath}",
              "{HasAttachments}",
              "{VersionNumber}"
            ]
          }
        }
      }
    }
  }
}

 

 

 

The easiest way for me to understand the requirement correctly would be to see JSON samples of the input and the desired output. Is this possible from one flow run history (change the values of sensitive data).

 

Right now I think xpath could be the way to go by using a select action and transform the data.

txkimmers
Frequent Visitor

I have been trying a few other things on my own and work got busy!  I will respond with the information you need asap, sorry for the delay.

txkimmers
Frequent Visitor

I managed to solve this problem by taking a different tack.  After creating the file and the getting the first SP list values, I set an Apply to each to loop through the SP list values, then looped select/join the values for the multi-choice column and then added a row to the spreadsheet.  So basically instead of trying to get all the values for the export lined up and ready to use after getting the matching sub-group from list 2, I am going to do the export in two stages.  Stage 1 will create the rows, add the concatenated string values into the correct column, then Stage 2 will be updating the rows with matching values from the other SP list.

Now I am working through updating the rows of that worksheet with the matching values from the second list.  Having some trouble since that is also new to me, but that is best saved for another ticket.

Hello @txkimmers

 

The problem with apply to each is that it takes long and consumes a lot of requests (depending on the amount of loops).

 

But if it is good enough it is perfect when you found your solution.

txkimmers
Frequent Visitor

Yes, as I am discovering, there isn't way to update multiple columns at per row when you update a row?  This is not going to work long term but it will have to do for now.  Thanks much for your help though.

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,010)