cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Super User
Super User

How to collect and create columns with input from other columns

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

10 REPLIES 10
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
martinav
Super User
Super User

@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
Super User
Super User

@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).

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
martinav
Super User
Super User

@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

 

 


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
Super User
Super User

@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? 

 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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
Super User
Super User

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
                                        })
                ))))
        )

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,783)