Many-many hierarchical entry

[tdawg]

Short question:

Is there any way to change the order of check-boxes in a value list from alphabetical to some custom order? If not, read on ...

Long question:

1) A part of my database consists of a many-to-many relationship between two files:

-a "companies" file

-a hierarchical "products" file.

I'm using the join file structure between the two for reporting purposes.

In the "companies" file, I want an entry screen showning a hierarchical display of the data in the "products" file (through a portal), with some form of checkboxes beside the entry options to display data already entered, and to act as buttons for entry.

Any suggestions?

2) Also, I was wondering if it were possible to flag the currently selected record (for relating purposes in another file), and to have this value continually updated.

Example: to have some_field=1 on the currently selected record, and =0 on all other records.

Any help would be appreciated!

[Vaughan]

Only custom (manually typed) value lists can have their order set. All others are alphabetical ascending.

Regarding "flagging" records, any script that replaces fields is going to bnreak in multi-user mode due to record locking. There is probably some other way of doing it, perhaps a bit more detail might help us understand what you want to do

[tdawg]

The method I suggested above was one idea I had for allowing the form of entry I want, but I'm very open to suggestions. (translation: Help!!) I'll clarify what I have & what I want ...

The relevant files 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)

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!

[tdawg]

Thanks for the tip - not a bad way to handle the ordering in a checkbox list. Any ideas on how to handle the many-to-many entry situation described in my second message?

[BobWeaver]

Actually, you can change the order of the check boxes. I'm assuming that you are using a valuelist generated from field data (call it VLItems). You need another field to determine the sort order. Either use an existing one, or create a new one (call it VLSort) and enter values in it according to how you want the list to sort (ie, 1 for the first item, 2 for the second...). also set up a calculated field "FormattedVLItems" with this formula:

VLItems & "