Auto increment columns in PA's

(0) Share

Report

Posted on by ilanos

136

Hello,

I would like to auto increment columns based on the Document Type.

If the Document Type = Procedure, then auto increment should start with 1000.

If the Document Type = Manual, then auto increment should start with 2000.

If Document Type = Work Instr, then auto increment should start with 3000.

Each time the request is submitted, the number should increment by 1.

I know that there is a Last() function to get the last number used.

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

WarrenBelz 153,789 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @ilanos ,
First thing, put in a dummy document of each type and start the sequence, then you have a starting number for each category. Note particularly as below that you will need to store the category in a field so the next number for the type can be found.
So using the following names (change to yours)
Your list field is AutoNo
Your Library name SPDocs
You have a Choice field in this called DocType which is populated by the drop-down called ddType
You would do something like this. Please understand the logic when replacing the names as I free-typed this and may need some cleaning up with brackets/commas etc.
Patch( ThisItem, { AutoNo: First( Sort( Filter( SPDocs, DocType=ddType.Selected.Value ).AutoNo ), AutoNo, Descending )+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.

Was this reply helpful? Yes No
WarrenBelz 153,789 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @ilanos ,
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.

Was this reply helpful? Yes No
ilanos 136 on at

Like (0)

Report
Copy link

Link copied!
This isn't working for me. This is what I have. Unable to use ThisItem. Get an error.

Patch( 'QMS New Document-Type', First( Filter( 'QMS New Document-Type', 'Document Type'.Value = DataCardValue4.Selected.Label ) ), { AutoNum} )

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @ilanos

I think @WarrenBelz 's solution is nearly correct. If I'm not mistaken it should be:

Patch(
ThisItem,
{
AutoNo:
First(
Sort(
Filter(
SPDocs,
DocType=ddType.Selected.Value
).AutoNo
),
AutoNo,
Descending
).AutoNo+1
}
)

Was this reply helpful? Yes No
ilanos 136 on at

Like (0)

Report
Copy link

Link copied!

ks,

Im not using a gallery. Im using the Edit form. ThisItem doesn't work for me.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@ilanos

The Patch() function requires three elements, the datasource, the record and the columns being patched. To make it work outside of a gallery, replace ThisItem with a lookup to the record in the datasource. Ie

Patch(Yourdatasource, Lookup(yourdatasource, ID = yourrecordID), {columnbeingpatched: yourdata})

to patch an existing record. To create a new record replace the Lookup() with Defaults()

Patch(Yourdatasource, Defaults(Yourdatasource), {columnbeingpatched: yourdata})

No matter where you execute the patch from, you will need to find a unique value that identifies the record, (usually ID), unless you are creating a new one.

Was this reply helpful? Yes No
ilanos 136 on at

Like (0)

Report
Copy link

Link copied!
This is what I have:

Patch('QMS New Document-Type', Lookup('QMS New Document-Type', ID = "ID1"), {AutoNumber: AutoNumber_7.Selected.Result})

Was this reply helpful? Yes No
ilanos 136 on at

Like (0)

Report
Copy link

Link copied!
Im getting close. How to I get the ID?

Patch('QMS New Document-Type', LookUp('QMS New Document-Type',ID="ID1"),{'AutoNumber': Value(AutoNumber_7.Selected.Result)})

Was this reply helpful? Yes No
WarrenBelz 153,789 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@ilanos ,
Back in my timezone and thanks @Drrickryp ,
My post was based on the assumption that you had a form (or gallery) you were running this from and @Drrickryp's correction was right - dangers of free-typing and not testing, however I now see you are choosing the AutoNumber from a drop-down rather than generating it, so this is irrelevant.
You have an Edit form, therefore the reference ThisItem.ID will work from within the form. You can do this in two ways - run the button from inside a card (a Custom Card will do) on the form OR put a label (you can hide it) in any card on the form (you will have to unlock it) with ThisItem.ID as the Text and then refer to Value(YourLabelName.Text) for the ID.

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.

Was this reply helpful? Yes No
ilanos 136 on at

Like (0)

Report
Copy link

Link copied!
I was able to Patch using this formula. How do I incremental AutoNumber by 1 using Value(ListBox2 +1 and store it in AutoNumber?

Patch('QMS New Document-Type',First( Filter('QMS New Document-Type', 'Document Type'.Value = DataCardValue4.Selected.Label)), {AutoNumber: Value(ListBox2.Selected.Result +1)})

Was this reply helpful? Yes No