row (record) id

[Amar]

dear all,

sorry this trivial question but not able to find answer in docs on in comunity.

i have created a collection "Collect" with n.3 rows (1,2,3)and n.2 columns (A,B)

i want to display the value of a specific field (i.e. row 2 colum 2  ) in a label.

the problem is I don't understand how to select row n.2. I just see the possibility to use "FIRST" formula but it works just for row number 1.

FIRST(Collect.A) will give me value in cell "A1", which is fine, but what if i want to select row number 2 or 3 and get field/cell value A2 or A3? how will be the formula?

thanks

[Drrickryp]

Hi @Amar,

Your label should include a lookup for one of the fields. If you have an ID column in the collection and you want to choose the row with ID=2, and a column (say LastName)  the text property of the Label should be: Lookup(mycollection,ID=2, LastName).  If you are choosing an item from a row in a Gallery or DataTable control, the label should be Gallery1.Selected.LastName.

[tchin-nin]

Hi @Amar

Several options depending on what you are trying to achieve.

Let say you have a collection MyCollect :

ID    Value

7      "A"

2      "B"

12    "C"

If you want to select the item with ID=2, then @Drrickryp is right, you need to use a LookUp() function :

LookUp(MyCollect,ID=2) will return the full Item : {ID=2, Value="B"}

LookUp(MyCollect,ID=2,Value) will return the specified property : "B"

If you don't know the ID value but you want the second Item of the collection you need to use a combination of Last() and FirstN() functions. Last return the last item of an array, FirstN the N first items of an array :

Last(FirstN(MyCollect,2)) will return the full Item : Item : {ID=2, Value="B"}

Then you can access one property with a "." like : Last(FirstN(MyCollect,2)).Value : "B"

You can combine this with a Sort() function if you want the item with the second highest ID value :

Last(FirstN(Sort(MyCollect,ID,SortOrder.Descending),2)) will return the full Item : {ID=7, Value="A"} (because 7 is the 2nd highest ID)

If you plan to use this item more than one time (on 2 different controls for example), I suggest you to make it a variable so you don't need to make this "Filter" operation on the whole collection every time :

Set(MyItem,Last(FirstN(Sort(MyCollect,ID,SortOrder.Descending),2)))

Then you can use everywhere you want MyItem.ID or MyItem.Value without any performance issue.

Cheers,

Théo

[v-xida-msft · Accepted solution]

Hi @Amar,

Do you want to display the value of a specific field (i.e. row 2 colum 2) of your Collection in a Label?

I have made a test on my side, please take a try with the following workaround:

• Set the OnVisible property of the first screen of my app to following formula:

ClearCollect(
      Collection1,   /* On your side, it is Colect */
      {A:"1",B:"2"},
      {A:"Kris",B:"PowerApps"},
      {A:"7",B:"8"}
)

The collection shows up as below:

• Set the OnSelect property of the "Retrieve Value" button to following formula (get row number 2 via clicking this button😞

ClearCollect(
     RetrievedCollection,
     Remove(
        FirstN(Collection1,2),
        FirstN(Collection1,1)
     )
)

Note: If you want to get row number 3, please modify above formula as below:

ClearCollect(
       RetrievedCollection,
       Remove(
          FirstN(Collection1,3),
          FirstN(Collection1,2)
       )
)

• Set the Text property of the Label control to following formula:

First(RetrievedCollection).B

Note: If you want to get column 1, please modify above formula as below:

First(RetrievedCollection).A

In addition, you could also take a try with the following workaround:

• Set the OnVisible property of the first screen of my app to following formula:

ClearCollect(Collection1,{A:"1",B:"2"},{A:"Kris",B:"PowerApps"},{A:"7",B:"8"});
ForAll(
    Collection1,
    Collect(
        Collection2,
        {
         A:Collection1[@A],
         B:Collection1[@B],
         Index:CountRows(Collection2)+1
        }
     )
)

On your side, it should be:

ClearCollect(Collect,{A:"1",B:"2"},{A:"Kris",B:"PowerApps"},{A:"7",B:"8"});
ForAll(
    Collect,
    Collect(
        Collection2,
        {
         A:Collect[@A],
         B:Collect[@B],
         Index:CountRows(Collection2)+1
        }
     )
)

• Set the Text property of the Label control to following formula:

LookUp(Collection2,Index=2,B)

Note: If you want to get row number 3 , column 2 (row 3, col 2), please modify above formula as below:

LookUp(Collection2,Index=3,B)

More details about the Remove function and LookUp function in PowerAPps, please check the following article:

Remove function, LookUp function

Best regards,

Kris

[Amar]

thank you all guys, sorry the delay just have today the time to pick again the project.

the solution form @theo is  what i took and works great.

i need excatly to select a row in a collection without knowing the ID, just want to select second, third or whatever row.

Also solution from kris works fine, in my case the solution form Theo was enough!

thank you all!

[Anonymous]

when the ID from list is not 1 we got a problom...

than we can do like this:

(ThisItem.ID) -First(QuestionForAktoariya).ID+ 1

asaf semoun

rgm@gmail.com

[jayprakash]

I have done By this Logic

Select(Parent);RemoveIf(ProductList, Index = Gallery2.Selected.Index);

Clear(collection2);

ForAll(ProductList,
Collect(collection2,
{
Index: CountRows(collection2)+1,
Comments: Comments,
SalesPrice: SalesPrice,
Discount: Discount,
Quantity: Quantity,
Height: Height,
Width: Width,
Color: Color,
ProfileSet: ProfileSet,
Product: Product,
Group: Group
}
)
);
Clear(ProductList);
ForAll(collection2,
Collect(ProductList,
{
Index: Index,
Comments: Comments,
SalesPrice: SalesPrice,
Discount: Discount,
Quantity: Quantity,
Height: Height,
Width: Width,
Color: Color,
ProfileSet: ProfileSet,
Product: Product,
Group: Group
}
)
);
Set(ValueToIncrement, CountRows(ProductList));

wrote: -Jayprakash Birla