Defualt value for Edit Form with leading zeros

(0) Share

Report

Posted on by burliad

183

Hi All,

On my apps edit form I am trying to set the default value to the current 2 digit year, a dash, and the id (DataCardValue12) with leading zeros. Would like only three digits total after the dash. I get the year and the dash but not the id field with the leading zeros.

Examples of how it should look:

20-001

20-025

20-234

Here is what I have tried

If(EditForm2.Mode = FormMode.New,Text( Now(), "[$-en-US]yy") & "-" & Text(DataCardValue12.Text,"[$-en-US]000"),Parent.Default)

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@burliad

Assuming your DataCardValue12 is going to be a numeric value, consider the following formula:

If(EditForm2.Mode = FormMode.New,Text( Now(), "[$-en-US]yy") & "-" & Text(Value(DataCardValue12.Text),"[$-en-US]000"),Parent.Default)

I hope this is helpful for you.

Was this reply helpful? Yes No
burliad 183 on at

Like (0)

Report

Hi Randy,

Thanks for your reply. This is not working but think I'm getting closer. Think the problem is that the id is not created until the user hits the submit button for the form. I tried using that formula on the OnSuccess of the form but didn't work either. Basically looking for the app to create a treat number based on the year and id. In MS Access I would use DMax to lookup the max value from the table then add 1 to it. Not sure if there is anything like this for PowerApps. I am still new to this platform.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@burliad

Yes, this is true, if you are creating a record there will be no ID.

Now, you can use a formula such as this to get the last ID:

First(SortByColumns(yourList, "ID", Descending)).ID

This will give you the very last ID, which you can add 1 to - HOWEVER, you cannot guarantee that it will stay that way. Meaning that if someone else adds a record while you have yet to submit your record, there will be duplicate ID's. In fact, this is pretty much the case with any incremental value approach to PowerApps based on an existing number in the datasource.

To guarantee, you would need to refresh prior to submitting (a costly and prone to problem solution), or consider another way to uniquely ID something.

If there is no risk of duplicates based on the scenario I outline above, then you need not worry, but if that does exist, then consider alternatives.

Was this reply helpful? Yes No
burliad 183 on at

Like (0)

Report

Understood, is there a way to save the form and force new id generation before submitting?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@burliad

No, submitting IS saving the form.

You have one of 3 choices:

1) Devise your unique ID strategy off of something else (unique time, GUID, etc)

2) Assume the ID you have based on the previous formula for determining the last ID is accurate and no one else has submitted a new record (this is not a PowerApp function as much as it is knowing the deployment strategy of your App)

3) Patch the record after you submit (you will not be able to truly display the value you want during record creation).

To do this you can alter your formula in the OnSuccess action of the edit form to the following:

UpdateIf(yourList, ID=EditForm2.LastSubmit.ID, { yourColumn: Text(Now(), "yy") & "-" & Text(EditDoem2.LastSubmit.ID, "000") } )

#3 should give you what you want, but has the disadvantage of not being able to show the user that value until after submitted.

Was this reply helpful? Yes No
burliad 183 on at

Like (0)

Report
Thank you, went with your last solution to update the table using the OnSuccess. It seems to be working. I am not familiar with the first part of this code. Just want to make sure it will not change treat numbers in the forms edit mode.
UpdateIf(tbl_treats, ID=EditForm2.LastSubmit.ID,

Full Code that I used:
UpdateIf(tbl_treats, ID=EditForm2.LastSubmit.ID, { treat_number: Text(Now(), "[$-en-US]yy") & "-" & Text(EditForm2.LastSubmit.ID, "[$-en-US]000") } )

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@burliad

Yes the Formula is correct.

The UpdateIf in the first part of the Formula (actually, it is really what the whole formula is) will update any record that meets the criteria.

Even if you submit the form again, after the record is created, it will still update the treat_number, but it will update it with the same value - which is fine.

If you want to simply avoid that extra update, you could change your Formula to the following:

If(IsBlank(treat_number, UpdateIf(tbl_treats, ID=EditForm2.LastSubmit.ID, { treat_number: Text(Now(), "[$-en-US]yy") & "-" & Text(EditForm2.LastSubmit.ID, "[$-en-US]000") } ) )

Was this reply helpful? Yes No
burliad 183 on at

Like (1)

Report

Got it, thank you again so much!

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@burliad

No problem at all. Happy to help.

Was this reply helpful? Yes No