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)
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();
}
Hii,
In my table i have build a relationship of employee id .
Can you please check my code .
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>
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,522 Super User 2024 Season 2
Martin Dráb 228,441 Most Valuable Professional
nmaenpaa 101,148