cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taubry
Advocate II
Advocate II

complex function to implement +1

Hello,

I can't create a function with Match and lookup...

Imagine an array:

France-F01-01

France-F01-02

France-F01-03

France-F01-04

France-F01-05

Germany-F01-01

Germany-F01-02 etc

             ...

 

In a text I want to return the last element starting with "France-F01" here "France-F01-05". The objective is to implement +1 in order to create France-F01-06. Here is my last function:

Upper(
     "France-F01" & If(
        CountRows(
            Filter(
                Gestion;
                StartsWith(
                    Référence;
                    Upper(
                        "France-F01"
                    )
                )
            )
        ) + 1 < 10;
        "0";
        ""
    ) & CountRows(
        Filter(
            Gestion;
            StartsWith(
                Référence;
                Upper(  "France-F01"
                    )
                )
            )
        )
    ) + 1
)

which counted the lines in order to add 1 to the number of lines.

My application must be able to delete a row, so the row counting system was not working.

My intention is then to find the element whose last two digits are the maximum, with this regular expression:

"\ d {2} $" and then add +1.

I don't know how to find the maximum (here 05), I thought of doing a lookup of a filter but I can't do it.

 

Thanks in advance, I would be lost without the work of those who respond on this forum...

1 ACCEPTED SOLUTION

Accepted Solutions
C-Papa
Super User
Super User

hi, yes, add a text column to your datasource, let's call it Delete. Set the default to "No". Then change your current filters / searches that you are using to filter the data where delete = "No". this means in your listed you will only see live records. if someone wants to delete a record, instead of actually deleting it, just patch the record and add "Yes" to the delete column. this will not longer show in you tables as delete has changed to yes. 

 

When you want to create a new record you can use lookup() to get a deleted record, e.g. where Delete = "Yes". lookup will return 1 record at a time, therefore, if lookup finds a record you can patch the new information to the old record and change delete from "Yes" to "No" so it appears in your gallery, if look up doesn't return a record, no deletes exist so you need to create a new record with a new number using the above formula

View solution in original post

4 REPLIES 4
C-Papa
Super User
Super User

Hi, you don't need to use upper as you are typing the text string, therefore, you can type what you want. the below should give you the results you are after. 

 

"FRANCE-F01-" & Text(CountRows(Filter(Gestion; StartsWith(Référence; "France-F01"))) +1,"#####00") 

 

at the moment this is run, it will give you the record count. if you delete a record the count would still return the correct count but that number may already exist. for example, if you have 5 records, and delete the second (02), the count would become 4 and your next record would be entered as 04. but 04 would already exist. you have two options, when a record is deleted you re assign a number to all of the remain records, or, you don't actually delete the record, just hide it. when using unique numbers in databases this is often the case. for example, in a SharePoint list you can only have 1 record with the ID 1, even if record 1 is deleted. this is to allow the record to be restored etc.

 

a good work around for you is to mark the record as deleted, and the next time an entry is submitted, rather than creating a new record, you just update the existing one that is marked as deleted. this means that the numbers will stay in order.  

taubry
Advocate II
Advocate II

Thank you for your answer,

I cannot change the numbers of the records, therefore, I cannot re-assign a new number. Your second option seems interesting to me, but I don't understand how to do it.

I have to add a delete column and each deletion, do I put something in that column so that I can replace the deleted items? But I have a search interface that allows you to view all the lines. So I have to implement a condition, except the deleted ones?

Simply search for the last two digits to take the maximum in order to make +1 is not possible?

C-Papa
Super User
Super User

hi, yes, add a text column to your datasource, let's call it Delete. Set the default to "No". Then change your current filters / searches that you are using to filter the data where delete = "No". this means in your listed you will only see live records. if someone wants to delete a record, instead of actually deleting it, just patch the record and add "Yes" to the delete column. this will not longer show in you tables as delete has changed to yes. 

 

When you want to create a new record you can use lookup() to get a deleted record, e.g. where Delete = "Yes". lookup will return 1 record at a time, therefore, if lookup finds a record you can patch the new information to the old record and change delete from "Yes" to "No" so it appears in your gallery, if look up doesn't return a record, no deletes exist so you need to create a new record with a new number using the above formula

taubry
Advocate II
Advocate II

Thank you ! I will try this 😉

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,176)