Forums: [SOLVED] Arithmetic Operations Accross Multiple Tables - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[SOLVED] Arithmetic Operations Accross Multiple Tables Rate Topic: -----

#1 User is offline   i.NoLim 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 45
  • Joined: 11-September 17

Posted 28 December 2017 - 06:05 PM

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;
	}

0

#2 User is offline   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 368
  • Joined: 26-April 08

Posted 28 December 2017 - 10:42 PM

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.
-jerry
0

#3 User is online   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15613
  • Joined: 03-February 03

Posted 30 December 2017 - 05:13 PM

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.co...asterrecord.htm

To access any field in any table:
https://xlinesoft.co...cs/db_query.htm
Best regards,
Sergey Kornilov
0

#4 User is offline   i.NoLim 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 45
  • Joined: 11-September 17

Posted 02 January 2018 - 05:03 PM

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

View Postjadach, on 28 December 2017 - 02:42 PM, said:

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.


View Postadmin, on 30 December 2017 - 09:13 AM, said:

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.co...asterrecord.htm

To access any field in any table:
https://xlinesoft.co...cs/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:

Posted Image
0

#5 User is offline   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 368
  • Joined: 26-April 08

Posted 07 January 2018 - 10:35 PM

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
}
}

-jerry
0

#6 User is offline   i.NoLim 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 45
  • Joined: 11-September 17

Posted 08 January 2018 - 07:23 PM

View Postjadach, on 07 January 2018 - 02:35 PM, said:

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!
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic