cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Data validation before posting in data base

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   

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User III
Super User III

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.

Highlighted
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,898)