ID not generated properly : Too much duplication & how can you grant permission to selected users only

[Tapesh]

Hi all,

I started to work on application, the thing i started to note now is, 

my power apps application is not generating new Id for every entry or form being updated, where as it is creating alot of duplications. I want the ID to be Unique and only One for each Item being updated 

Please see the Screen shot. If you can suggest will be helpful. Thanks 

Also I want to know that how can i only allow user to see the list and not edit or do something else, but he should be able to .

update and edit the items from power apps not sharepoint. Is this possible ?

[eka24]

I gave a similar solution on this platform. The idea is to check if the Number does not exist before it allows you to Submit the form or Patch the record. Knowing your Submit Formula would help but even before that follow this structure :
If(CountRows(Filter(TableName, Value(ID)=Value(DataCardValue.Text))) > 0,Notify("Number Exist",Notification.Error),SubmitForm(Form1)))
Thus, if exist give warning, else allow submission.

If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution

[v-yutliu-msft]

Hi @Tapesh ,

Q1:

Do you mean that many duplicated ID value is updated by using powerapps?
Could you tell me which field represents  ID your list? Customer ID?

In sharepoint, there's already an unique field that will never be updated with duplicated value, which is called ID.

It will not display by default. You need to choose it to display.

What's more, if you want to make one field with unique value, you could set it:

Q2:

Do you want to make user can only edit/create data from powerapps and only view data in sharepoint list?
Yes, this is possible.

Firstly, you can not set user's permission about the list because the site permission will work not only in sharepoint but also in powerapps.

For example, if you set one user's permission to be unable to edit, then he will be unable to edit records both in sharepoint and powerapps.

So I suggest you consider how user edit data in sharepoint.

Two places:

1)quick edit

2)sharepoint form

To disable the first one, you could set it in list settings->Advanced settings.

 To disable the second one, I suggest you create a sharepoint custom form.

Then set SharePointIntegration's OnNew:

ViewForm(SharePointForm1)

OnEdit: 

ViewForm(SharePointForm1)

OnEdit: 

ViewForm(SharePointForm1)

delete the formulas in OnSave and OnCancel.

Then save and publish this form.

You will be unable to edit/save data in sharepoint list.

Best regards,

[Tapesh]

Thanks @v-yutliu-msft 

 The Sp List settings solutions looks good and accepeted as well, but the first is still not clear, i know that as you said Sharepoint has its unique ID filed, but that is not reset to 1 if the records are empty or null, this always keeps on Increasing even if recordsa re created, deleted or updated, which is why im using the cutsomerID column which is custom generated column. 

now the thing and issue here is, when the user logs in with different account, the customerID is generated as 1 if he is first time user, where as in the List there are already 4 records, which in result then should be 5, then 6 and so on, where as it is not. 

the cutomerID column in my power apps, generates the new ID for every new user/customer. 

Say im one User if i submit form that will be 1, then 2, and 3 and 4 and so on,

now say you are second user, 

even then the app generates the new ID which will be 1, and when you submit next time will be 2, and 3 and 4 and so on, 

where as the result here should be, 

1, 2, 3 , 4 , 5 ,6 no matter what, or how many users comes and go, 

also if they suppose updates 1 then id should be not changed, it should remain 1 the data can be changed. 

hope you got the thing now. 

have any solutions to this? 

[v-yutliu-msft]

Hi @Tapesh ，

Yes, the sharepoint system ID field indeed has the disadvantages that you said.

Could you show me

1)the date type of the customer ID field?

2)your settings of the custom generated column?

3)the updated formula of the custom generated column?

Maybe you just need to make a little change on your original settings.

What's more, have you set the field like I said to "enforce unique value"?
This could make this field value unique.

 If it is a calculated field or a generated automatically field, I'm afraid it's not supported to control it in powerapps. This kind of field is read-only in powerapps.

You could only change its settings in sharepoint.

If it is a field that could could be updated in powerapps, you could use this kind of formula to get the Id value that you should update with:

If(IsEmpty(LookUp(list,Employee.Email=User().Email)),
   Last(list).customerID+1,
   LookUp(list,Employee.Email=User().Email,customerID)+1
  )

Best regards,

[Tapesh]

hi @v-yutliu-msft 

Please see the details below: 

filed data type is: Text

Formula on CustomerId  Id: 

if(Edit_Emp_Form.Mode = New, First(
   Sort(
      'Petty Cash',
      CustomerID,
      Descending
   )
)
.CustomerID+ 1, ApplicationRecordsGallery.Selected.CustomerID)

Settings are default are i@m using the power apps customize mode. 

[v-yutliu-msft]

Hi @Tapesh ,

Is 'Petty Cash' the sharepoint list name?

Please set the customer ID field textinput's Default:

If(Edit_Emp_Form.Mode = New, First(
   Sort(
      'Petty Cash',
      CustomerID,
      Descending
   )
)
.CustomerID+ 1, ApplicationRecordsGallery.Selected.CustomerID)

 I've made a similar test.

In my test, 'Petty Cash' is the list name, not the view name. If you set the textinput's default to this formula, the generated customerid will not be duplicated.

Best regards,

[Tapesh]

Yes i have the same formula on my default, still having that issue i dont know why? is it something that, while im giving access to the people of my sharepoint list, they also become the member, which is why i guess this may be result. 

Any idea? 

@v-yutliu-msft 

[v-yutliu-msft · Accepted solution]

Hi @Tapesh ,

Yes, that maybe the reason.

If multiple people are editing the list at the same time, it will lead to data duplicated.

Best regards,