Jump to content

Sum related values with same Key


This topic is 950 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm currently designing a database to track my properties, leases, tenants and costs involved. I want to make this as simple to explain as possible but fear it might not read that way.

After spending a few days trying to figure this out I have no idea what to try next.

I have an invoicing table related to my lease table (leaseid::leaseid) and on the lease layout I have an invoicing portal which I enter any charges/payments made on that lease. Some records entered in this table are charges/payments towards other companies, like different property management companies that I have. This is selected in a "Vendor" field with a popup menu where I can select said company (Vendor::CompanyID ; show second field only). All vendors are available to all leases via a cartesian join. In a smaller, separate portal on the same lease layout I want to display these companies, if any are entered for this particular lease, and a running total of what each company has charged and been paid for in that lease.

I have been successful in getting one instance of each company, if entered, to display by creating another table occurrence of my Vendors table and linking it to the Invoicing table by the CompanyID (Invoicing::Vendor = Vendor::CompanyID). Therefore, if Company X has two related records in the payment table for that lease, and Company Y has one then Company X just shows once and Company Y shows once. This works. Where I'm struggling is getting the running total for each company inside the invoicing table for that specific lease. The best I can get is a running total of all that companies entries regardless of what lease they're entered on.

I've spent two days trying to get this to work and I'm about ready to remove this feature all together. I know this will be a simple fix but any help appreciated as I'm going in circles now.

Link to comment
Share on other sites

42 minutes ago, madman411 said:

In a smaller, separate portal on the same lease layout I want to display these companies, if any are entered for this particular lease, and a running total of what each company has charged and been paid for in that lease.

I was with you until you mentioned "running total". I thought you wanted to show a portal where each company that was invoiced for the currently viewed lease would have a single, unique row. And in that row there would be a single amount representing the sum of all invoices of that company for the currently viewed lease. I don't see where a running total would figure in this.

 

Link to comment
Share on other sites

2 minutes ago, comment said:

I was with you until you mentioned "running total". I thought you wanted to show a portal where each company that was invoiced for the currently viewed lease would have a single, unique row. And in that row there would be a single amount representing the sum of all invoices of that company for the currently viewed lease. I don't see where a running total would figure in this.

 

Hi comment. Your explanation is what I am indeed after. I went a bit overboard trying to explain everything. The smaller portal needs to display which companies are entered into the invoicing table for that lease, if any, and the total charged/paid. 

If Company X has two related records in the invoicing table for the lease, one for $5 and one for $10 then the portal should display Company X $15.

Link to comment
Share on other sites

The way I would implement this is to add a global gLeaseID field to the Vendors table and use it to define a relationship to another occurrence of the Invoices table as:

Vendors::CompanyID = Invoices 2::Vendor
AND

Vendors::gLeaseID = Invoices 2::LeaseID

Then a calculation field (also in the Vendors table)=

Sum ( Invoices 2::Amount )

This calculation field goes into your portal showing Vendors on the Leases layout. You also need a script triggered OnRecordLoad to set the global field in the Vendors table to the LeaseID of the currently viewed lease.

(I hope I got this right; your use of companies and vendors to describe the same thing is rather confusing.)


Alternatively you could use the script to populate a global text field with the result of an SQL query. This does not require adding anything to your schema (other than the global field), but the display will be different.

 

  • Like 1
Link to comment
Share on other sites

comment - your solution only required me to add the global field and the script trigger and it worked. Everything else was already in place. This did the trick! Thank you.

Link to comment
Share on other sites

This topic is 950 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.