cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StuCundy
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
WarrenBelz
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.

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.

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.

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.

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. 🙂

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.

 

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.

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
)

 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (34,730)