cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Generate Inventory Number

I didn't see any previous threads on this subject so I thought I'd ask. I have an inventory application. And in that inventory application, an employee can add an item via a dropdown to the inventory from a group of common items.  Each item has a corresponding inventory number like ABC-00-123. Right now, employees have to manually enter that number, which is obviously very inconvienient.

 

I would like to add a function that generates the next inventory number in the series. For example, ABC-00-001 is the last item in that series, the next item would be ABC-00-002. This number would auto-generate in a text-box, and then Patch to SQL table when the item is created.

 

I'm assuming that PowerApps would have to do the following:

 

1. On select , LOOKUP the last serial number in the sequence.

2. Add +1 to the last number in the sequence.

3. Generate new number.

4. Upon completetion of other text fields, PATCH new record to DB .

 

Would the following formula work?

 

OnSelect(LookUp(Table1,  SerialNumber[Dropdown.Selected + 1 ] ))

v/r

Brendon

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Generate Inventory Number

Yes, you can do that. Take this example below:

ForumPost001.PNG

This app has a gallery with some values (with serial number and some names), and a couple of text boxes, one for the prefix of the new item, and one for the item of the new name. The value shown in the text box on the bottom (next serial) is given by the following formula (the data comes from a collection called 'coll2'):

Concatenate(
    TextInput1.Text,
    Text(
        Value(
            Mid(
                First(
                    Sort(
                        Filter(coll2, StartsWith(Serial, TextInput1.Text)),
                        Serial,
                        Descending)).Serial,
                Len(TextInput1.Text) + 1,
                100)) + 1,
        "000"))

Let's break it down in small parts. This filters all items for those that have the same prefix (in case you have different serial numbers for different departments, for example):

Filter(coll2, StartsWith(Serial, TextInput1.Text))

With the items filtered, we now sort them in decreasing order, from where we get the first element that has the last of the serial numbers:

First(
    Sort(
        Filter(coll2, StartsWith(Serial, TextInput1.Text)),
        Serial,
        Descending)).Serial

Now we need to remove the prefix to retrieve the actual counter, using the Mid function to start after the suffix until the end of the string (the last parameter, 100, is just a large enough value that it will take all characters after the prefix):

Mid(
    First(
        Sort(
            Filter(coll2, StartsWith(Serial, TextInput1.Text)),
            Serial,
            Descending)).Serial,
    Len(TextInput1.Text) + 1,
    100))

With that value, we can convert that into a number, and add 1, to retrieve the counter for the next serial number to be inserted:

Value(
    Mid(
        First(
            Sort(
                Filter(coll2, StartsWith(Serial, TextInput1.Text)),
                Serial,
                Descending)).Serial,
        Len(TextInput1.Text) + 1,
        100)) + 1

And finally we create the next serial number by concatenating the prefix with the new value, padded with the appropriate number of zeroes using the Text function.

 

You should be able to use this new serial number when creating a new item to add to your data source.

7 REPLIES 7
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Generate Inventory Number

Yes, you can do that. Take this example below:

ForumPost001.PNG

This app has a gallery with some values (with serial number and some names), and a couple of text boxes, one for the prefix of the new item, and one for the item of the new name. The value shown in the text box on the bottom (next serial) is given by the following formula (the data comes from a collection called 'coll2'):

Concatenate(
    TextInput1.Text,
    Text(
        Value(
            Mid(
                First(
                    Sort(
                        Filter(coll2, StartsWith(Serial, TextInput1.Text)),
                        Serial,
                        Descending)).Serial,
                Len(TextInput1.Text) + 1,
                100)) + 1,
        "000"))

Let's break it down in small parts. This filters all items for those that have the same prefix (in case you have different serial numbers for different departments, for example):

Filter(coll2, StartsWith(Serial, TextInput1.Text))

With the items filtered, we now sort them in decreasing order, from where we get the first element that has the last of the serial numbers:

First(
    Sort(
        Filter(coll2, StartsWith(Serial, TextInput1.Text)),
        Serial,
        Descending)).Serial

Now we need to remove the prefix to retrieve the actual counter, using the Mid function to start after the suffix until the end of the string (the last parameter, 100, is just a large enough value that it will take all characters after the prefix):

Mid(
    First(
        Sort(
            Filter(coll2, StartsWith(Serial, TextInput1.Text)),
            Serial,
            Descending)).Serial,
    Len(TextInput1.Text) + 1,
    100))

With that value, we can convert that into a number, and add 1, to retrieve the counter for the next serial number to be inserted:

Value(
    Mid(
        First(
            Sort(
                Filter(coll2, StartsWith(Serial, TextInput1.Text)),
                Serial,
                Descending)).Serial,
        Len(TextInput1.Text) + 1,
        100)) + 1

And finally we create the next serial number by concatenating the prefix with the new value, padded with the appropriate number of zeroes using the Text function.

 

You should be able to use this new serial number when creating a new item to add to your data source.

Re: Generate Inventory Number

Wow, I did not expect such a thorough tutorial. I'll give a shot in the morning. Thank you so much.

 

Re: Generate Inventory Number

Amazing. That worked! I will have to do some minor adjustments, such as combing addtional fields, in my other applications but this is a solid formula to work off. Thank you again

 

Brendon

Re: Generate Inventory Number

Was some part of this formula depricated? For some strange reason, the application cannot see the previous record and create the next serial number in succession. 

 

Concatenate(DrugLbl.Selected.Value,"-", LotNumber.Text,"-",Text(Value(Mid(First(Sort(Filter('[dbo].[Narcotics]', StartsWith(InventoryNumber, Text(DrugLbl.Selected.Value))), InventoryNumber, Descending)).InventoryNumber,Len(Text(DrugLbl.Selected.Value)) + 1, 100)) + 1, "[$-en-US]000" ))

 The InventoryNumber is the name of the column in my SQL table. It is currently set to NVARCHAR(50). I have done multiple data refreshes, but to no avail. 

JBrenner
Level: Powered On

Re: Generate Inventory Number

Can you please break this down a little more for me please? are you setting the textboxes to any default values or anything? i am simply trying to to the sample thing but my column name is "report#" and i just want it to increment from 1 +++ whenever something new is entered. so essentially a textbox or label that is set to the formula that is posted and retreives the next new number (i.e 1 2 3 4).

 

 

 

thank you for all of the useful guidance 

SamuelParkes
Level: Powered On

Re: Generate Inventory Number

Hi, this has helped me heaps, i have a question though. ive done something similair to the above but now if i go in and edit the entry it defaults back to a new autogenerate number. 

For example: 

GUN01 - water - 202 units

then i go to edit the line (say to change water to milk)

and it defaults to 

GUN02 - water - 202 units

wheras i need it to stay as GUN01 and let me still edit the entry any ideas on how to get around this??

Veronica
Level: Powered On

Re: Generate Inventory Number

Hi, anyone:

 

I used this to filter my gallery and it work. I could filter until show me the record from I want to increment the number,  but  how I capture the value of the column a want into a variable?  What I have is a table filter with one record and I need the value of one column of the return record.

 

I tried to assign the code to a text but It didn't work - What I am missing here?

 

First(

    Sort(

    Filter('[dbo].[Detalle]',StartsWith(vOfficeLocation,Oficina)&&(vTipo=Valortipo)),vNumeroPeticion,Descending).vNumeroPeticion) – this filter in the Item property of the gallery worked

 

Veronica