How to collect and create columns with input from other columns

[martinav]

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

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:

[RandyHayes]

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

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

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

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

---

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]

@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 · Accepted solution]

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

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

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