Portal conditions and totalling problem in Project Backup DB
I've got a problem that I initially thought would be easy but is now making my head spin.
I am trying to create a project backup database that tracks the location and status of projects on external hard drives. As of now I've got two tables, "ProjectTable" and "HardDriveTable", and a layout for each.
"ProjectTable" consists of:
Backup Drive 1 (Primary)
Drive 1 Last BU Date
Backup Drive 2 (Redundant)
Drive 2 Last BU Date
"HardDriveTable" consists of:
--and a portal that shows the Agency, Client, Project, Size, and Last BU of the included projects specified in "ProjectTable".
I want to be able to add new project records as they come in the Projects Layout and add new hard drive records as needed in the HardDrive Layout.
The problem I'm having is this:
Each HardDrive record should be able to check each Project record to see if it is included in either backup slot, and if so display the relevant data in the portal. The total size of the projects present in this portal should be subtracted from the Total Capacity of the drive to give Free Space.
I've been able to filter the relevant project records in the portal by first creating the relationship between Project and HardDrive TO's:
Backup Drive 1 X Drive Name
AND Backup Drive 2 X Drive Name
and then filtering the result in the portal with:
ProjectTable::BU_Drive_1 = HardDriveTable::DriveName or ProjectTable::BU_Drive_2 = HardDriveTable::DriveName
This gives me the correct result in the portal, but what I don't understand is why when removing the second match I get the same results. Should values from Backup Drive 2 even come up as a result if it's not included in the range?
The second problem I'm having is then totalling the sizes of the projects included in the portal, and then using that result to calculate the remaining drive space per hard drive record. The result I'm getting right now is the total size of all projects combined.
Does anybody have an explanation for the relationship issue or a solution to the portal total. I am new to this so I'm not even sure if I'm approaching the design correctly.
Oh, and I'm on OSX 11 Advanced.
Any help would be appreciated.