Sort by custom value list

[pfarrell]

need some assistance please:

-- in one layout i have individual records with up to 10 categories assigned to each record via a custom value list.

-- in another layout i want to select from a single pop-up field based on the same custom value list and perform a search of all records that have been tagged with the selected category (they then appear as a list). i've read that without a plugin, the only way to do this is with a scripted button, which is fine by me...but i'm having issues with that script...seems like this should be simple...but...

also...one weird thing...when i select from the "search" pop-up...it seems to disappear...and otherwise behave erratically on the page...? maybe i've set it up wrong?

file attached for anyone interested (might look wierd without the right font). thanks for any help!

-patrick

rtdb.fp7.zip

[LaRetta]

Hi Patrick,

Well I see a certain future with this approach; which isn't good or bad. But to take advantage of it, I suggest you incorporate a text calculation (maybe called cCombineCategories) which holds a multiline of all 10 fields. It would look like this:

Category_1 & ¶ &

Category_2 & ¶ &

Category_3 & ¶ &

Category_4 & ¶ &

Category_5 & ¶ &

Category_6 & ¶ &

Category_7 & ¶ &

Category_8 & ¶ &

Category_9 & ¶ &

Category_10

Do not change this field to unstored - we want it indexed for finds and relationships. Why would I suggest multiline over concatenation (line with spaces between each word) - or even looping through fields on a Developer table, creating new find requests on each field? Because this calc would produce a nice key field which can be used in portal displays via checkbox based upon that custom list as well as Finds. And because it is easier to script and maintain. This type of field might be more functional over concatenation if one wants to see multiple conditional combinations. This is obviously a critical part of your solution and I think the resource-cost is worth it.

Oh BTW, I've seen your wonky popup behavior. I saved the file which generated it because it intrigues me. I could provide a GIF of the identical problem. Once I click outside the popup a few times, it magically appears again and it too is based upon a global (and list view) and the field drops down into the body (8.0v2). I believe you have a trashed layout. It happened in this file when it crashed - this was one of the residual effects. If a file crashes, I revert to backup copy. Some would recommend just deleting the layout and sometimes this works. But I believe it is a symptom of file crash and I don't believe it is worth risking continuing work on a file that has crashed.

LaRetta

[pfarrell]

hi LaRetta-

thanks for the response and the clues...actually, a field (Categories_Assigned) exists in the db with that exact text calculation in it...i set it up just to keep track of what selections had been made from the drop-downs - per record .

you are saying that i could use this to return search results based on the right script for the "submit/search" button in the list view?

so far i've been trying to do this with an "if" statement...setting the value list global pop-up field in the list view to show related records if it equals the "assigned categories" field with the calculation in it...this is not working...it's surely my lack of scripting knowhow...any other thoughts?

and on the wacky pop-up behavior...i rebuilt the entire db with a new file...and the pop-up still vanishes inbetween making new selections...it is not however showing up in the body like it was before...very strange...it seems to only behave like this in list view...fm bug? seems to not be an OS thing...as you're on PC...

thanks again,

patrick

[LaRetta]

Ooops, I didn't see you've already created a multiline, Patrick. But yes, you can search on it AND use it in your relationship. It will work as an OR condition and match to any category. But you have your relationship set to join to ALL categories. Using this method, if ANY category is blank, the relationship will break (because ALL key fields don't contain a value). I've attached a file which shows how you can 1) perform a find on your Categories_Assigned field and also use it to filter a relationship (using GTRR).

The momentary disappearance of the value list seems to only happen when it is in the Header and I consider it a bug - it worked right in vs. 7. I haven't upgraded to 8.0v3 yet and I wonder if it is fixed. But the jump of the field down into the body is a trashed layout (as you've found out).

Try this (attached) …

Note the changes in your graph - not joining the global to EVERY category but rather joining the global to the calculation multiline. In this way, if there is ANY match then the relationship will be value and produce results. BTW, your subject is sort. What does sort have to do with this?

UPDATE: I must say something here ... I have never used 10 different category fields. I've always used ONE field and used checkbox to insert multiple selections. I'm not saying it isn't an okay method (especially if the ORDER or PRIORITY of selection matters), only that it will cause some complications you are already facing. If one field was used, then the multiline would exist naturally.

LaRetta :wink2:

rtdbMOD1.zip

Edited July 8, 2006 by Guest
Added update

[Razumovsky]

UPDATE: I must say something here ... I have never used 10 different category fields. I've always used ONE field and used checkbox to insert multiple selections. I'm not saying it isn't an okay method (especially if the ORDER or PRIORITY of selection matters), only that it will cause some complications you are already facing. If one field was used, then the multiline would exist naturally.

Hi Laretta,

I very much agree that I would not use 10 category fields for this, but would not be as diplomatic in my response. Could you elaborate why this approach might offer advantages when priority and order of selection matters over, say, a single repeating field or a join table structure? I am having a hard time thinking of an example at the moment. It would seem that 10 fields would mean 10 times more work in many operations, but you always seem to have some obscure real life example up your sleeve that I wouldn't have considered...

-Raz

[LaRetta]

Hi Raz,

I do not know the context of the selection process and I was being overly cautious. Just because I can't think of a use (or have never used) a method doesn't mean there aren't reasons it might be used. But something nags at me ... that maybe a use exists. How about this:

1) Only 10 selections are possible. Yes, one could ValueCount() them and script could stop them but ...

2) Visually display all 10 fields in the order they were entered. Yes, a portal could do the same thing and portal can be broken up to display in two columns just like the fields.

3) Having a different script attached to each field. Yes, script can be attached to repetitions (or portal row) as well (both of which provide multiline relational capabilities).

4) Maybe a conditional value list will need to only consider entries from Category 1. Maybe Category 2 should display all options EXCEPT what has been selected in Category 1. Maybe, maybe ...

So no - I can't think of really good reasons for the multiple fields (if they all mean the same thing). Thank you for helping me think through what felt wrong (and right) about multiple categories. I'm still sitting on the fence but leaning on the side of alternate methods. :wink2:

[Razumovsky]

1). specify only 10 rows or repetitions.

2). ...Couldn't have said it better

3). With portals, you could use a single button and a single script with get(portalRowNumber) as the parameter

4). Hmmm, like cat 2 vl displays all but cat 1, cat 3 vl displays all but cat 1 and 2... ? I still think you would have to write 10 different calcs/valuelists for this when it could be done with one in a portal.

Just because I can't think of a use (or have never used) a method doesn't mean there aren't reasons it might be used.

Agreed. I will keep the fence in eyesight, but think I will stay over here for the moment. I spent some four hours yesterday trying to make something work with multiple fields on a single record that only took about 5 minutes once I realized the normalization issue. I had thought it was one of those rare occasions where a multi field approach would have been better, but no... it always seems to be an indication of a larger problem for me.

*I realize that you are not suggesting a 10 field approach at all. I am just interested as well in finding a scenario where it might be preferable - thanks!

[LaRetta]

1)...specify only 10 rows or repetitions.

Oh sure! I never allow adding records in portal and use button above portal for new records thus script must catch it - didn't think about someone adding directly in portal.

Thank you for questioning ... I really appreciate it. I hope Patrick takes your concerns as seriously as I do. And who knows ... maybe he can provide a good reason for us. :wink2:

[pfarrell]

fantastic!

nice little discussion going on here.

LaRetta...i realized the "misjoin" of the global on my own...and then read your post...it's rewarding to feel on the right track...that script for the find would have taken me a while to crack on my own however - and i really appreciate the guidance. this is working exactly as we need it to...now on to figuring out how to print the selected records!

as for my calling the post subject "sort", i suppose it should have been "find"...

also, to shed some light on what you and Raz have been pondering with regard to why in the world i'd want 10 categories worked up in this way....it's pretty simple really....since this is a "resource" db with everything from photo assistants with woodworking skills ("photo assistants", "woodworking") to tabletop prop companies that dable in flowers on the side ("tabletop", "flowers", "props")...we want the ability to generate connections between records that might seem unrelated...but connections we might also forget about. the categories are really "labels" that allow us to "tag" say a company that sells "Lighting Fixtures" is in the "D&D building" and mentioned developing a side business specializing in "Men's Accessories"...sometimes the connections are pretty obtuse...that said 10 categories is probably overkill...3-5 is probably the magic number...but options are always good.

obviously i'm open to any knowledge on how this might have been accomplished easier - or improved...(conceptually it seems pretty straight forward to me...baring my lack of fm skills...)

thanks again for the help!

patrick

[Razumovsky]

Hi Patrick,

What we are both reacting to is that in your current structure, you will have to search 10 fields to know all the catgories a resource belongs to. This translates to 10 times as much work everytime you want to do any operation that considers category. For example, a simple calculation to find if a resource is part of the "D&D building" category would be:

Case(

Cat1="D&D building"; "yes";

Cat2="D&D building"; "yes";

Cat3="D&D building"; "yes";

...

Cat10="D&D building"; "yes"; "No")

If you created a separate Table "Categories" with individual records for each category and related it to Resources table CategoryID=CategoryID, you could have Category ID be a single repeating field (10 repetitions) in Resources that would hold all 10 of your category ID's and you could view them all in a single portal. Then the above calc could be made:

Case(position("¶"&valuelistItems(get(filename); "RelatedCategories")&"¶"; "¶"&ResourceName&"¶"; 1; 1); "yes"; "no")

where RelatedCategories is a valuelist of all related categories from resource table.

(note that this is one of the few places where you might not get a lot of grief for using a repeating field)

Basically, as there does not seem to be any real difference between category 1 and category 10 (they both could be D&D building), the data will be much easier to manipulate and track if it is stored as separate records through a single relationship. Streamlining the relational structure now will save you a lot of work in the future.

[pfarrell]

hi Raz,

i was just discussing this with my father-in-law...he's not familiar with filemaker...but was making the same point you are...i'm getting it...but...if this is working as is, and the script LaRetta devised is really pretty simple, can you give me an example of what i might want to do with the db information at some point that would be seriously complicated, or impossible, under the current setup? granted i'm all for simplicity...but i also don't really care about making my computer work a little harder to give up the results...but that's probably just inexperience talking...

in the end i'd expect that we'd have 2,000 records or so...but we were just thinking that it might be nice to have a way to tag "favorites"...so if we were searching for "florists", we could view all records under "florists" that we also flagged as "favorites"...

anyway, thanks so much for the insight and help.

--patrick

[comment]

One thing, for example, that you cannot do without a join table is print out a 'yellow-pages' styled directory, where a company listed under two categories gets printed twice.

[Razumovsky]

Well, there is always the 'if it aint broke...' rule, but it appears that you are at the beginning of what might be a long design process. An extra hour or two spent in fleshing out your structure now can easily save dozens down the road. Additionaly, there are many ways to accomplish any one result in filemaker, but there are definitely prefered ways of doing things that take much more advantage of how the program 'naturally' operates. Addressing this small issue here might in the long run not save you much time (although I suspect it will), but a lack of awareness of the principles involved in this problem are the source of probably some %80 of the "Help me" posts on this forum (at least %80 of mine)- if you continue to develop in Filemaker, you will face the same issues again.

For example- your drop down list is much too long for my tastes, and I would think that your users would find it awkward as well. You have enough categories to merit at least a level of subcategories. Choose one category from a list of 12, choose one subcategory from a list of 1-10 that had filtered by the first selection (this is called a 'conditional value list' - there are many fine and detailed posts about them on the forum). It would be much more difficult to try this with your current structure.

we want the ability to generate connections between records that might seem unrelated...but connections we might also forget about.

An easy and powerful way to generate connections is to create a record of what connects two things, in this case, a category connects two resources. You do not really seem to want to connect a resource to other resource that share the same categories, you want to view the resources associated with different categories. This way you can tunnel easily from Resources-Categories-CategoryResources.

You can still use Laretta's GTRR script with this method. In fact, she pretty much set it all up already- stopping shy just short of adding a category table, but your 10 fields are completely redundant in her example - change the multikey to a repeating text field (you can use the same value list - but only select one value per repetition), relate it to a new Category table CategoryMultikey=CategoryName, delete your 10 fields and you are on your way.

[Razumovsky]

One thing, for example, that you cannot do without a join table is print out a 'yellow-pages' styled directory, where a company listed under two categories gets printed twice.

I have been thinking about this, and it would not be too tough to do using getnthrecord and a sliding repeating calc field. Pretty easy and fast for scenarios where the number of companies to category will always be below a set number, but not nearly as elegant or flexible as a proper join table which can take advantage of the native FM reporting features.

[comment]

It wouldn't be too hard to create a join table 'on-demand' just for the report, either. But what's the point? Kinda like buying a closet to put your stuff away for those rare occassions when you have company...