Conditional Calculation based on fields from another table

[n7mafia]

Hello everyone,

I've got a problem. I'm trying to build up a very simple database with two tables. Basically I want one set of records where I can input the repayments of some loans, and another table where I can get the status of the loans (how much is left to pay and how many rates have been paid so far or how many rates are left)

TABLE 1 FIELDS (Multiple Records)

CREDITOR, Text Field, (has a dropdown with 3 names)
DATE, Date Field
AMOUNT, Number Field

TABLE 2 FIELDS (One Record)

CREDITOR1, Calculation Field = 10.000 - If (TABLE1::CREDITOR = "CREDITOR1" ; TABLE1::AMOUNT ; 0)
CREDITOR2, Calculation Field = 20.000 - If (TABLE1::CREDITOR = "CREDITOR2" ; TABLE1::AMOUNT ; 0)
CREDITOR3, Calculation Field = 30.000 - If (TABLE1::CREDITOR = "CREDITOR3" ; TABLE1::AMOUNT ; 0)

Of course there is a mistake in my calculation since it doesn't work as intended. I've tried adding a relationship between the two tables with corresponding fields for the IF function (Creditor from Table1 = Creditor from Table2 and Amount from Table1 = Amount from Table 2).

Is it a silly mistake or I took the wrong approach altogether?

 

 

[comment]

Can you explain what are you trying accomplish by this? Usually, when you have two tables, it is to implement a one-to-many relationship. For example, one loan has many payments - so there would be multiple records in a Payments table for each record in the Loans table, and the two tables would be related by LoanID.

I cannot figure out what your two tables represent in real life and what exactly are you trying to calculate.

 

[n7mafia]

14 minutes ago, comment said:

Can you explain what are you trying accomplish by this? Usually, when you have two tables, it is to implement a one-to-many relationship. For example, one loan has many payments - so there would be multiple records in a Payments table for each record in the Loans table, and the two tables would be related by LoanID.

I cannot figure out what your two tables represent in real life and what exactly are you trying to calculate.

 

I'll try to further explain my issue. On Table 1 I have a set of records which contains data such as:

Creditor: Creditor1
Amount is 1000

Creditor: Creditor1
Amount is 2000

Creditor: Creditor2
Amount is 500

On Table 2 I want a single record where to perform some calculations such as:

Total Debt 1 is 10000
Total Debt 2 is 5000

Calculation field which calculates:

Total Debt 1 - All the Amounts found in records of Table 1 where Creditor is Creditor1
Total Debt 2 - All the Amounts found in records of Table 1 where Creditor is Creditor2

[comment]

26 minutes ago, n7mafia said:

On Table 2 I want a single record where to perform some calculations such as:
...
Total Debt 1 - All the Amounts found in records of Table 1 where Creditor is Creditor1
Total Debt 2 - All the Amounts found in records of Table 1 where Creditor is Creditor2

That is not a good thing to ask for. You would have to add a new calculation field to this table whenever you add a new creditor. That's not how a database is supposed to work. Not to mention how difficult this would be to implement (and how slow it would probably be).

There are number of ways to get an overview of total debt by creditor:

• You can produce a summary report of the Loans table (your "Table 1"), summarized by creditor and using a summary field to show the total debt amounts;
• You can (and very likely should) add a table of Creditors, add a calculation field to sum the related amounts from Loans, and view the results in List or Table view of this table;
• Use the ExecuteSQL() function to produce the summary as text (this would be more suitable for a scripted overview, because using the function in a calculation field can be very slow).