Portals for dynamic search using related values

[TonyInSeattle]

I’m trying to create a dynamic search using a portal, with some values related to other tables, but I cannot get this to work. Here are the details:

I have two tables: PRODUCTS and TASKS. Each product has a Product ID, Customer, and Department. On each product page, I have a portal to the TASKS table in which I can create tasks that automatically inherit the Product ID from the product.

I also have a Tasks Search page, which is a tasks layout with a portal to a copy of the TASKS table. I want the user to be able to choose a Customer and Department from drop-down lists at the top of the page to see the related tasks in the portal below.

For my key fields between the TASKS table copies, I have:

• gCustomer and gDepartment, which are global fields that contain the Customer and Department values for which the user wants to search the tasks.

• cCustomer and cDepartment, which are calculation fields that equal Customer & & “All”, and Department & & “All”. I would like to base the gCustomer and gDepartment value lists on the cCustomer and cDepartment values, so the user can choose a specific Customer or the value “All”, for example.

When I change the Customer or Department of a Product, I want the Customer and Department of the associated tasks to update, too. I’m currently doing this by making them Calculation fields that link to the PRODUCTS table. But this causes problems: cCustomer and cDepartment must then be unstored, so I cannot use them as key fields or value lists.

If I change Customer and Department in the task table to Auto-Enter calculations, they can be stored, but they don’t automatically update if the values change in the PRODUCTS table.

I think I explain this all pretty well in the attached file. I’ve been working on this for weeks to no avail. I would appreciate any help you can give.

Thanks!

Tony

TonyInSeattle_-_Dynamic_Search_with_Portals.zip