Need help incrementing numerical text value in dropdown field

[DJanssen]

Hello all,

I currently have a collection named DocumentNumberCollection that contains the columns: HasAttachment (yes/no) and SequentialNumber (text). I also have a dropdown that contains the values of the SequentialNumber only if the HasAttachment is true. However the dropdown should also include 1 extra entry that is the largest numerical value and increments it. So for example if the dropdown contains: 03 and 05 the extra entry should be 06. 

Currently the dropdown has the following formula:

Filter(DocumentNumberCollection, HasAttachment = true).SequentialNumber

I tried the following formula but that didn't work:

With(
    {
        maxNumber: Max(
            Filter(DocumentNumberCollection, HasAttachment = true),
            Value(SequentialNumber)
        ).Value
    },
    AddColumns(
        Filter(DocumentNumberCollection, HasAttachment = true),
        "NumberAsText", SequentialNumber
    ) & [{NumberAsText: Text(maxNumber + 1, "00")}]
)

If anyone has any ideas I'd love to hear them.

Best wishes

[Pro_User_365]

Hi  @DJanssen, 

Your approach is close, but there's a small issue with how the Max function is used. The Max function should be applied to a table of numbers, not directly on the filtered collection. Additionally, the Value function should be used to convert the SequentialNumber to a number before finding the maximum. Here’s a revised version of your formula:

With(
    {
        maxNumber: Max(
            ForAll(
                Filter(DocumentNumberCollection, HasAttachment = true),
                Value(SequentialNumber)
            )
        )
    },
    AddColumns(
        Filter(DocumentNumberCollection, HasAttachment = true),
        "NumberAsText", SequentialNumber
    ) & [{NumberAsText: Text(maxNumber + 1, "00")}]
)

This formula uses ForAll to convert each SequentialNumber to a value before applying Max. It then creates a new record with the incremented largest number and appends it to the filtered collection.

Note: After receiving the answer, if you think it's helpful, please consider accepting it. 😊 Regards! Ali Shahzad

[DJanssen]

Hey @Pro_User_365  Thank you so much for your quick reply! I tried out your solution but sadly it gives me the error: "Expected Table value" for the whole formula. I have specific errors at ForAll: "Expected number. We expect a number at this point in the formula" and the AddColumns: "Invalid argument type. Expecting one of the following: Text, Number, Decimal, Date, Time, DatetimeNoTimeZone, Boolean, OptionSetValue, ViewValue, UntypedObject". 
If it helps this is inside my DocumentNumberCollection collection: 

And this is how it looks in the Sharepoint List where I'm retrieving the information from:

[Pro_User_365]

 @DJanssen, 

The error you're experiencing with the "Expected Table value" message indicates that the Max function is not receiving a table of numbers as it expects. The problem with ForAll is that it returns a table of records, and Max needs a single column table of numbers instead. Let's correct the formula by ensuring that we provide Max with a proper table of numbers.

Here is the adjusted formula:

With(
    {
        maxNum: Max(
            Filter(DocumentNumberCollection, HasAttachment = true),
            Value(SequentialNumber)
        )
    },
    AddColumns(
        Filter(DocumentNumberCollection, HasAttachment = true),
        "NumberAsText", SequentialNumber
    ) & [{NumberAsText: Text(maxNum + 1, "00")}]
)

In this corrected version, Filter returns a table with only the rows where HasAttachment is true. Then Max is used directly on this table but with the Value function to ensure that SequentialNumber is treated as a number. The rest of the formula appends a new record with the incremented largest number to the filtered collection. Remember to replace SequentialNumber with the actual field name if it's different in your collection.

[DJanssen]

Hello @Pro_User_365  I appreciate you so much! Thank you also for explaining every step. I'm pretty new to Power Apps so it really helps in understanding the logic. 

Sadly the console keeps complaining with the updated code you provided. The error might be in the last part of Addcolumns because if I remove the "&[....] the console doesn't log an error:

and with the &[...]

I looked numerous times at the SequentialNumber naming but to me the column name, or field name how you call it of my collection really is named SequentialNumber: 

The name of the dropdown is called HasAttachmentDropdown, but that one is not needed in the formula right?

And just to make sure, the formula you provided I'm currently using it on the dropdown property "Items". Could I be using the wrong property maybe?

[Prabhakar_S]

Hi @DJanssen ,

Give this a try,

With(
{ 
maxNumber: Max(
Filter(DocumentNumberCollection, HasAttachment = true),
Value(SequentialNumber)
).Value
},
Distinct(
AddColumns(
Filter(DocumentNumberCollection, HasAttachment = true),
"NumberAsText", SequentialNumber
),
NumberAsText
) & [{NumberAsText: Text(maxNumber + 1, "00")}]
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[Pro_User_365 · Accepted solution]

Hi  @DJanssen, 

The error you're encountering in the console when adding the extra entry with the incremented SequentialNumber suggests that there might be a syntax or logical error in the formula. The AddColumns function works correctly by itself, but combining it with the appended record seems to be where the issue arises.

With(
    { maxNum: Max(ForAll(Filter(DocumentNumberCollection, HasAttachment = true), Value(SequentialNumber)), Result) },
    AddColumns(
        Filter(DocumentNumberCollection, HasAttachment = true),
        "NumberAsText", SequentialNumber
    ) & [{NumberAsText: Text(maxNum + 1, "00")}]
)

Make sure that you use this formula in the Items property of the dropdown HasAttachmentDropdown. This formula does the following:

1. Uses ForAll to iterate over the filtered DocumentNumberCollection where HasAttachment is true, converting SequentialNumber to a value.
2. Applies Max to the Result property of the records returned by ForAll.
3. Adds columns to the filtered collection, keeping the SequentialNumber as text.
4. Appends a new record with the next sequential number, incremented by 1 and formatted as two digits.

   If the error persists, double-check the data types of your SequentialNumber field to ensure they are strings and can be converted to values correctly. Also, ensure there are no hidden characters or spaces in the field names that could be causing issues with the formula.

[DJanssen]

@Prabhakar_S  Hey thank you for having a look with me too. Appreciate it! After trying your solution the following errors appear: