Power Apps Exchange

Please login or click SIGN UP FOR FREE to create your PowerAppsUG account to join this user group.
Expand all | Collapse all

Patch and Update in SP List

  • 1.  Patch and Update in SP List

    Bronze Contributor
    Posted 16 days ago

    Hi All,

    I have a Leave Application, in which I am using two lists.

    List 1) LeaveRequests

    List 2) EmployeeLeaveBalance.

    When the user comes in my app, it requests for Leave, Let's say Annual Leave and fill out the information in the form and press on the submit button,
    the Submit button The First Patch Command  first patches in the LeaveRequests List of that User, which stores (User Email, LeaveType, Description, Manager Email, Start Date and End Date). and after that another Patch Command runs which, patches that user's Leave Balance in the EmployeeLeaveBalance List which Stores (User Email, Total Leaves, Annual Leave balance and Days Requested).

    Now, I want to save only 1 record for each employee and then if he/she requests again. So, I want to update the EmployeeLeaveBalance List and update their LeaveBalance Column Everytime which is Subtracted from their Days Requested.

    how to do this, Any help in this Regard.

    Thanks!!! 



    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------


  • 2.  RE: Patch and Update in SP List

    Bronze Contributor
    Posted 15 days ago
    @Warren Belz

    Any Help in this Regard?​

    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------



  • 3.  RE: Patch and Update in SP List

    Top Contributor
    Posted 15 days ago
    @Mustafa Hussain Mohsin,
    Please supply the Patch code you are using and what fields are the "key" unique identifiers in your lists​

    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------



  • 4.  RE: Patch and Update in SP List

    Bronze Contributor
    Posted 15 days ago
    Edited by Mustafa Hussain Mohsin 15 days ago

    Hi @Warren Belz

    Actually I have two lists

    1) LeaveRequests

    2) EmployeeLeaveBalance

    On submit button in a form I am patching the fields only in LeaveRequests list, which stores (User Email, LeaveType, Description, Manager Email, Start Date and End Date).this is working fine till now.

    But Now I want to have the ability of storing the leave total and leave balance calculation label values which I am doing it on that screen. I want to patch the labels text into the 2nd List of that user, but the the Annual Balance Label should patch the calculated value which is Annual Balance = Total Leaves - Days Requested  into that list.

    each user should have only 1 row and it should update whenever they request again, and if the Requested days which are greater than his/her balance so it should get a popup that days request should be lesser than the balance.

    My Code for now: 

    If(NewForm1.Mode=FormMode.New, If(DataCardValue1.Selected.Value = "Time Off", Patch( LeaveRequests, Defaults(LeaveRequests), { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, TimeOffDate: DataCardValue22.SelectedDate, TimeOffFrom: DataCardValue19.Selected, TimeOffTo: DataCardValue20.Selected, TotalTimeRequested: Label23.Text, LeaveStatus:"Pending" } ), DataCardValue1.Selected.Value = "Annual Leave", Patch( LeaveRequests, Defaults(LeaveRequests), { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ), DataCardValue1.Selected.Value = "Sick Leave", Patch( LeaveRequests, Defaults(LeaveRequests), { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ), DataCardValue1.Selected.Value = "Casual Leave", Patch( LeaveRequests, Defaults(LeaveRequests), { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ) ), NewForm1.Mode=FormMode.Edit, If(DataCardValue1.Selected.Value= "Time Off", Patch( LeaveRequests, { ID:LeaveGallery.Selected.ID }, { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, TimeOffDate: DataCardValue22.SelectedDate, TimeOffFrom: DataCardValue19.Selected, TimeOffTo: DataCardValue20.Selected, TotalTimeRequested: Label23.Text, LeaveStatus:"Pending" } ), DataCardValue1.Selected.Value="Annual Leave", Patch( LeaveRequests, { ID:LeaveGallery.Selected.ID }, { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ), DataCardValue1.Selected.Value="Sick Leave", Patch( LeaveRequests, { ID:LeaveGallery.Selected.ID }, { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ) , DataCardValue1.Selected.Value="Casual Leave", Patch( LeaveRequests, { ID:LeaveGallery.Selected.ID }, { LeaveID: CurrentUserID, User: DataCardValue6.Text, Requestor: MyUserEmail, LeaveTypee: DataCardValue1.Selected, Approver: { Claims: Concatenate( "i:0#.f|membership|", DataCardValue3.Selected.Email // Person email ), Department: "", DisplayName: "", Email: DataCardValue3.Selected.Email, // Person email JobTitle: "", Picture: "" }, EmployeeName: { Claims: Concatenate( "i:0#.f|membership|", MyUserEmail // Person email ), Department: "", DisplayName: "", Email: MyUserEmail, // Person email JobTitle: "", Picture: "" }, Description: DataCardValue2.Text, StartDate:StartDate.SelectedDate, EndDate:EndDate.SelectedDate, DaysCount:Label25.Text, LeaveStatus:"Pending" } ) ) ) ; Navigate(SucessScreen,None); ResetForm(NewForm1);

    Thanks!!! 



    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------


  • 5.  RE: Patch and Update in SP List

    Top Contributor
    Posted 14 days ago
    @Mustafa Hussain Mohsin,
    Can you please delete all the bits not relevant to the question - I have no chance of sorting through all of that.​

    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------



  • 6.  RE: Patch and Update in SP List

    Bronze Contributor
    Posted 13 days ago

    Hi @Warren Belz,

    Which bits should I delete??, actually sorry​, my code is kinda long. In this code I am actually updating and creating the new and existing records to be modified of each employeee.

    But I wanted to first create the record of an employee in EmployeeLeaveBalance List, and then if he requests again, so it should update the balance column of that employee after subtraction from the days requested. I have these columns in my 2nd List:

    ( 1)  EmployeeName  ( Person Column) \\ this Column is same as in the 1st List (LeaveRequests)
    2) Total Annual Leaves (Number Column)
    3) Annual Balance (Number Column)
    4) Total Casual Leaves (Number Column)
    5) Casual Balance (Number Column)
    6) Total Sick Leaves (Number Column)
    7) Sick Balance (Number Column)

    Thanks



    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------



  • 7.  RE: Patch and Update in SP List

    Top Contributor
    Posted 12 days ago
    @Mustafa Hussain Mohsin,
    I do not use person fields, so I may not be the right person to ask, but I would approach it by having two separate forms for new and edit and code them accordingly.

    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------



  • 8.  RE: Patch and Update in SP List

    Bronze Contributor
    Posted 12 days ago
    Hi @Warren Belz,

    Yes, you are absolutely correct.
    But actually, I need some help in the logic that when I patch in both the lists for the first time, it should create the record and then the user comes and requests for another than it should in the first list which is LeaveRequests but should​ update the Annual Balance column in the 2nd List.

    can you help with that?

    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------



  • 9.  RE: Patch and Update in SP List

    Top Contributor
    Posted 11 days ago
    Hi @Mustafa Hussain Mohsin,
    You need to test User().Email against existing records and if the lookup is blank, produce a new record otherwise ​edit the existing one.

    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------



  • 10.  RE: Patch and Update in SP List

    Bronze Contributor
    Posted 11 days ago

    Hello @Warren Belz,

    I have done some customizations and my Code is now Shorter, where  I am patching the values in the first list,

    When Creating New Record:

    ​​if Time Off selected, it will patch this Code in the first list LeaveRequests

    Patch 1 if  (Time Off selected)
    ​​else if Annual , Casual or Sick Leave selected, it will patch this Code in the first list LeaveRequests
    Patch 2 if  (When Annual, Casual,or Sick selected)
    Now, I want to run an another patch command which will Patch data into the 2nd List EmployeeLeaveBalance, and
    EmployeeLeaveBalance List  ScreenShot (There will be only 3 entries of Every User n this List, which will be for Annual, Casual and Sick, and the patch should update respectively according to the leave type selected when submitting record)
    if it's First entry of the Current Logged in user, then it should create the record of that employee in that list and patch the data into the list (Which Ever leave Type He/she Selected )  of that User, while patching the record it should subtract the DaysRequested with that Leave Balance. 
    and
    if there is an entry of the Current Logged in user, then it should update the Balance Field (Which Ever leave Type He/she Selected )  of that User, while patching the record it should subtract the DaysRequested with that Leave Balance. 
    I am stuck in the Patching thing, please help
    Any Help.?


    ------------------------------
    Mustafa Hussain Mohsin
    Associate Data Analyst
    ------------------------------