I have 4 tables; Property, Portfolio, Portfolio Properties and Pipeline.
Portfolio Properties is the linking table to Portfolio and Property. However, because I want to be able to add properties to the portfolio on creation, I’ve had to do it through the vLookupID and vLookupPartentID linking method.
Because of this, the PortfolioID isn’t always stored in the Portfolio Property table.
This means that in the Pipeline table, which only has the PortfolioID, I can’t link in the properties that are related to that portfolio.
Really what I need to do is link the pipeline to the portfolio using the PortfolioID.
Then link the Portfolio to the Portfolio Properties using vLookupID and vLookupParentID
Finally Link in the Property Table to the Portfolio Properties using the PropertyID.
I can only think I can do this in CAML but I’m really not sure where to start. Does anyone have any advice on how I might achieve this?