Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / PowerApp -- create , u...
Customer experience | Sales, Customer Insights,...
Suggested answer

PowerApp -- create , update,delete,

(0) ShareShare
ReportReport
Posted on by Sumedha 142

Hii,

I have created employee vise data in another table on button click on home page . My issue is occur it create employee but many records. 

1 emp - amount = 3000

1 emp - amount = 4000

when i select 2 record it create 2 records one is 3000 and other is 7000. but i want only 1 record will be created which is 7000 (amount will be added)

  • Sumedha Profile Picture
    Sumedha 142 on at
    RE: PowerApp -- create , update,delete,

    Hii,

    Below mention is the code i have delete (code is removed) now 1 emp has 4 records amount will be calculated as per i mention .

    function expenseclaim_post(selectedIds)

    {

    'use strict';

    var strIds = selectedIds.toString();

    //alert(strIds);

    //for multiple values

    var res = strIds.split(",");

    //alert(res);

    //alert("hii");

    for (var indxIds = 0; indxIds < res.length; indxIds++) {

    createRecords(res[indxIds]);

    }

    Xrm.Navigation.openAlertDialog("Expense Approved");

    }

    function createRecords(selectedIds)

    {

    //var lineid=selectedIds;

    var lineid=selectedIds;

     // var formContext          = selectedControlSelectedItemIds;

    var UserId = Xrm.Page.context.getUserId();

    var varpat= UserId.toLowerCase();

    var currentuserid =  varpat.replace(/[{}]/g, "");

     //  var id             =  formContext.data.entity.getId();

    //    var headerid       =  id.toLowerCase().replace(/[{}]/g, "");

    // alert(headerid);

    var today = new Date();

    var UserName = Xrm.Page.context.getUserName();

    //debugger;

    var totalamount = 0;

    var amountincurrency = 0;

    var ExpensePayhead = 'cbbaad62-1d35-ed11-9db1-0022480dadc2';

    //Fetch all employees from employee master

    var req1 = new XMLHttpRequest();

    req1.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/amy_expenseclaimlines?$select=amy_aed,amy_amountincurrency,amy_approvalstatus,_amy_currency_value,amy_customerattendees,amy_date,_amy_employeeid_value,_amy_expenseclaim_value,amy_expenseclaimlineid,amy_expensetype,_amy_linemanager_value,amy_notes,_amy_period_value&$filter=amy_expenseclaimlineid eq "+lineid+"", false);

    req1.setRequestHeader("OData-MaxVersion", "4.0");

    req1.setRequestHeader("OData-Version", "4.0");

    req1.setRequestHeader("Accept", "application/json");

    req1.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req1.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

    req1.onreadystatechange = function() {

       if (this.readyState === 4) {

           req1.onreadystatechange = null;

           if (this.status === 200) {

               var results = JSON.parse(this.response);

               for (var i = 0; i < results.value.length; i++) {

                   var amy_aed = results.value[i]["amy_aed"];

                   var amy_aed_formatted = results.value[i]["amy_aed@OData.Community.Display.V1.FormattedValue"];

                   var amy_amountincurrency = results.value[i]["amy_amountincurrency"];

                   var amy_amountincurrency_formatted = results.value[i]["amy_amountincurrency@OData.Community.Display.V1.FormattedValue"];

                   var amy_approvalstatus = results.value[i]["amy_approvalstatus"];

                   var amy_approvalstatus_formatted = results.value[i]["amy_approvalstatus@OData.Community.Display.V1.FormattedValue"];

                   var _amy_currency_value = results.value[i]["_amy_currency_value"];

                   var _amy_currency_value_formatted = results.value[i]["_amy_currency_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_currency_value_lookuplogicalname = results.value[i]["_amy_currency_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_customerattendees = results.value[i]["amy_customerattendees"];

                   var amy_date = results.value[i]["amy_date"];

                   var _amy_employeeid_value = results.value[i]["_amy_employeeid_value"];

                   var _amy_employeeid_value_formatted = results.value[i]["_amy_employeeid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_employeeid_value_lookuplogicalname = results.value[i]["_amy_employeeid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var _amy_expenseclaim_value = results.value[i]["_amy_expenseclaim_value"];

                   var _amy_expenseclaim_value_formatted = results.value[i]["_amy_expenseclaim_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_expenseclaim_value_lookuplogicalname = results.value[i]["_amy_expenseclaim_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_expenseclaimlineid = results.value[i]["amy_expenseclaimlineid"];

                   var amy_expensetype = results.value[i]["amy_expensetype"];

                   var amy_expensetype_formatted = results.value[i]["amy_expensetype@OData.Community.Display.V1.FormattedValue"];

                   var _amy_linemanager_value = results.value[i]["_amy_linemanager_value"];

                   var _amy_linemanager_value_formatted = results.value[i]["_amy_linemanager_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_linemanager_value_lookuplogicalname = results.value[i]["_amy_linemanager_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_notes = results.value[i]["amy_notes"];

                   var _amy_period_value = results.value[i]["_amy_period_value"];

                   var _amy_period_value_formatted = results.value[i]["_amy_period_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_period_value_lookuplogicalname = results.value[i]["_amy_period_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

    //alert(amy_aed);

    //Update Posted in Expense Claim Line

    var entity3 = {};

    entity3.amy_Posted = true;

    var req7 = new XMLHttpRequest();

    req7.open("POST", Xrm.Page.context.getClientUrl() + "/XRMServices/2011/OrganizationData.svc/amy_ExpenseClaimLineSet(guid'"+lineid+"')", false);

    req7.setRequestHeader("Accept", "application/json");

    req7.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req7.setRequestHeader("X-HTTP-Method", "MERGE");

    req7.onreadystatechange = function() {

       if (this.readyState === 4) {

           this.onreadystatechange = null;

           if (this.status === 204 || this.status === 1223) {

               //Success - No Return Data - Do Something

    //alert("Post Updated");

    //Create data in TempExpense Table

    var entity1 = {};

    entity1.amy_EmployeeId = {

       Id: _amy_employeeid_value,

       LogicalName: "amy_employeemaster"

    };

    entity1.amy_ExpenseType = {

       Value: 100000000

    };

    entity1.amy_Period = {

       Id: _amy_period_value ,

       LogicalName: "amy_periodline"

    };

    entity1.amy_AED = parseFloat(amy_aed).toFixed(2);

    var req8 = new XMLHttpRequest();

    req8.open("POST", encodeURI(Xrm.Page.context.getClientUrl() + "/XRMServices/2011/OrganizationData.svc/amy_TempExpenseTableSet"), false);

    req8.setRequestHeader("Accept", "application/json");

    req8.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req8.onreadystatechange = function() {

       if (this.readyState === 4) {

           this.onreadystatechange = null;

           if (this.status === 201) {

               var result = JSON.parse(this.responseText).d;

               var newEntityId = result.amy_TempExpenseTableId;

    //alert("create");

           } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req8.send(JSON.stringify(entity1));

      } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req7.send(JSON.stringify(entity3));

    }

           } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req1.send();

    //Fetch all employees from employee master

    var req10 = new XMLHttpRequest();

    req10.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/amy_employeemasters?$select=_amy_departmentcode_value,amy_employeecode,amy_employeemasterid,amy_gender,amy_jafzacode,amy_maritalstatus,amy_name,amy_status,_amy_user_value", true);

    req10.setRequestHeader("OData-MaxVersion", "4.0");

    req10.setRequestHeader("OData-Version", "4.0");

    req10.setRequestHeader("Accept", "application/json");

    req10.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req10.onreadystatechange = function() {

       if (this.readyState === 4) {

           req10.onreadystatechange = null;

           if (this.status === 200) {

               var results = JSON.parse(this.response);

               for (var i = 0; i < results.value.length; i++) {

                   var _amy_departmentcode_value1 = results.value[i]["_amy_departmentcode_value"];

                   var amy_employeecode1 = results.value[i]["amy_employeecode"];

                   var amy_employeemasterid1 = results.value[i]["amy_employeemasterid"];

                   var amy_gender1 = results.value[i]["amy_gender"];

                   var amy_jafzacode1 = results.value[i]["amy_jafzacode"];

                   var amy_maritalstatus1 = results.value[i]["amy_maritalstatus"];

                   var amy_name1 = results.value[i]["amy_name"];

                   var amy_status1 = results.value[i]["amy_status"];

                   var _amy_user_value1 = results.value[i]["_amy_user_value"];

    alert(amy_name1);

    // Fetch TempExpenseTable

    var req9 = new XMLHttpRequest();

    req9.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/amy_tempexpensetables?$select=amy_aed,amy_amountincurrency,_amy_currency_value,amy_customerattendees,_amy_employeeid_value,amy_expensetype,_amy_period_value,amy_posted,amy_tempexpenseid,amy_tempexpensetableid&$filter=_amy_employeeid_value eq "+amy_employeemasterid1+" ", false);

    req9.setRequestHeader("OData-MaxVersion", "4.0");

    req9.setRequestHeader("OData-Version", "4.0");

    req9.setRequestHeader("Accept", "application/json");

    req9.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req9.onreadystatechange = function() {

       if (this.readyState === 4) {

           req9.onreadystatechange = null;

           if (this.status === 200) {

               var results = JSON.parse(this.response);

               for (var i = 0; i < results.value.length; i++) {

                   var amy_aed1 = results.value[i]["amy_aed"];

                   var amy_amountincurrency1 = results.value[i]["amy_amountincurrency"];

                   var _amy_currency_value1 = results.value[i]["_amy_currency_value"];

                   var amy_customerattendees1 = results.value[i]["amy_customerattendees"];

                   var _amy_employeeid_value2 = results.value[i]["_amy_employeeid_value"];

                   var amy_expensetype1 = results.value[i]["amy_expensetype"];

                   var _amy_period_value1= results.value[i]["_amy_period_value"];

                   var amy_posted1 = results.value[i]["amy_posted"];

                   var amy_tempexpenseid = results.value[i]["amy_tempexpenseid"];

                   var amy_tempexpensetableid = results.value[i]["amy_tempexpensetableid"];

    //alert(_amy_employeeid_value2);

    totalamount = totalamount +amy_aed1;

    // Fetch Head Definition

    var req5 = new XMLHttpRequest();

    req5.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/amy_headdefinitions?$select=amy_amount,amy_amountinaed,_amy_currency_value,amy_description,amy_effectivefrom,amy_effectiveto,_amy_employeeid_value,amy_glaccount,amy_headdefinitionid,amy_headdefinitionsid,_amy_payheadid_value,_amy_payrollworker_value,amy_remark,amy_status&$filter=_amy_employeeid_value eq "+_amy_employeeid_value2 +" and  _amy_payheadid_value eq "+ExpensePayhead +" and  amy_status eq 100000000", false);

    req5.setRequestHeader("OData-MaxVersion", "4.0");

    req5.setRequestHeader("OData-Version", "4.0");

    req5.setRequestHeader("Accept", "application/json");

    req5.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req5.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

    req5.onreadystatechange = function() {

       if (this.readyState === 4) {

           req5.onreadystatechange = null;

           if (this.status === 200) {

               var results = JSON.parse(this.response);

               for (var i = 0; i < results.value.length; i++) {

                   var amy_amount = results.value[i]["amy_amount"];

                   var amy_amount_formatted = results.value[i]["amy_amount@OData.Community.Display.V1.FormattedValue"];

                   var amy_amountinaed = results.value[i]["amy_amountinaed"];

                   var amy_amountinaed_formatted = results.value[i]["amy_amountinaed@OData.Community.Display.V1.FormattedValue"];

                   var _amy_currency_value = results.value[i]["_amy_currency_value"];

                   var _amy_currency_value_formatted = results.value[i]["_amy_currency_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_currency_value_lookuplogicalname = results.value[i]["_amy_currency_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_description = results.value[i]["amy_description"];

                   var amy_effectivefrom = results.value[i]["amy_effectivefrom"];

                   var amy_effectiveto = results.value[i]["amy_effectiveto"];

                   var _amy_employeeid_value4 = results.value[i]["_amy_employeeid_value"];

                   var _amy_employeeid_value_formatted4 = results.value[i]["_amy_employeeid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_employeeid_value_lookuplogicalname4 = results.value[i]["_amy_employeeid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_glaccount = results.value[i]["amy_glaccount"];

                   var amy_headdefinitionid = results.value[i]["amy_headdefinitionid"];

                   var amy_headdefinitionsid = results.value[i]["amy_headdefinitionsid"];

                   var _amy_payheadid_value = results.value[i]["_amy_payheadid_value"];

                   var _amy_payheadid_value_formatted = results.value[i]["_amy_payheadid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_payheadid_value_lookuplogicalname = results.value[i]["_amy_payheadid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var _amy_payrollworker_value = results.value[i]["_amy_payrollworker_value"];

                   var _amy_payrollworker_value_formatted = results.value[i]["_amy_payrollworker_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_payrollworker_value_lookuplogicalname = results.value[i]["_amy_payrollworker_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_remark = results.value[i]["amy_remark"];

                   var amy_status = results.value[i]["amy_status"];

                   var amy_status_formatted = results.value[i]["amy_status@OData.Community.Display.V1.FormattedValue"];

    alert(_amy_payheadid_value_formatted);

    }

    // Fetch PayTransTable(Payroll Detail)

    var req2 = new XMLHttpRequest();

    req2.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/amy_paytranstables?$select=_amy_employeeid_value,amy_fromdate,_amy_payrollworker_value,amy_paytransid,amy_paytranstableid,_amy_periodid_value,_amy_periodlineid_value,amy_posted,amy_posteddate,amy_status,_amy_structurecode_value,amy_submitteddate,amy_todate&$filter=_amy_employeeid_value eq "+_amy_employeeid_value4+" and  _amy_periodlineid_value eq "+_amy_period_value1+" ", false);

    req2.setRequestHeader("OData-MaxVersion", "4.0");

    req2.setRequestHeader("OData-Version", "4.0");

    req2.setRequestHeader("Accept", "application/json");

    req2.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req2.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

    req2.onreadystatechange = function() {

       if (this.readyState === 4) {

           req2.onreadystatechange = null;

           if (this.status === 200) {

               var results = JSON.parse(this.response);

               for (var i = 0; i < results.value.length; i++) {

                   var _amy_employeeid_value5 = results.value[i]["_amy_employeeid_value"];

                   var _amy_employeeid_value_formatted5 = results.value[i]["_amy_employeeid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_employeeid_value_lookuplogicalname5 = results.value[i]["_amy_employeeid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_fromdate = results.value[i]["amy_fromdate"];

                   var _amy_payrollworker_value = results.value[i]["_amy_payrollworker_value"];

                   var _amy_payrollworker_value_formatted = results.value[i]["_amy_payrollworker_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_payrollworker_value_lookuplogicalname = results.value[i]["_amy_payrollworker_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_paytransid = results.value[i]["amy_paytransid"];

                   var amy_paytranstableid = results.value[i]["amy_paytranstableid"];

                   var _amy_periodid_value = results.value[i]["_amy_periodid_value"];

                   var _amy_periodid_value_formatted = results.value[i]["_amy_periodid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_periodid_value_lookuplogicalname = results.value[i]["_amy_periodid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var _amy_periodlineid_value = results.value[i]["_amy_periodlineid_value"];

                   var _amy_periodlineid_value_formatted = results.value[i]["_amy_periodlineid_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_periodlineid_value_lookuplogicalname = results.value[i]["_amy_periodlineid_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_posted = results.value[i]["amy_posted"];

                   var amy_posted_formatted = results.value[i]["amy_posted@OData.Community.Display.V1.FormattedValue"];

                   var amy_posteddate = results.value[i]["amy_posteddate"];

                   var amy_status = results.value[i]["amy_status"];

                   var amy_status_formatted = results.value[i]["amy_status@OData.Community.Display.V1.FormattedValue"];

                   var _amy_structurecode_value = results.value[i]["_amy_structurecode_value"];

                   var _amy_structurecode_value_formatted = results.value[i]["_amy_structurecode_value@OData.Community.Display.V1.FormattedValue"];

                   var _amy_structurecode_value_lookuplogicalname = results.value[i]["_amy_structurecode_value@Microsoft.Dynamics.CRM.lookuplogicalname"];

                   var amy_submitteddate = results.value[i]["amy_submitteddate"];

                   var amy_todate = results.value[i]["amy_todate"];

    alert(_amy_periodid_value_formatted);

    }

    // Create Payroll Detail Lines

    var entity = {};

    entity.amy_Amount = parseFloat(totalamount).toFixed(2);

    entity.amy_DepartmentCode = {

       Id: _amy_departmentcode_value1 ,

       LogicalName: "amy_department"

    };

    entity.amy_EmployeeId = {

      Id: _amy_employeeid_value5 ,

      LogicalName: "amy_employeemaster"

    }

    entity.amy_PeriodLineId = {

       Id: _amy_periodlineid_value ,

       LogicalName: "amy_periodline"

    };

    entity.amy_PayTransId = {

       Id: amy_paytranstableid ,

       LogicalName: "amy_paytranstable"

    };

    entity.amy_PayHeadId = {

       Id: ExpensePayhead ,

       LogicalName: "amy_payhead"

    };

    entity.amy_JafzaCode = amy_jafzacode1 ;

    entity.amy_GLCode =amy_glaccount  ;

    entity.amy_Date = new Date(today).toLocaleString();

    var req4 = new XMLHttpRequest();

    req4.open("POST", encodeURI(Xrm.Page.context.getClientUrl() + "/XRMServices/2011/OrganizationData.svc/amy_PayrollDetailsLinesSet"), false);

    req4.setRequestHeader("Accept", "application/json");

    req4.setRequestHeader("Content-Type", "application/json; charset=utf-8");

    req4.onreadystatechange = function() {

       if (this.readyState === 4) {

           this.onreadystatechange = null;

           if (this.status === 201) {

               var result = JSON.parse(this.responseText).d;

               var newEntityId = result.amy_PayrollDetailsLinesId;

    alert("Payroll will be created");

    //    Delete TempExpenseTable

           } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req4.send(JSON.stringify(entity));

    }

    alert(totalamount);

      totalamount=0;

                 } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req2.send();

     } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req5.send();

           } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req9.send();

            }

           } else {

               Xrm.Utility.alertDialog(this.statusText);

           }

       }

    };

    req10.send();

    }

  • Sumedha Profile Picture
    Sumedha 142 on at
    RE: PowerApp -- create , update,delete,

    Hii,

    In my table i have build a relationship of employee id .

    Can you please check my code .

  • Suggested answer
    Goutham A Profile Picture
    Goutham A on at
    RE: PowerApp -- create , update,delete,

    Hi Sumedha , We need more context to  assists better but from what i understand , if you are looking for aggregate value then you need to have a roll up field in the new table you created and establish  relationship(1:n) with employee table(i hope you already have this)

    learn.microsoft.com/.../define-rollup-fields

    If you do not intend to relate these two tables OOB and if this is required only on Client\UI, then you can go with writing a Javascript function(on Ribbon Action) and implement your calculation logic there. If you want this custom calculation logic to be reused, Go for a custom Action\API and call the action\API from javascript.

    <Please mark as Verified if it resolved your issue>

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans