0

Show Total Amount for a specific Record

Hello, Let's say I have two tables: Donations table and Profile table. The Profile table will store personal related information and the Donations table all the Donations we have received.

I want a field inside the profiles table that shows the total amount that was donated by that person based on the status.

This is the code I`m currently using (Created by sakshi) but this code sums everything with status 1 inside the donations table and what i want is the total for the specific record inside the Profiles table that this donation is linked to.

sum((select Donations where number(Status) = 1 ).'Pledged Amount')

I tried to do this, but it does not work:

sum((select Donations where number(Status) = 1 and number('Donor ID') = number(Profile.Id)).'Pledged Amount')

 

Thank you

5 replies

null
    • Support
    • 5 yrs ago
    • Reported - view

    Hi, thanks for your request. Please write to support@ninoxdb.de. We can schedule a screen sharing session and help you to find a solution. 

    Best regards, Alex

    • MattL
    • 3 yrs ago
    • Reported - view

    Was this ever resolved? I am looking to do this exact same thing.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    I suppose that there is a 1:n relation between the tables "Donations" and "Profile", and that the field "'Donor ID'" in the "Donations" table is somehow set to the ID of the corresponding donor.

    In the formula

    sum((select Donations where number(Status) = 1 and number('Donor ID') = number(Profile.Id)).'Pledged Amount')

    the logical expression

    number(Status) = 1 and number('Donor ID') = number(Profile.Id)

    is evaluated, in turn, in the context of each record of the "Donations" table. Which means that the local field "'Donor ID'" is compared with the related field "Profile.Id", as seen from the same record of the "Donations" table. One should expect them to always be equal. If you need to compare the local field "'Donor ID'" with the value of the field "Id" in the (fixed) current record of the "Profile" table, you need to save that value while still in the context of that record, before entering the "select". Which means something like:

    let pID := ID;

    sum((select Donations where number(Status) = 1 and number('Donor ID') = pID).'Pledged Amount')

    • MattL
    • 3 yrs ago
    • Reported - view

    Thanks Alain. My ID in this case is an email address.

    Data is in two tables T1 is donations (fields are Bill Email and Amount) and T2 is donors (field here is email) the donors table is where I want the sum of gifts field to appear. Do I need to set that up as formula field or as a relation from donations?

    If I'm following you right, I came up with something like this:

    let pID := 'Bill Email';
    sum((select Amount where 'email' = pID).'Total Donated')

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Since you want to put total in the T2 table, you need to put in a formula (*) field in that table:

    let donorId := email;

    sum((select T1 where 'Bill Email' = donorId).Amount)

    If the "donations" table is not called "T1", replace with the correct name in the second line…

    (*) You may call this field 'Total Donated'.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 5Replies
  • 1576Views