cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Sequential number with new item based

I have a large list of Students who can be separated into groups by GroupTitle.  each item has its own ID of course but I wish to have a second ID Column with the "WithinGroupID"  So a group called "BlueGroup" with 10 students would have a new ID starting at 01.

 

I have added a field to the Student List called "WithinGroupID", it is a number type and I have "GroupTitle" field.   I know I need to look at GroupTitle and look at the last "WithinGroupID that exists for other Items with the same GroupTitle and +1 but not sure how to go about it.  Hope this makes sense.

11 REPLIES 11
Highlighted
Super User III
Super User III

Hi @StuCundy ,

If you add numeric fields for all the groups (with the Default of zero) to your list, you can find the next number with (I will use BlueGroup as a name)

First(
    Sort(
        WithinGroupID,
        BlueGroup,
        Descending
    )
).BlueGroup + 1

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted

Hi @WarrenBelz 

Thank you for your reply.  Don't I want to add this Number type field to the Student List? 

 

I want the student to have the ID but it is respect to the group they belong to and is unique to that group.    Like If I had a product that was part of a category group and each variety products of product in that group had a 2nd unique ID number. 

 

I can see I could create a separate list for each group of students but it would be great to just have one list and filter by group.

I wish to filter to the GroupTitle field.

Highlighted

Hi @StuCundy ,

If you really need to make it this way then you need a numeric field in the list and something like this - note I have free-typed so watch commas and brackets.

If(
   IsBlank(
      Lookup(
         WithinGroupID,
         GroupTitle = YourGroupName,
         YourNumberField
      )
   ),
   1,
   First(
      Sort(
         Filter(
            WithinGroupID,
            GroupTitle = YourGroupName
         ),
         Descending
      )
   ).YourNumberField + 1
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted

Hi @StuCundy ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted

Hi @WarrenBelz 

Thanks for your code.  I was a bit confused.  By number field is WithinGroupID (now called "LHSID")

 

So when you had 

[IsBlank(Lookup(LHSID,GroupTitle = YourGroupName,YourNumberField  ]  I am not sure what to do as I though my number field was the LHSID field.

 

Also the GroupTitle = YourGroupName  : The GroupTitle is the Value I want to reference and add a new number to the sequence, only with new records.

 

To Clarify, In my list I want any new record to have the LHSID number to be the next number in sequence after the highest existing number with other records sharing the same GroupTitle.

 

To be honest I can just sort the list into LHSID order and just manually enter the number myself but we all love automation. 🙂

Highlighted

Hi @StuCundy 

To avoid any confusion (and I cannot see your data, so I won't name anything) - I will use Your in front of every reference - you just need to replace these with your actual list and field names - the only name I am reasonably sure of is GroupTitle as you have specified this as the field you want to search on.

If(
   IsBlank(
      Lookup(
         YourListNsme,
         GroupTitle = YourChosenGroupName,
         YourNumberField
      )
   ),
   1,
   First(
      Sort(
         Filter(
            YourListName,
            GroupTitle = YourChosenGroupName
         ),
         Descending
      )
   ).YourNumberField + 1
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Highlighted

Hi @StuCundy ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted

Hi @WarrenBelz 

 

I used the below  in the default function of LHSID input label.  if editing a record the field goes to "2".  If new the field goes to one.

With Editing an item the number of LHSID number always wants to go to "2" 
On New the LHSID goes to 1.
The problem I this that the "GroupTitleDropdown.Selected.Title" is dictating my group.

If(
   IsBlank(
      LookUp(
         GroupStudents,
         GroupTitle = GroupTitleDropdown.Selected.Title,
         LHSID
      )
   ),
   1,
   First(
      Sort(
         Filter(
            GroupStudents,
            GroupTitle = GroupTitleDropdown.Selected.Title
         ),
         Descending
      )
   ).LHSID + 1
)

 

Highlighted

Hi @StuCundy ,

A little confused - what do you want to use are the criteria for updating the record? My syntax was more around adding the number for you.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,702)