cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lkosa
Regular Visitor

Create conditional auto increment based on data card value

Hello,

My apologies in advance for what is likely a repost, but I have been scouring these forums all day and can't quite find another example that has been able to help me. Ready to rip my hair out as I figured I could find a solution in a normal amount of time.

 

Canvas app, excel dataset, well under 2000 records. What I am attempting to do is have an ID number auto generate when a new record is created, based on the region that will be selected during the process of this new record creation.

 

I made my first attempt using the Last function, I'm thinking Last or Max theoretically could fill this role in my uneducated opinion. I thought I finally got it with Max but did not. No error messages, just not returning the proper value.

 

Current formula is: 

 

If(
    EditFormNew.Mode = FormMode.New,
    LookUp(
        Table1,
        Region = DataCardValue73.Selected.Value,
        Max(IDNumber + 1)
    ),
    ThisItem.IDNumber
)

 

 

I've tried a few different iterations of this formula (like moving Max before LookUp) but I always get returned an error, or a 1 or a 2. The selected region during new record creation does not have any effect on what is returned. 

 

Any help on returning the the highest/most recent (should be the same!) IDNumber and adding 1 to it for a specific Region?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi  @Lkosa ,

 

The below should do the trick: it first filters Table1 for the selected region and then sorts the returned items by IDNumber. The last Item is then selected and its' IDNumber increased by 1:

 

If(
    EditFormNew.Mode = FormMode.New,
    Last(
		Sort(
			Filter(
        		Table1,
        		Region = DataCardValue73.Selected.Value,
        	),
			IDNumber
    	)
    ).IDNumber+1,
    ThisItem.IDNumber
)

 

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

2 REPLIES 2
BCBuizer
Super User
Super User

Hi  @Lkosa ,

 

The below should do the trick: it first filters Table1 for the selected region and then sorts the returned items by IDNumber. The last Item is then selected and its' IDNumber increased by 1:

 

If(
    EditFormNew.Mode = FormMode.New,
    Last(
		Sort(
			Filter(
        		Table1,
        		Region = DataCardValue73.Selected.Value,
        	),
			IDNumber
    	)
    ).IDNumber+1,
    ThisItem.IDNumber
)

 

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
Lkosa
Regular Visitor

Wow, thank you for the succinct explanation to accompany the working code!!!

 

Sidenote for others - I have a decent amount of inconsistent and duplicate ID numbers in this dataset, most being a 4 digit (eg. 1001). Overtime as my table has changed hands, others had started the ID naming metric starting at 1 (they should be punished). So I had single digit IDs, 4 digit IDs, and lots of duplicates.

 

This formula did not work until I standardized the ID numbers all into 4 digits. One would think that this code should still work by returning that larger 4 digit ID, the one i wanted, but for some reason the code was getting caught up with the single digits.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

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