cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
llacefield
Level: Powered On

Auto Increment Based of Matched Value of SharePoint List Column

Increment-SubJob.jpg

I have the above form that I'd like to have the txt_SubJob.text field auto-increment based on the MATCHED value that's entered into the txt_JobNumber.text field. 

So, the user already knows the JobNumber but isn't aware of the next available SubJob value. When the user enters the job number, it will then check back to a SharePoint List column for the matching value and then detects the SubJob column for the next available number. I have utilized an auto-increment function before but only off of one SharePoint column. Any assistance would greatly be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Auto Increment Based of Matched Value of SharePoint List Column

Hi @llacefield ,

Could you please share a bit more about your scenario?

Does single one JobNumber match multiple SubJob value? e.g. JobNumber 0001 -> SubJob1, SubJob2, SubJob3?

 

Based on the issue that you mentioned, I think the LookUp function could achieve your needs. On your side, within the Edit form, set the Default property of the Text Input box within the SubJob Data card to following:

If(
   EditForm1.Mode = FormMode.New,        /* <-- EditForm1 represents the Edit form in your app */
   Last(Filter('YourSPList', JobNumber = JobNumberTextInputBox.Text)).SubJobColumn + 1,
Parent.Default )

If the JobNumber column is a Number type column in your SP list, please consider modify above formula as below:

If(
   EditForm1.Mode = FormMode.New,
   Last(Filter('YourSPList', JobNumber = Value(JobNumberTextInputBox.Text))).SubJobColumn + 1,
Parent.Default )

Please consider take a try with above solution, check if the issue is solved.

Note: I assume that the SubJobColumn is a Number column in your SP List.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
RezaDorrani
Level 10

Re: Auto Increment Based of Matched Value of SharePoint List Column

Hi @llacefield 

 

For the subjob data card, first unlock the data card

 

Then  for default property on the SUB JOB data card, write your query to fetch the next sub job id for the entered job number

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Super User
Super User

Re: Auto Increment Based of Matched Value of SharePoint List Column

Hi @llacefield 

 

I've implemented something similar before.

 

Assuming that your subjob items all have a field that references the Main Job's Job Number or unique ID field, and your SubJobNumber field is a number column/field we can use Last and LookUp to find out what the last number was and then +1 to that.

 

So what I implemented was a label beneath subjob that says something along lines of

"The next available job number is: " + (Last(Filter(SubJobs, MainJobIDReference=JobNumberField.Text)).SubJobNumber +1)

So what we're doing is filtering only the subjobs that reference the MainJobID, then we're getting the last one, then we're getting that last one's SubJobNumber and adding one.

 

This works great if the users follow your advice, however you might want to make the field uneditable (hide the Text input and only have a label) and then have it strictly set the subjob number using that formula, that way there's no way for the user to create duplicates subjobs. It all depends on the use case.

 

Any questions please feel free to shout,

 

Cheers,

ManCat




Don't forget to 'Mark as Solution' and 'Thumbs Up' those who deserve it!
Highlighted
Community Support Team
Community Support Team

Re: Auto Increment Based of Matched Value of SharePoint List Column

Hi @llacefield ,

Could you please share a bit more about your scenario?

Does single one JobNumber match multiple SubJob value? e.g. JobNumber 0001 -> SubJob1, SubJob2, SubJob3?

 

Based on the issue that you mentioned, I think the LookUp function could achieve your needs. On your side, within the Edit form, set the Default property of the Text Input box within the SubJob Data card to following:

If(
   EditForm1.Mode = FormMode.New,        /* <-- EditForm1 represents the Edit form in your app */
   Last(Filter('YourSPList', JobNumber = JobNumberTextInputBox.Text)).SubJobColumn + 1,
Parent.Default )

If the JobNumber column is a Number type column in your SP list, please consider modify above formula as below:

If(
   EditForm1.Mode = FormMode.New,
   Last(Filter('YourSPList', JobNumber = Value(JobNumberTextInputBox.Text))).SubJobColumn + 1,
Parent.Default )

Please consider take a try with above solution, check if the issue is solved.

Note: I assume that the SubJobColumn is a Number column in your SP List.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
llacefield
Level: Powered On

Re: Auto Increment Based of Matched Value of SharePoint List Column

@v-xida-msft 

 

That is correct, one JubNumber could have multiple SubJob Numbers. So the scenario is that there is a limited number of JobNumbers as the Job Cost accountant provides a range of job numbers to be assigned per customer (why it's that way don't ask, it should be randomized) so Customer1 has a job number range of 100 - 149 Customer 2, 150 - 199, etc. There could be a job that requires additional work that wasn't captured in the scope and in order to track the additional work the will create a SubJob in order to track the additional cost to the original JobNumber. If that makes sense. 

I believe the formula that you provided will work. I'm trying it now and will report back in a few moments with an update.

llacefield
Level: Powered On

Re: Auto Increment Based of Matched Value of SharePoint List Column

@iAm_ManCat 

 

I appreciate your comment! What @v-xida-msft  and yourself provided was exactly what I was looking for. As always, I greatly appreciate these forums! Thanks, guys.

Super User
Super User

Re: Auto Increment Based of Matched Value of SharePoint List Column

You're welcome, glad to help Smiley Happy




Don't forget to 'Mark as Solution' and 'Thumbs Up' those who deserve it!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 54 members 4,572 guests
Please welcome our newest community members: