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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,117)