cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Community Champion
Community Champion

How to Concatenate() inside a ForAll loop.

This is a simple example, and I'm sure there are better ways to do it, but when this gets more complex, I will be needing to append to the end of a string every 'loop' of the ForAll().  Not quite sure how to do that since I'm not able to set a variable inside of it.  Or, is there a way?

 

 

 

Set(TestList,[12345,12346,12347,12348,12350,12351]);

Set(TestString,"Test");
ClearCollect(Testdcollect,(ForAll(TestList.Value, Concatenate("Text",Text(Value))
       )))

 

 

Desired result for this example is:

 

TestList = "12345,12346,12347,12348,12350,12352,12354,13000"

 

My end-all desired is:

 

TestList = "12345-12348, 12350, 12352-12354, 13000"


Thanks,
Rod

16 REPLIES 16
Eelman
Super User
Super User

@martinav 

This is a really interesting challenge ... and I like those 🙂

 

I expanded on @timl idea of a collection and think I have a solution for you:

 

// set up your list of values
Set(TestList,[1,2,3,7,8,9,20,10,5,4]);

// clear the collection before ForAll
Clear(colTestList);


ForAll( 
    // sorting the list of values makes sure you  can add more values in any order without error
    Sort(TestList, Value,Ascending) As Count,

    // build collection of first and second values
    If( 
        // create first row in collection
        CountRows(colTestList)= 0 , Collect( colTestList, { FirstCol: First(TestList).Value, SecondCol: First(TestList).Value}),
        
        // update collection rows or add new ones as required
        Count.Value = Last( colTestList).SecondCol+1,
        Update(colTestList, Last(colTestList), {FirstCol: Last(colTestList).FirstCol ,SecondCol: Count.Value}),
        Collect(colTestList, { FirstCol: Count.Value, SecondCol: Count.Value}
        )
    )
)

 

Add a label and add this to the Text property

Concat( colTestList, 
            FirstCol & 
                If( FirstCol = SecondCol, "," ,"-" & SecondCol & ",")
)

Your output will be;

1-5,7-10,20,

 

You can then use string manipulation to make it look how you like eg remove the finally comma, etc

View solution in original post

timl
Super User
Super User

@Eelman - Wow, that's a great solution - thanks for sharing that 🙂

For anyone else, this might help illustrate the purpose of the FirstCol and SecondCol columns.

timl_0-1630492156871.png


The only slight caveat I have is that ForAll can execute in parallel, and not execute the formula in the correct sequence. This is what the documention says:

 

>> When writing your formula, keep in mind that records can be processed in any order and, when possible, in parallel. The first record of the table may be processed after the last record.

 

In practice, I find that ForAll works sequentially most of the time but I have seen rare occasions where users have reported problems. If anyone is worried about this, I guess it's possible to store some additional sequence values in the colTestList collection to help identify cases where ForAll has executed the formula in parallel.

 

Overall, I think what you've posted here is great and workable in most cases. Thanks again!

martinav
Community Champion
Community Champion

Wow, you certainly took this to the desired outcome.  I was just asking for part of it because I know people dont have a lot of time to give away on forums.  I cant thank you enough for your pursuit of this solution!  Thanks so much!

@martinav 

No worries, happy to help :). I had a spare 10-15mins and thought I'd give it a crack!

 

I will also stress, as @timl has done, that ForAll() is documented as having potential sequence issues and this is worth noting. Personally, I've never seen it in action but then I only rarely use ForAll() and typically look for other ways to do things first, then only use it if I have to.

 

@timl 

Thanks for the praise mate, it's very well appreciated coming from a SU at your level :). It was your idea of a collection that got me thinking that I could use it like an 'in function variable' that I could update sequentially ... and it worked! Keep up the great work on the forums mate!

 

 

timl
Super User
Super User

Thanks for your kind words @Eelman 🙂 Likewise, keep up the great work on the forums yourself too!

martinav
Community Champion
Community Champion

@Eelman ,

 

Ok, this worked great for the first case.  Now, I need to cram this into a gallery.  So, it has to all go together, including the Concat() statement.  I get stuck with the collections because they break in galleries.  

 

The input for this case is a label.  With a list of numbers separated by columns.  I make that into my array like this:

 

Sort(Split(Label1.Text,",").Result), Result, Ascending)

 

Putting this in the ForAll() then get this going, but not sure how to make Collect() into Table() or something that can be used in the gallery.  I'm still looking for the same text output.

@martinav 

Can you explain a little further as to what you mean by " ... I need to cram this into a gallery"? Can you share some pics of what you are trying to do and what the inputs/outputs are.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,484)