Related record creation/deletion through portal

[tdawg]

I have a little many-to-many entry problem here. Here's my situation ...

 

The relevant files in my database are structured as follows:

--2 files, "companies" and "products", in a M2M relationship

--1 join file for the M2M, "line_items"

--"products" file is a hierarchical listing (hierarchy done through self-joins)

 

What I want is a nice way of entering, and showing already-entered, data into the "line_items" file FROM the "companies" file. The entry choices (in the form of a hierarchical list) are individual records in the "products" file.

 

My preliminary idea was as follows:

--The entry is performed using a portal.

--The portal is based on a relationship between a (drop-down selected)global field in the "companies" file, and a calculated "category" field in the "products" file .

--The portal then shows the products from the specified category.

--In the portal, next to the product names, would be a calculation graphic (a checkmark), indicating that the company does/does not have that product. (Note: the checkmarks should correctly show related items from "line_items" as the user scrolls though records in the "companies" file - sorry, no scripted calculations allowed)

--The checkmark graphic in the portal doubles as a button to set/clear that particular product (ie: to make or delete that particular record in the "line_items" file.

 

The major roadblocks I've come up against:

--How to have a "global, calculated" field in the "products" file which always contains the ID of the record currently being browsed.

--How to calculate a "product_exists" (0,1) field in the "products" file to indicate that the current company makes a given product. (ie: a real-time find related records calculation)

 

:

 

The reason I'm not simply using a checkbox-based value list is that I need the M2M structure for reporting purposes, smaller file sizes, and need the entry options structured as a hierarchical list.

 

All of this seems a little convoluted for such a simple task, and I'm wondering if there's some built-in feature that would help with this, or some function I just don't know about.

 

Any help on the roadblocks above, or offering a completely different perspective on a solution to this problem, would be greatly appreciated.

 

Thanks!