Filling a field/column on Sharepoint list automatically

[CanD]

Hello pa-community,

i have a question regarding my new app. I have connected my app with a sharepoint list. So with the app, users can add rows into the list over a formular. Everything works for now. So one column should have to be filled automatically. Everytime the user creats a new row, it should create a string/integer in the first column in that row (to be unique like an primary key in the database sector). I already have an column for that it called ServiceID. At the moment it is of type number but i can switch it to string/text (maybe i should switch it, it would be much easier). The input to the column ServiceID should be in that form if possible: YYYYMMDD[unique number] . For example if some creates a row today it should add -> 20201126001 for example.

I could not find a solution to that issue....

I hope someone can help me.

Best regards,

CanD

[CanD]

I tought about to take the last row of my list, take the ServiceID and add 1 to do it coninues. But the problem is, that we can not take the number out of the String to add 1 or am i wrong? If its not possible with date in front of the unique number, do someone have an solution without the date and maybe using a number/integer ?

Thank you.

[v-bofeng-msft · Accepted solution]

Hi @CanD :

Do you want to get something like this?

If so,I've made a test for your reference:

1\My data source-MySPList1

ServiceID-Number

2\Add an edti form-Form1

DataSource

MySPList1

DefaultMode

FormMode.New

3\Set The text input control(DataCardValue2)'s Default property to

If(
    CountRows(MySPList1) = 0,
    Year(Now()) & Text(
        Month(Now()),
        "[$-en]00"
    ) & Text(
        Day(Now()),
        "[$-en]00"
    ) & "001",
    Year(Now()) & Text(
        Month(Now()),
        "[$-en]00"
    ) & Text(
        Day(Now()),
        "[$-en]00"
    ) & Text(
        Value(
            Right(
                Text(
                    Last(
                        Sort(
                            MySPList1,
                            ServiceID,
                            Ascending
                        )
                    ).ServiceID
                ),
                3
            )
        ) + 1,
        "[$-en]000"
    )
)

4\Add a button control and set it's OnSelect property to:

SubmitForm(Form1);NewForm(Form1)

Best Regards,

Bof

[CanD]

@v-bofeng-msft 

YEEEES it worked. Thank you so so so much!!!

One small question, is it possible to delete the commas from the list? In the app it shows the field without commas, but on sharepoint list there are the same field with comma. Your gif image shows it too. If its not possible, then no problem i am already good with solution.

Thanks again.

Best regards,

CanD

[v-bofeng-msft]

Hi @CanD :

Because ServiceID is a Number column, there will be no comma if it is a "Single Line of Text" column.

Best Regards,

Bof

[CanD]

Hey @v-bofeng-msft , again thank you so mutch.

Can i add one small question regarding the automatic fill. What happens if 2/3/4/... users start the app and starting to create a form. What will happen with the ServiceID field? Will it show for everybody the same serviceID? Because the list size changes itself after clicking the button. But if one user starts the form and starts to type his name for example and at this time another one starts the app with creating a form... Hope you understand my question..

Thank you.

Best regards,

CanD

[v-bofeng-msft]

Hi @CanD :

Your concerns are likely to happen. I suggest you create a button that allows users to refresh the data source before submitting the form.

Add a button and set it's OnSelelct property to:

Refresh(YourDataSource)

Best Regards,

Bof

[CanD]

Hi @v-bofeng-msft  ,

sry i am answering late. Could not test something. It would be really nice if there where an function to reservate an id, but i think it would be too difficult. I will add the function you mentioned to my App.

Thank you really much.

Best regards,

CanD

[CanD]

Hi @v-bofeng-msft 

i have a small question regarding this topic. First of all thanks again for your help regarding the automatic fill. My question: what happens if i add 999 rows and what happens when it turns to 1000? I have saw that in your it is on "20201127006" for example. I have created some more rows and now i am at "20210106013" . So first it were two zeros and now there is only one. I think you understand what i want to ask. Will it count above 999 normaly or will i get a mistake? I could not test it on my own because i do not have access to the lists at the moment..

Is it possible to start after the 999th row to start the count new if a new day starts?

I hope you can help me with that.

Thank you.

[Anonymous]

a