Current record calculation

[mickeyfinn]

I have 2 main tables, Pages and Services, with a Line Items link table that allows me to assign multiple Services to any number of pages. I have a Qty (number) field in Line Items. I also have a Tasks table, whose records are created from a portal in Services, which also have a Qty field. I want to show a portal from Pages that shows all related Tasks (which I have done) but also contain a calculation of Line Items::Qty * Tasks::Qty.

The logic is that each page is assigned numerous services which are each made up of a number of tasks. A page can contain one or more of each service. The user wants to be able to add services to a page, specifying the quantity needed for each. Each service can be applied to any page, and the user wants to see a portal showing the related tasks with the correct quantities for each. If I do a calculation based on the existing relationship, I can get the first instance of the Service in the database to multiply with the correct related task, but as soon as I add that Service to a new page, the calculation does not use the new Line Item quantity, it uses the original one. I was hoping to find a way to create a relationship where the current page being viewed is used as a match field in the Tasks table to Line Items (which contains the PageID foreign key).

The problem is that I would like to do this without creating another link table between Line Items and Tasks. I know a link table is the "correct" way to do this, but I only need this one calculation to work - if I can do this, the user interface will be much simpler, rather than having to script the creation of records in the link table.

[comment]

You can place a global gPageID field in Tasks, and use it to define the second relationship to LineItems - but you must make sure it gets populated every time you go to another record in Pages.

[mickeyfinn]

Sorry it took a while to get back to you, I have been doing some testing. In your solution, does this mean I have to populate the global by triggering a script, or is there a way to make a lookup or something else auto-enter the data?

[comment]

It needs to be a script. Either use script triggers, or restrict user navigation to scripted only.