Lookup Function & Autonumber

(0) Share

Report

Posted on by carrie-anne27

260

Hi All,

I am trying to achieve a lookup that will then populate an automatic number depending on this lookup formula.

This is my first time using the lookup function.

I want to create a "Job Number"

It will go:

Text 1 - Text 2 - Text 3 = Number

in the case above the number should be 0001.

If Text 1, Text 2 & Text 3 are again used I want the number to then roll over to 0002... etc.

Now, Text 1, 2 & 3 can change

Because the above will be a new record I need the number to then go back to 0001.

Hopefully that makes sense.

Categories:

Building Power Apps

All responses (21)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,570 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @carrie-anne27 ,

Try this for a start - I have assumed the Job Number text pattern from your post

With( {_Job: Text1.Text & "-" & Text2.Text & "-" & Text3.Text}, With( { _Data: LookUp( Sort( SPListName, ID, SortOrder.Decending ), StartsWith( 'Job Number', _Job ) ).'Job Number' }, _Job & If( IsBlank(_Data), "0001", Text( Value(Right(_Data, 4)) + 1, "0000" ) ) ) )

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.

MVP (Business Applications) Visit my blog Practical Power Apps
carrie-anne27 260 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Warren, I will give this a go! Just to clarify, when you mention _Job & _Data what are you referring to?
WarrenBelz 156,570 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@carrie-anne27 ,

They are simply temporary Variables - you can call them whatever you want.
carrie-anne27 260 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry, another question, what is ID and 'Job Number' referring to?
WarrenBelz 156,570 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@carrie-anne27 ,

I am assuming you are using SharePoint here - ID is just sorting newest first. 'Job Number' is the name of the field you posted - if it is different, change to the actual name.
carrie-anne27 260 on at

Like
a
(0)

Report
Copy link

Link copied!

Okay, so ID doesn't refer to any column in my table? (its a Dataverse)
WarrenBelz 156,570 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@carrie-anne27 ,

It is always good to post your data source in your initial post as this possibly affects the response. You need to sort by the created date - I believe it is 'Created On'
carrie-anne27 260 on at

Like
a
(0)

Report
Copy link

Link copied!

Alright, this is what I have entered

The errors are:
1. The function 'LookUp' has some invalid arguments
2. The function 'Sort' has some invalid arguments
3. Invalid use of ' '
4. The function 'Right' has some invalid arguments.
WarrenBelz 156,570 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@carrie-anne27 ,

The sort needs to be on a valid column name representing the created date (I am not a Dataverse user). The bottom error results from the top one. 'Auto Number' is your field name - what type of field is it (although it makes no sense that it is valid in the filter, but not valid in the output).
carrie-anne27 260 on at

Like
a
(0)

Report
Copy link

Link copied!

The "Created On' column is automatically created when you create a Dataverse table and any added rows get data.

The auto number column in a number column. Does it make any difference if my input is a label or text input?