This issue has to do with the project I mentioned here, but decided to make a new topic as to not confuse everyone.
This is an overview of what the project looks like:
Table | Fields
Team: TEAM_STATUS, TEAM_ID, TEAM_PHONEN, TEAM_EMAIL, TEAM_SIZE, TEAM_LIMIT, TOTAL, REMAINDER
Individual: TEAM_ID, INDIVIDUAL_ID, INDIVIDUAL_STATUS, PHONEN, EMAIL, AMOUNT_USED, IND_REMAINDER, TEAM_LIMIT_COPY, TOTAL_COPY, REMAINDER_COPY
Charges: TEAM_ID, INDIVIDUAL_ID, CHARGE_ID, CHARGE_STATUS, FEE
Team (Master) > Individual (Details)
Individual (Master) > Charges (Details)
Whenever I add a new Charge it supposed to be added to the Individual's AMOUNT_USED, then the IND_REMAINDER would be calculated. Additionally, the Team's TOTAL would be calculated by adding all AMOUNT_USED that share the same TEAM_ID as the new entry. The REMAINDER will be calculated the same way. All of this works 100%. The issue is when I delete a charge, specifically when the Team has only 1 charge; meaning that if the Team has 2 or more charges the following code works flawlessly.
string strAmount = "UPDATE A SET I.AMOUNT_USED = (SELECT SUM(FEE) CHARGESUM FROM CHARGES C WHERE C.CHARGE_STATUS = 'Active' AND C.INDIVIDUAL_ID ='" + deleted_values["INDIVIDUAL_ID"].ToString() + "')FROM INDIVIDUAL I, CHARGES C WHERE I.INDIVIDUAL_ID = '" + deleted_values["INDIVIDUAL_ID"].ToString() + "'";
CommonFunctions.db_exec(strAmount, null);
Example.
Step 1. Delete CHARGE_ID = 952 which belongs to John, his AMOUNT_USED would equal 0, IND_REMAINDER = 100, the Team's TOTAL = 90, Team's REMAINDER = 110, TOTAL_COPY and REMAINDER_COPY would equal TOTAL and REMAINDER respectively.
Step 2. Delete CHARGE_ID = 951 which belongs to Steve, his AMOUNT_USED would equal 55, IND_REMAINDER = 45, the Team's TOTAL = 55, Team's REMAINDER = 145, TOTAL_COPY and REMAINDER_COPY would equal TOTAL and REMAINDER respectively.
Step 3. Delete CHARGE_ID = 950 which belongs to Steve, his AMOUNT_USED would equal 0, IND_REMAINDER = 100, the Team's TOTAL = 0, Team's REMAINDER = 200, TOTAL_COPY and REMAINDER_COPY would equal TOTAL and REMAINDER respectively.
That's how it should work but unfortunately when doing step 3 the Charge is deleted but the values for Step 2 remain.
Side Question: FEE is a required look up value, when the value equals 0 I receive an error because it thinks the field is empty. Is there a way to ignore this error? I check if the field is empty by using this code in the "Before record added" event.
XVar data = pageObject.getMasterRecord();
if (data != null)
{
//Get INDIVIDUAL information for current Charge
string strSQLExists = "select * from dbo.INDIVIDUAL where INDIVIDUAL_ID='"+data["INDIVIDUAL_ID"].ToString()+"'";
XVar rsExists = CommonFunctions.db_query(strSQLExists, null);
XVar data1 = CommonFunctions.db_fetch_array(rsExists);
//set Charge's TEAM_ID
values["TEAM_ID"] = data1["TEAM_ID"];
//Get Team information for current Charge
string strTEAMExists = "select * from dbo.TEAM where TEAM_ID='"+values["TEAM_ID"].ToString()+"'";
XVar TEAMInfo = CommonFunctions.db_query(strTEAMExists, null);
XVar data2 = CommonFunctions.db_fetch_array(TEAMInfo);
if(data1)
{
if( ((values ["FEE"] + data2 ["TOTAL"]) <= data2["TEAM_LIMIT"]) && ((values["FEE"] + data1["AMOUNT_USED"]) <= 100) && (values["FEE"]) )
{
values["CHARGE_STATUS"] = "Active";
return true;
}
else
{
else ( (values["FEE"] + data2["TOTAL"]) > data2["TEAM_LIMIT"])
{
XSession.Session["message"] = "";
MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed Limit');</script>");
return false;
}
else if( (values["FEE"] + DATA1["AMOUNT_USED"] > 100)
{
XSession.Session["message"] = "";
MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed Approved Amount');</script>");
return false;
}
else
{
XSession.Session["message"] = "";
MVCFunctions.EchoToOutput("<script>alert('Missing FEE');</script>");
return false;
}
}
}
else
{
}
}