categories with multiple values

[kaosgds]

good day!

i'm on the steering committee for a new non-profit group in the pacific northwest, and trying to teach myself FM for our membership database. i've used it before for really basic stuff like mailing lists, but this may be out of my league. i learned some simple script/query writing years ago, but don't really remember much.

for our trade directory, each member has four listings, each listing belonging to one or more of 16 categories in a value list(some listings will belong to several categories). i need to create a columnar table for each different category with all the corresponding listings and the contact info for the member. these tables will then be converted to HTML for an online directory.

to get an idea of what i mean, you can see our current directory online at http://riverhours.org/Goods_and_service_provider.htm. right now we use excel tables, which are getting too unwieldy to update efficiently as our membership increases.

here's a simplified version of what it should look like. this is what i have so far:

memberID=1

contact_info (various fields)

listing_1 category_1 {[X] art, [ ] technology, [ ] labor, [X] education}

listing_2 category_2 {[X] art, [ ] technology, [ ] labor, [ ] education}

listing_3 category_3 {[ ] art, [X] technology, [ ] labor, [X] education}

listing_4 category_4 {[ ] art, [ ] technology, [X] labor, [ ] education}

memberID=2

contact_info (various fields)

listing_1 category_1 {[ ] art, [ ] technology, [X] labor, [ ] education}

listing_2 category_2 {[ ] art, [ ] technology, [X] labor, [ ] education}

listing_3 category_3 {[ ] art, [X] technology, [ ] labor, [ ] education}

listing_4 category_4 {[X] art, [ ] technology, [ ] labor, [ ] education}

now i need to pull each listing belonging to each category and lay them out in tables as so:

category=art

listing_1 contact_info memberID=1

listing_2 contact_info memberID=1

listing_4 contact_info memberID=2

category=technology

listing_3 contact_info memberID=2

listing_3 contact_info memberID=1

category=labor

listing_1 contact_info memberID=2

listing_4 contact_info memberID=1

listing_2 contact_info memberID=2

category=education

listing_1 contact_info memberID=1

listing_3 contact_info memberID=1

the memberID field won't be printed in the table, it's just there to match the listings with the contact info, and the listings will be alphabetized in each table. do you see how it works? it seems like to make it work there's gonna be something else i haven't addressed yet because of the different listing fields. i've been reading the help manual trying to see how portals work and was thinking perhaps that's the missing link.

how do i write something to do this? i've been racking my brains over the help manuals, but as usual they seem to assume you already know how to do everything. there doesn't seem to be much help out there for beginners unless you have a lot of money to spend, which we don't. i've looked at a lot of predesigned layouts and nothing really corresponds.

any assistance would be greatly appreciated

thanks

have a beautiful day

db

[Fenton]

This is actually not such a simple problem. You have 8 records. You want to export 10 records. This is not possible as is, unless you use XML; and even then there'd be big problems to separate the values. Checkbox fields are not really relationally correct, in that they are multi-valued fields. It's very convenient in FileMaker, but not so convenient if you want to deal with each value separately; and not at all if you want to export each as a separate record.

You have a couple of choices. The first, and most obvious, is don't use checkboxes. Use portals, with only 1 category value per record (radio button). But this means 2 tables.. (The structure you now have may be only 1 table, but that's not so good. You should have at least Members and Listings.)

The portals would be Listings, with a line items table, ListingCategories. So records for Listing 1 for MemberID 1 may be:

Member 1 tab Listing 1 tab Art

Member 1 tab Listing 1 tab Education

This is the data structure you need to export like you want.

An alternative, which would work with the data you have now, is to use checkboxes, but bust them out into separate records before doing the export, using a looping script.

It would be most efficient if it isolated only multi-valued checkboxes first. But that would involve first saving, then restoring the found set; which I didn't include, to cut down on confusion. It's not hard though, just mind-boggling :-]

But it would be somewhat tedious compared to setting it up relationally correct. On the other hand, setting it up correctly would not look quite as simple to the users; 4 sets of radio buttons rather than 1 checkbox with 4 values. But it would go about as quick with data entry.

[Attachment had a BUG! Fixed and moved to later post.]

[kaosgds]

as a matter of fact, while i work my way through the VTC tutorials, i've seen the need for a separate listings table, and have since been trying to write a looping script to pull each listing into its own record.

i'm attaching the test database i've been working from. i'm afraid my scriptwriting is still pretty rusty, though it seems i've gotten myself onto the right track anyway, which is a little encouraging even if you'll probably have a good laugh over the script i've been trying to work out.

the category thing is still a pickle, because some listings will always belong to more than one. i expect it'll probably end up involving some other complicated script involving found sets or something, but i don't really know, and haven't actually tried to tackle it yet. i looked at the files you attached, and it doesn't really seem to address listings that have more than one category value.

is there someplace i can get understandable information on exactly how check boxes work in FM7? nothing i've seen thus far really explains how to use one beyond how to make one.

thanks so much for your help!

db

GLCCtestdatabase.zip

[Fenton]

My first example explicitly separates listings that have more than one category, each into its own record, using a script. The 2nd example creates separate records during data entry, using radio button choices and a related table.

There are other ways to get the totals for different checkbox choices. The sum of self-relationships can be used (multi-lines being matchable via a relationship).

But if you want to export the results as separate lines then they have to be separate records. There are ways to tweak the export, such as repeating the summary field data in each line (FileMaker does not do this by default, but it can be easily done), or adding "header" lines, with XML/XSL.

The basic fact of checkboxes is that they are multi-line fields:

Record 1:

cat

dog

mouse

Record 2:

dog

horse

Much like repeating fields, easy to enter, more difficult than single-valued fields to analyze, much more difficult to export (as separate values).

[Fenton]

After looking at your file, which I probably should have done before responding, I see that your other problem is that you've put the listings 1-4 in the Member table. This causes problems also, beyond the checkbox one. It could still be done, but is poor relational design. I assumed you would be entering this listing checkboxes in a 4-row Listings portal in the Member table.

I also see that you have different data than you first posted, multiple words rather than single. I'll have to tweak the script to use Values functions instead of Words; that's really better anyway.)

And, my original file had a BUG! It was getting a wrong value in the inner Loop (from Category, which it should have been from _gListing, the global). So it would only work for 2, then it would repeat.

MembersListings.zip

[kaosgds]

hmmm...so if i'm getting this right, i could maybe write something to search for each individual text string in the category value list, and then use that to compile a list of the matching listings.

i don't need to print the actual category field in the visual layout, so if it has more than one value, i suppose it wouldn't matter much.

the visual layout might then be something like this for the category tables:

<<merge field with text string>>

listing member contact_info [this is what i'd use a portal for, right?]

now back to getting those pesky listings separated...i need to look more closely at the files you attached and try to see what the scripts you wrote are doing.

thanks again

db

[Fenton]

Once you have the listings as separate records, all you have to do is sort by the category field then export.

[kaosgds]

i think i'm getting some of it...i've adapted the portal so i can see how it will look with listings, and it seems to be working pretty well. layouts aren't a problem, just getting the relationships set up properly.

still not really getting what the scripts and calculations are doing, but i guess those are questions for a different forum.

thanks for all your help!

i'm sure you'll be hearing from me on the boards again

db