get the last item of the list based on filter

[pokhani]

hi Experts

i have text input that i want to display 

for example 

this is my sp list

• i want display the last id in textinput based on file name
•  if i want to add new record to file "X" it will check the last id, add 1 to the id and create new id as X-2021-4 if i try to add X-2021-3 than it will alert me saying record already exist

Thank you again

[phipps0218]

To check whether the ID already exists you will need to add a filter or lookup to retrieve the ID and add your IF statement to either add your ID if the lookup returns blank or if not blank then ID + 1. With incrementing the ID you may have some challenges if your ID is not number format. If it is text format you may need to split it then increment number part and then concatenate. 

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
Subscribe to my YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg

[pokhani]

hi

can you please share the syntax if possible?

[v-qiaqi-msft · Accepted solution]

Hi @pokhani,

To display the last id based on the filtered result based on file name:

Last(Filter(List,FILENAME="X")).ID

Here is another easier solution that we could force the new added ID to be unique without something alert.

1). To add 1 to the last id you get, you should firstly get the last number of the id, which is 3, to get it, you should use the Right() function:

Right(Last(Filter(List,FILENAME="X")).ID​,1)

2). To get the new id, you should replace the last number 3 with 4 and generate a new id:

Replace(Last(Filter(List,FILENAME="X")).ID,8,1,Right(Last(Filter(List,FILENAME="X")).ID+1)

3). I just assume that you use the Patch() function to add a new record, and please set the OnSelect property of the button as below:

Patch(List,Defaults(List),{ID:Replace(Last(Filter(List,FILENAME="X")).ID,8,1,Right(Last(Filter(List,FILENAME="X")).ID+1),FILENAME:"X"})

If you insist on something alert when you input ID within a TextInput, please try as below.

Set the OnSelect property of the button as below:

If(TextInput1.Text=Last(Filter(List,FILENAME="X")).ID,Notify("Duplicated ID",Error),Patch(List,Defaults(List),{ID:TextInput1.Text,FILENAME:"X"}))

[pokhani]

you are awsome and thank you so much it worked

[pokhani]

hi 

what if i have ID as 

US-X-2022-1

CAN-X-2022-1

then will this code work. simply by replacing 8 to 12. and is there a way to make 2022 to select frm drop down?

Replace(Last(Filter(List,FILENAME="X")).ID,8,1,Right(Last(Filter(List,FILENAME="X")).ID+1)

thank you again

[pokhani]

hi Qi, 

On select Property 

Set the OnSelect property of the button as below:

If(DataCardValueID=Last(Filter(Master,FILENAME="X")).CaseID,Notify("Duplicated Case ID",Error),Patch(Master,Defaults(Master),{CaseID:DataCardValueID.Text,FILENAME:"X"}))

but am having error on the patch as it says Title:Title field is required whereas title is caseID

[v-qiaqi-msft]

Hi @pokhani,

Sometimes the Power Apps could not recognize the changed column name if you replace the Title column name with another new one, so the solution is to keep the Title column in your Patch() function.