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

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.
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.
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
Community Champion
Community Champion

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

@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
Community Champion
Community Champion


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
Community Champion
Community Champion

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

martinav
Community Champion
Community Champion

@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
Community Champion
Community Champion

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
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 (1,254)