web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / How to collect and cre...
Power Apps
Answered

How to collect and create columns with input from other columns

(0) ShareShare
ReportReport
Posted on by martinav 3,347

Let me provide a visual for what I'm trying to do here.

 

martinav_0-1634047832175.png

In this case, I want to use _SNValueFixed in the next definition of _TestUseLast.  I dont get any errors in the statement, but I get this:

 

martinav_1-1634047944056.png

 

 

Categories:
Building Power Apps
I have the same question (0)
  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @martinav 

    Please consider changing your Formula to the following:

    ClearCollect(
 DataItems,
 With({_data:
 With({_items: FirstN(Filter(temporary_assign_wo, Updated<>3), IfError(Value(TextInput6.Text), 0))},
 ForAll(_items As _item,
 With({_sn: Substitute(_item._SNValue, ";", ",")},
 Patch(_item,
 {_SNValueFixed: _sn,
 _TestUseLast: "Test" & _sn
 }
 )
 )
 ),
 ForAll(Sequence(CountRows(_data)), Patch(FirstN(_data, Value), {Index: Value}))
 )
 )
)

     

    This should give you what you are looking for.  Use a ForAll as the function it was designed for - a Table producing function, not a For/Loop statement.

     

    I hope this is helpful for you.

  • martinav Profile Picture
    martinav 3,347 on at

    @RandyHayes ,

     

    Thanks for such a quick reply!

    I will try this.  A lot going on there, and its hard for me to follow.  Overall, I get what you are doing.  Its getting hard to follow, because I'm not as smart as most here.

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @martinav 

    Yes, give a shot.

    Essentially, it is first getting the items from the filter.  Then it is creating a table (ForAll) of those items.  It patches the original record from the _items into a new record with two added columns.  The inner With statement with the Substitute is just to avoid typing the function twice as it is used for both columns.

    Finally...the entire result of all of that is being assigned to a _data scoped variable.  It is then used to create the final table (from ForAll) that has all of the initial records produced within the _data table and adds a column that is a sequential count (index) of the records.

    All of that is then ultimately returning a Table...that table is then applied to a Collection (really no need for a collection in this case unless you are planning to add, remove, or edit rows within it).

     

  • martinav Profile Picture
    martinav 3,347 on at

    @RandyHayes ,

     

    I may have hurt my brain trying to figure this out.  Since its hard to write solutions in the blind, I think I found a couple parenthesis and bracket issues, but, i'm stuck on the Patch source in the very last part.  I just cant see what needs to go there.  

     

    ClearCollect(
 DataItems,
 With({_data: 
 With({_items: FirstN(Filter(temporary_assign_wo, Updated<>3), IfError(Value(TextInput6.Text),0))},
 ForAll(_items As _item,
 With({_sn: Substitute(_item._SNValve, ";", ",")},
 Patch(_item,{_SNValveFixed: _sn,_TestUseLast: "Test" & _sn})
 )))},
 ForAll(Sequence(CountRows(_data)), Patch(FirstN(_data, Value), {Index: Value}))
 )
)

    I very well could have broke something.  It actually has no errors, but after I run it, this part turns red:

     

    martinav_0-1634055894704.png

     

     

  • martinav Profile Picture
    martinav 3,347 on at
    that has all of the initial records produced within the _data table and adds a column that is a sequential count (index) of the records.

     

    @RandyHayes ,

     

    Actually, index needs to be what comes from the temporary_assign_wo.index.  Those values must pass through, since it is the only way to associate this table to the original.

  • martinav Profile Picture
    martinav 3,347 on at

    @RandyHayes ,

     

    Actually, that text you gave explaining the flow made me see what you were doing with Index.  I just needed to pass that through from temporary_assign_wo.Index, so here is the working function.

     

     

    ClearCollect(
 DataItems,
 With({_items: FirstN(Filter(temporary_assign_wo, Updated<>2), IfError(Value(TextInput6.Text),0))},
 ForAll(_items As _item,
 With({_sn: Substitute(_item._SNValve, ";", ",")},
 Patch(_item,{_SNValveFixed: _sn,
 _TestUseLast: "Test" & _sn,
 Index: _item.Index})
 )))
 )

     

    Ok, so... I think the moral to this story is that you CANNOT use column definitions from within the Patch statement to feed in to other column definitions farther down the list.  Is this correct? 

     

  • Verified answer
    RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @martinav 

    Yes, I might have misunderstood your original use of the Index.  It appeared it was something you were trying to generate. 

    If you are trying to relate back to your data source, then you should be using the primary key of your datasource....if index is it, then you are good.

     

    AND...the change you made to the formula would be accurate based on that assumption.

     

    But, since you're really just adding two columns to a table then the Index already exists in the record and there is no need to expose it further - as you're not using it in the formula.

    So, the formula can be simplified to the following:

    ClearCollect(
 DataItems,
 ForAll(FirstN(Filter(temporary_assign_wo, Updated<>2), IfError(Value(TextInput6.Text),0)) As _item,
 With({_sn: Substitute(_item._SNValve, ";", ",")},
 Patch(_item,
 {_SNValveFixed: _sn,
 _TestUseLast: "Test" & _sn,
 }
 )
 )
 )
)

    Here we are creating a table (ForAll) from the table of FirstN records (called _item).

    Then we're just doing a simple scoped variable to hold the substitute results to use twice.

    Then, each record of our Table (ForAll) will be made of the original _item record with two columns added.

     

    You could have used AddColumns for this, but you would not be able to use the With function on that and would have had to duplicate your formula functions over and over.

  • martinav Profile Picture
    martinav 3,347 on at

    @RandyHayes ,

     

    Yea, the With() statements is what makes this work.  I didnt realize until the end that all columns were passing through, so yes.  I didnt need to have Index in the patch.  Actually, this is a very simple example just to get the structure down.  My actual application of this is quite extensive.  I'll post just for your enjoyment!  😄  I'm almost done with it.  I'm doing some crazy stuff.

  • martinav Profile Picture
    martinav 3,347 on at

    Well, here is the finished product.  Its quite ugly.  But your structure is what get this working, finally!  

    What this actually does is to take a range of numbers, i.e. "1034, 1040-1045, 1088, 1090" to "1034,1040,1041,1042,1043,1044,1045,1088,1090".

     

    It also takes into account when the number has an alpha prefix, such as "VR1010-1013, 1044,"  converts to:  "VR1010,VR1011,VR1012,VR1013,VR1044"  It also allows for exceptions when VR is not at the prefix of the other numbers.  It also accounts for spaces between the alpha prefix and the number, i.e. "VR 1010-VR1013" etc.

     

    Thanks much.

     

     

    ClearCollect(
 DataItems,
 With({_items: FirstN(Filter(temporary_assign_wo, Updated<>3), IfError(Value(TextInput6.Text),0))},
 ForAll(_items As _item,
 With({_sn: Substitute(_item._SNValve, ";", ",")},
 With({_SN_Prefix: Trim(Left(_sn,
 Max(
 ForAll(Sequence(Len(First(Split(First(Split(_sn,",").Result).Result,"-").Result).Result),1,1).Value,
 If(IsNumeric(Mid(First(Split(First(Split(_sn,",").Result).Result,"-").Result).Result,Value,1))=false
 ,Value)).Value,
 Value)
 )
 )},
 Patch(_item,{_SNValveFixed: _sn,
 _TestUseLast: "Test" & _sn,
 Index: _item.Index,
 SN_Prefix: _SN_Prefix,
 SN_List: Match(
 Concat(
 ForAll(
 TrimEnds(
 Split(_sn,",")).Result,
 If(
 IsBlank(Find("-",ThisRecord.Result)),
 ThisRecord.Result,
 If(_SN_Prefix="",
 
 Match(Concat(
 Sequence(Value(Last(Split(ThisRecord.Result,"-").Result).Result)
 -Value(First(Split(ThisRecord.Result,"-").Result).Result)+1,
 Value(First(Split(ThisRecord.Result,"-").Result).Result),1).Value,
 Concatenate(Text(Value),",")
 ),
 "^(?<trim>.*),$"
 ).trim,
 Match(Concat(
 Sequence(Value(Last(Split(Last(Split(ThisRecord.Result,"-").Result).Result,_SN_Prefix)).Result)
 -Value(Last(Split(First(Split(ThisRecord.Result,"-").Result).Result,_SN_Prefix)).Result)+1,
 Value(Last(Split(First(Split(ThisRecord.Result,"-").Result).Result,_SN_Prefix)).Result),1).Value,
 Concatenate(_SN_Prefix,Text(Value),",")
 ),
 "^(?<trim>.*),$"
 ).trim
 ))
 ),
 Concatenate(Value,",")
 ),
 "^(?<trim>.*),$"
 ).trim
 })
 ))))
 )

     

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @martinav 

    Yes, when you get into complex string manipulation, it can get ugly.  Glad all is working now.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 356 Most Valuable Professional

#2
11manish Profile Picture

11manish 225 Super User 2026 Season 2

#3
Mohsin Ali Profile Picture

Mohsin Ali 211

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans