This topic is locked

Delete Entry Error & Empty Field Error

7/12/2018 11:39:50 AM
ASPRunner.NET General questions
I
i.NoLim author

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

{

}

}