This topic is locked
[SOLVED]

 Arithmetic Operations Accross Multiple Tables

12/28/2017 1:05:57 PM
ASPRunner.NET General questions
I
i.NoLim author

Hello everyone!
I have 3 tables: Family, Applicant, Charges.

Relations: Applicant is the details table of Family; Linked by field Family_ID

Charges is the details table of Applicant; Linked by field Applicant_ID
Family has a field called "Total" while Applicant has a field called "Subsidy." Charges has a field called "New_Charge."
Issue: I would like to set the limit for "Total" to 1000 and for "Subsidy" to 400. Every time a new charge is added it must be checked on whether it exceeds any of the limits by adding the "New_Charge" to our current "Total" and "Subsidy." If it exceeds, it should show a message "Limit exceeded" and will not record the entry; else record entry.
I have the following code in the "Before record added" event of the Charges table but I'm not sure how to reference the "Total" and "Subsidy" from the Family and Applicant tables.

if ( ((values["New_Charge"] + values["TOTAL"]) < 1200) && ( (values["New_Charge"] + values["SUBSIDY"]) < 400 ) )

{

return true;

}

else

{

XSession.Session["message"] = "";

MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed amount');</script>");

return false;

}
jadachDevClub member 12/28/2017

On add page before display you will need to get the values from the parent tables - maybe put them into a session variable, then use those in your before added event.

admin 12/30/2017

In event like BeforeAdd you have access to all values entered on that Add page. If you need to access fields from other tables you either need to use API calls or SQL Queries.
To access master table info:

https://xlinesoft.com/asprunnernet/docs/getmasterrecord.htm
To access any field in any table:

https://xlinesoft.com/asprunnernet/docs/db_query.htm

I
i.NoLim author 1/2/2018

I apologize for not replying sooner, because of the holidays I was not at work.



On add page before display you will need to get the values from the parent tables - maybe put them into a session variable, then use those in your before added event.



Yeah, I have an idea of what I'm supposed to do... I just don't know how to do it. I dont know how to code it and get the values from the parent tables.



In event like BeforeAdd you have access to all values entered on that Add page. If you need to access fields from other tables you either need to use API calls or SQL Queries.
To access master table info:

https://xlinesoft.com/asprunnernet/docs/getmasterrecord.htm
To access any field in any table:

https://xlinesoft.com/asprunnernet/docs/db_query.htm


I've tried using SQL queries before but it didn't work. This is what I've tried in the past, I created two new values in the CHARGES table and copied the values from "TOTAL" and "SUBSIDY" from the parent tables. I wrote this code in the "Before record added" so I could add these values to the "NEW_CHARGE" and compare it to the limits, it didn't load the values into the newly created fields.

//SET THE "CHECK_SUBSIDY" IN CHARGE TABLE EQUAL TO "TOTAL" IN FAMILY TABLE; SET "FAMILY_ID" IN CHARGE TABLE EQUAL "FAMILY_ID" IN APPLICANT TABLE BY CHECKING SAME "APPLICANT_ID"

DB.Query("UPDATE CHARGES SET CHECK_SUBSIDY = A.SUBSIDY, FAMILY_ID = A.FAMILY_ID FROM APPLICANT A WHERE CHARGES.APPLICANT_ID = A.APPLICANT_ID");
//SET THE "CHECK_TOTAL" IN CHARGE TABLE EQUAL TO "TOTAL" FROM FAMILY

DB.Query("UPDATE CHARGES SET CHECK_TOTAL = F.TOTAL FROM FAMILY F WHERE F.FAMILY_ID = CHARGES.FAMILY_ID");*/
if ( ((values["NEW_CHARGE"] + values["CHECK_TOTAL"]) < 1200) && ( (values["NEW_CHARGE"] + values["CHECK_SUBSIDY"]) < 400 ) )

{

return true;

}

else

{

XSession.Session["message"] = "";

MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed amount');</script>");

return false;

}


Using the info you provided me:



dynamic data;

dynamic rs = DB.Query("select * from FAMILY F where F.FAMILY_ID = CHARGES.FAMILY_ID");
while(data = rs.fetchAssoc())

{

if (((values["NEW_CHARGE"] + data["TOTAL"]) < 1200) )

{

return true;

}

else

{

XSession.Session["message"] = "";

MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed amount');</script>");

return false;

}

}


I received the following error:

jadachDevClub member 1/7/2018

Try something like this. I am sure there are other ways, but I do think this will work.
Create 2 new views in your database (I assume SQL Server)

CREATE VIEW [dbo].[Subsidy_View]

AS

SELECT dbo.Applicant.Family_ID, SUM(dbo.Applicant.Susidy) AS Susidy, dbo.Family.Total

FROM dbo.Applicant INNER JOIN

dbo.Family ON dbo.Applicant.Family_ID = dbo.Family.Family_ID

GROUP BY dbo.Applicant.Family_ID, dbo.Family.Total
GO
CREATE VIEW [dbo].[SubsidyTotal_View]

AS

SELECT dbo.Applicant.Family_ID, dbo.Applicant.Applicant_ID, dbo.Subsidy_View.Susidy, dbo.Subsidy_View.Total

FROM dbo.Applicant INNER JOIN

dbo.Subsidy_View ON dbo.Applicant.Family_ID = dbo.Subsidy_View.Family_ID
GO


In your Before Record Added for the Charges table, add this:

XVar data = pageObject.getMasterRecord();

if (data != null)

{

string strSQLExists = "select * from dbo.SubsidyTotal_View where Applicant_ID='"+data["Applicant_ID"].ToString()+"'";

XVar rsExists = CommonFunctions.db_query(strSQLExists, null);

XVar data1 = CommonFunctions.db_fetch_array(rsExists);

if(data1)

{

if ( ((values["New_Charge"] + data1["Total"]) < 1200) && ( (values["New_Charge"] + data1["Subsidy"]) < 400 ) )

{

return true;

}

else

{

XSession.Session["message"] = "";

MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed amount');</script>");

return false;

}

}

else

{

//nothing

}

}
I
i.NoLim author 1/8/2018



Try something like this. I am sure there are other ways, but I do think this will work.
Create 2 new views in your database (I assume SQL Server)

CREATE VIEW [dbo].[Subsidy_View]

AS

SELECT dbo.Applicant.Family_ID, SUM(dbo.Applicant.Susidy) AS Susidy, dbo.Family.Total

FROM dbo.Applicant INNER JOIN

dbo.Family ON dbo.Applicant.Family_ID = dbo.Family.Family_ID

GROUP BY dbo.Applicant.Family_ID, dbo.Family.Total
GO
CREATE VIEW [dbo].[SubsidyTotal_View]

AS

SELECT dbo.Applicant.Family_ID, dbo.Applicant.Applicant_ID, dbo.Subsidy_View.Susidy, dbo.Subsidy_View.Total

FROM dbo.Applicant INNER JOIN

dbo.Subsidy_View ON dbo.Applicant.Family_ID = dbo.Subsidy_View.Family_ID
GO


In your Before Record Added for the Charges table, add this:

XVar data = pageObject.getMasterRecord();

if (data != null)

{

string strSQLExists = "select * from dbo.SubsidyTotal_View where Applicant_ID='"+data["Applicant_ID"].ToString()+"'";

XVar rsExists = CommonFunctions.db_query(strSQLExists, null);

XVar data1 = CommonFunctions.db_fetch_array(rsExists);

if(data1)

{

if ( ((values["New_Charge"] + data1["Total"]) < 1200) && ( (values["New_Charge"] + data1["Subsidy"]) < 400 ) )

{

return true;

}

else

{

XSession.Session["message"] = "";

MVCFunctions.EchoToOutput("<script>alert('Charges exceed allowed amount');</script>");

return false;

}

}

else

{

//nothing

}

}




Thank you!!!
The top part of your second code was exactly what I needed, I didn't know how to retrieve those values.
Thanks again!