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.

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.

 

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.

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.

 

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.