Look into the collection till one has found?

[TimmyBoy]

Hello,

Can someone help me
I like to look into the collection for a customer name and see if any value in column "var1" are not empty or blank
And then get the last added "var1" value for that customer only

Then i want to get the info if 'var1' has a value like
Set(varTempCustomer, i like to get here the current data var1 , var2 , var3)

I tried it

Set(varMyTempCustomer;LookUp(Sort(mycollection; ID; SortOrder.Descending);CUSTOMER = varItem1.CUSTOMER;        
If(!IsBlank(Last(mycollection).var1);Concat(Last(mycollection).var1;Value))))

but doesn't work all the time, because if the "var1' isn't a last added data it does nothing.

Data looks like this for example

customer var1 var2 var3
John Doe  V1   2   V3
John Doe                 somevalue  someother value
John Doe  V2  3   V1 
John Doe  V8  6   V5     somevalue  someother value
John Doe                 somevalue  someother value

Also If "var1" has no value
Then i want to keep searching till i found one but i only want to get the last or previous value of that "var1" from that customer
Off Couse till some point there are none "var1" values
I get a message and press ok, to start a empty form.
Var1 is a list in SharePoint.

Is this possible, yes could you show me how
Thank you.

[CarlosFigueira]

To get the last added record to the SharePoint list that does not have an empty 'var1' column, you can use an expression similar to the one below:

First(
    Sort(
        Filter(myCollection; Not IsBlank(var1));
        ID;
        SortOrder.Descending
    )
)

 Looking from the inside-out, it first filters the collection (or SP list) to only keep the rows that have a non-blank "var1" value; the result is then sorted in descending order of the ID column (which is auto-incremented for SharePoint lists); finally we take the first element of that list, which would be the last one that was added.

Hope this helps!

[TimmyBoy]

Hello @CarlosFigueira 

Thank you,
But how to get the data var1 var2 var3 inside my variable
Set(vartempcustomer,
Is this also possible with your example
Thank you

[CarlosFigueira]

If you assign the result of that expression to a variable, you should be able to access it:

Set(
    vartempcustomer;
    First(
        Sort(
            Filter(myCollection; Not IsBlank(var1));
            ID;
            SortOrder.Descending
        )
    )
)

Then you could access those values with expressions such as vartempcustomer.var1, vartempcustomer.var2, and so on...

[TimmyBoy]

Hello,

I tried your code

But it's not working what i like to retrieve
It takes the first 1 or even the last record but the var1 is empty no idea why he takes that record
Also i changed first to last and last to first but same result.
Any solution? so it will take the last input where var1 isn't empty from that customer?

Set(
    varTempCustomer;
    Last(
        Sort(
            Filter(mycollection; CUSTOMER=varItem1.CUSTOMER && Not IsBlank(var1));
            ID;
            SortOrder.Descending
        )
    )

Thank you