Seeking simpler value list structuring

[jrob]

A bit over a year ago I had a piece of programming done for a solution of mine. The objective was to have conditional value category lists for each of 8 tables where I could input all values without leaving the table's layout. I was told by the programmer he provided the only option available - I'm looking to find out if there's a simpler alternative more suited to my skill level.

These are the tables & fields that were created:

These are the relationships created:

Here's the first script:

Here's a script (I assume was to be deleted as it's missing table & field info?)

Here's the layout created - one for all 8 tables

Here's what it looks like in browse mode:

The programmer set up 2 of the 8 tables and they are working but - I'm unable to set up the rest. Worse, if I clone my solution, I'm unable to import records from my original as the categories can't be imported, and all I get in their place are numbers.

[comment]

The objective was to have conditional value category lists for each of 8 tables

You have 8 tables using the same classification to categories?

[jrob]

I'm not sure I understand the correct definition of "classification." Within each table I need to designate conditional category values at up to 3 levels - category 1, 2 & 3. The category values are different in each of the 8 tables. What the programmer structured was a a single layout where I have to select one of the 8 table's arrows which then allows me to enter a value in category 1. That enables me to select its arrow and enter a value in category 2 and so on. In the 2 layouts for the tables he did all works.

However, I can't even figure out how to get working fields on the other 6 table layouts. If I could figure out how to add the fields to those 6 table layouts AND figure out how to import the categories I'd leave it as is - even though this is not at all what I wanted. The reason I can't figure out how to get the fields added to other layouts is because they are not in the 6 tables - they are in tables of their own as shown in the screen shots above.

[comment]

The category values are different in each of the 8 tables.

Ah, that's OK then. But then what is the advantage of keeping them all together?

The other thing is that "up to 3 levels" is not the same as "exactly 3 levels" (which is how your current solution seems to be structured). Perhaps you should consider a recursive hierarchy instead - where all categories are in the same table, with each value linked to its parent category.

[jrob]

There's no advantage I know of to keep them altogether. It's not what I asked of the programmer, it's what he said had to be done.

The current structure allows for stopping after selecting the first, second, or third category. That is what I wanted. I didn't want to be forced into designating all 3 categories for each record in each table.

I'm willing to try anything I can understand. When you suggest a recursive hierarchy:

First, do I find the best documentation on how to do that in this forum or the FM Knowledge Base?

2nd, do you mean I create a new table to house all the categories? That's what the programmer did appears to be above my skill level to implement

3rd, if "each value..is..linked to its parent category" does that allow for me to assign values for all categories in the browse mode? (Which is what I need)

[qube99]

The way I've always done it is to list all categories in a standalone table. Then each category can be assigned another category as a parent. In this way there is only 1 parent selection for each category and the nesting is of arbitrary depth - child, grandchild, great grandchild, great great grandchild, etc. If no parent is selected then the category is a top level category with no parent.

There is no script. A category either has a parent or it doesn't. A server-side script outputs the HTML class IDs for dropdown/flyout website menus by iteratively counting the ancestors. The FMP Category data only needs 2 fields to work, Category_Name and Category_Parent which is a just a list of Category_Name. Needless to say all category names must be unique. One could move the simple counting script over to FMP if the full ancestry is needed there.

I use it for ecommerce category data. It's really simple.

[jrob]

I head over heels in love with simple. Would you know where I could find an example?

[qube99]

Take any category and write a script to get it's CATEGORY_PARENT. If it doesn't have a parent (false) then it's a top level category.

If the boolean is true then iterate and ask what the CATEGORY_PARENT of the CATEGORY PARENT is. If true it's a second level category.

Iterate and keep asking what it's parent is until you get a false and you've reached the top ancestor.

Count the number of iterations to get the ancestry level.

You can use this to create an indented list of all categories, or replace the indents with whatever you want (arrow graphics, dancing babies or something). I like HTML dropdown/flyout menus a lot.

You can also just show the children of a single category but this is a bit more complex scripting. You can also get the ancestry of a category and create breadcrumb links with a pretty simple script - just iterate in reverse.

Some dropdown flyouts get too large (run off the screen) and must be abandoned in favor of a straight list where only the selected category displays its children in an indented list format that allows scrolling.

When working with category spreadsheets it's important to be able to sort them in a variety of ways. Sometimes clients want the newest at the top. Alphabetical is quite useful. I provide an extra field (simple numbering) so that the client can define a custom sort order. This gets complex quickly because there is a sort order for each level of ancestry and THEN a custom sort order within each category. Data entry order is usually too chaotic for humans to interface with.

In this system a category can have only 1 parent. If you want a category to have 2 or more parents then use an ID code instead of the name. Then you can copy a category and give it 2 codes with the same display name. The data then uses CODE, NAME and PARENT. The display script uses the CODE instead of the NAME. No big deal, just 1 extra datum in the spreadsheet and 1 more line of code in the script.

This is a universal solution for all types of nested, multi-level category structures.

I'm curious what the 8 tables are about. I only need 1 table for display with a self join filtered portal. Maybe yours has a trick in it requiring an 8 table solution.

[jrob]

Correct me if I'm mistaken, but I believe what you're describing is what the programmer set up. Can you look at the screen shots above and tell me if that's true? If so, perhaps I can use your details to finally figure out how to get the 6 unfinished categories onto layouts so I can use them.

This is not a solution for commercial use. It's personal and for my friends who keep asking if it's ready for them to use.

The 8 tables are: Contacts, Associations, Activities, Health, $$$$, Notes, Things & Lockbox. The 8 tables are in 8 tabs on a single Contacts layout page - with the 7 other tables in a variety of portals. I think this is the same or similar to what you say you commonly structure.

[qube99]

I would have just 1 table in a self-join relationship.

I would have a Dropdown List of the categories.

I would have 1 layout.

Then filter the portal with the dropdown selection.

Add categories as needed and they automagically appear in the list.

You only need extra layouts if the displays are different. In this case use case or if scripts triggered from the list selection.

Yes, it's similar to your solution. It uses 1 table for all items and categories with no need to create a new table for new categories. You don't need a script to add new categories or revise existing ones. Categories are treated like an attribute of an item.

I don't think you need any parent-child category structure unless I misunderstand.

[comment]

There's no advantage I know of to keep them altogether. It's not what I asked of the programmer, it's what he said had to be done.

I would suggest you put aside what you have now and concentrate on what would fit best the way you want to work.

appears to be above my skill level to implement

Well, that could be a problem... For example, it might be convenient to select the category from a hierarchical portal, such as the one shown here:

http://fmforums.com/forum/topic/72391-getting-more-out-of-filtered-portals-6-hierarchical-portal/

But it's not something I would recommend to a beginner.

[jrob]

Re: http://fmforums.com/...rchical-portal/ Kind of scary looking to me, but will try it if the following doesn't go in the right direction.

Does this work:

• Stay with the one CONTACTS layout
  
• Create a relationship for each of the 6 tables currently without categories as follows:
  

Relationship: contacts_TableName :: contacts_TableName_Selected

xgSelectedRecord = _idTableName

AND _idfContacts = _idfContacts

then Create a relationship between contacts_TableName_Seleted and contacts_CATEGORY1_Sel_LB where

_idCATEGORY1 = category1

Then I would use the TableName relationship for the category fields in the layout

[qube99]

Hierarchical portal is what I was doing with a filtered list but my approach is clumsier.

[jrob]

I believe this was the programmer's approach. Are you saying duplication of his relationship structure would work for the other 6 tables?