cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CanD
Helper III
Helper III

Filling a field/column on Sharepoint list automatically

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

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @CanD :

Do you want to get something like this?

4.gif

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"
    )
)

1.JPG

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

SubmitForm(Form1);NewForm(Form1)

Best Regards,

Bof

View solution in original post

8 REPLIES 8
CanD
Helper III
Helper III

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.

Hi @CanD :

Do you want to get something like this?

4.gif

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"
    )
)

1.JPG

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

SubmitForm(Form1);NewForm(Form1)

Best Regards,

Bof

View solution in original post

@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

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

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

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

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

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.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (17,419)