Data validation before posting in data base

[kartikfabiani]

Hi All,

I am fairly new with powerapp and app level coding and needed some help. I am trying to create a simple employee scheduling tool for my team. The idea is different Asst. managers will request for a employee to work on a time bound assignment and the managers will approve this request in the system.

I am facing the following problems.

1) What data base i should use. I have started with an excel data base linked to my one drive but reading posts here i am no longer sure whether thats the best idea

 2) I have to build a validation to check whether a employee is not actually booked on that day by someone else. So in the form when the user selects the from and to date it needs to go back to my data base and see whether he is booked by someone else and give a response to the user.

3) What is the best way to control access to be given. Requestor and approver need to have different screens   

[WarrenBelz]

Hi @kartikfabiani,

These are fairly broad questions, so I can only give broad answers

1. If you have a "Standard" E3 licence, the SharePoint should do the job for you as a data source
2. Power Apps has built-in functions User().FullName and User().Email that can be used to query your data for matches (or not)
3. You can also control who sees what with the functions above.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft · Accepted solution]

Hi @kartikfabiani ,

For your first question, based on my experience, I think the Excel table stored in OneDrive folder is not better choice. Firstly, Excel table is a slow data source, which would not have advantages in querying data from data source than other data source e.g. SharePoint, CDS, etc.

In addition, the Excel table is not a Delegable data source, in other words, it would be subject to Delegation limit -- could only process 2000 records at most locally in an app, could not delegate data process to Excel table from your app.

You could consider use SharePoint List or Common Data Service Entity instead of your original Excel table.

For your second question, I think the LookUp function could achieve your needs. Please consider set the OnChange property of the To DatePicker control in your Edit form to following:

Set(
    BookedRecord, 
    First(Filter(
           'Data Source', 
           EmployeeName = User().FullName,
           (FromDatePicker.SelectedDat && FromDatePicker.SelectedDate <= To) || (ToDatePicker.SelectedDate >= From && ToDatePicker.SelectedDate <= To)
    ))
);
If(
   !IsBlank(BookedRecord),
   Notify("You have been booked by " & BookedRecord.ManagerColumn & " from " & BookedRecord.From & " to " & BookedRecord.To & ", please choose another date range which is not conflict!", NotificationType.Error)
)

Note: "From" and "To" represents the columns in your data source, which is used to store the From date and To date.

For your third question, if you want the Approver and Requestor to navigate to different screen when they run this app, I think you could consider add a Approver table to store all available approvers in your app. The Approvers table may contains the following columns:

|Approver Name | Approver Email|
|--------------| --------------|
|Approver A  | approvera@email.com|
|Approver B  | approverb@email.com|
|Approver C  | approverc@email.com|

Within your canvas app, you could compare current sign in user with above Approvers table, check if he/she is a approver, if not, it would be requestor.

Add the Approvers table as data source in your app, then set the OnStart property of the App to following:

If(
   User().Email in ApproversTable.ApproverEmail,
   Navigate(ApproverScreen),
   Navigate(RequestorScreen)
)

when the user run your canvas app, the OnStart event would be fired, and check if the current sign in user is an approver or requestor.

Best regards,