sorting checkboxes

[Ugo DI LUCA]

Hi all,

Anybody here know how to have the result of a checkbox field sorted alphabetically instead of being inserted according to the order this value was checked ?

[-Queue-]

You inspired me, I had an epiphany, and here is the result. It's almost too simple. Now, if you can improve on my 'Refresh' necessity, I'll be more than appreciative.

This is far simpler than Rich Coulombre's method too!

Anybody want to clean up the layout for me? I'm left-brained, after all, and just too excited to care about presentation right now.

Update: This file has been moved further down the thread.

[Ugo DI LUCA]

I JUST CAN'T BELIEVE THIS!!

Inspired did you say. Left brain for sure....

This will save me a huge time my friend.

Let me put 5 stars to this one, hoping that I'm not the only fool that thought this couldn't be done without a scripted calculation.

BTW, great site too Queue

[Lee Smith]

Hi Ugo,

Check this demo by my friend Don:

One Line Value List Display by: Don Wieland URL: http://www.dwdataconcepts.com

HTH

Lee

[-Queue-]

Heh, thanks. I'm kind of stoked myself about it.

It's all your guys' fault though. My brain hasn't stopped going since I happened upon this forum last weekend. I've soaked up more cool tricks in one week than I have in the last three years of developing. Smart Ranges, locking/allowing field access without using scripts and field validations, PreviewPalette. This site is the greatest FM guru collection of resources I've ever seen! I'm only happy to have been able to contribute something 'they' said was 'incredibly difficult'.

Now, I guess I should make this thing pretty and explain how it works a bit, eh?

[Ugo DI LUCA]

Let me add something here. I've read about 50 posts in different forums about this same request. Slam me if I'm wrong but noone never pointed to that. Even here some gurus left their "left brain" on that one as I could read.

So, really, care your presentation and this could be one of the best simple tip ever...

I'm using a checkbox for a relationship (I know ), and I had difficulties for the indexing as I wanted some exact matches and AB wasn't BA !

Again, great thanks

[-Queue-]

Do you think I should take it down until I get it professional looking?

[Ugo DI LUCA]

Lee,

yours saves the script process with a calc. OK guys, 2 solutions in less than an hour.

Though Don Wieland should look at this too...

My god. I'll keep the script as I use it anyway to set the Multikey before the GTRR.

Thanks to both of you. When is next Epiphany. And yes this Forum's great. See my excitement...

[Ugo DI LUCA]

Delete it (kidding...well may be not )

[-Queue-]

Fixed it. No need for script anymore.

I'm taking this down to tweak a few things first.

[Ugo DI LUCA]

Hey,

I wanted to send you a PM for that one as I came up with the same.

Even more. Add a ValueListText lookup from your SJonModification, and we've got it indexed for a suitable relationship.

Definitely made my day here....

[-Queue-]

The only problem is that indexing the lookup only gives you the previous contents of the field. You still have to do a refresh to show the most current data. There must be a way to do this! I don't think I'll sleep tonight until I figure it out.

[Ugo DI LUCA]

Yes, in fact, I forced a relookup on Mod Time at the moment, as it wasn't working as expected.

Though, as I need to index a calculation using your method, I'm with the script anyway, either a refresh or a go to field "stored" (on layout) and exit record.

I've spent some time on it. Still find it VERY easy to use, but definitely need some sleep too...

Ciao. Thanks.

[-Queue-]

Okay, here's the revised version. I left the result as a calculation, although you can make it a lookup. But due to the relationship/value list connection, it wouldn't update until you leave the record entirely and return. It appears to require a double refresh. I tried using a double-trigger, but that didn't seem to work too well. As long as you know the limitation (unless somebody brilliant can solve that part of it), I think it's useful and much, much easier than the alternatives.

Enjoy.

SortCheckbox.zip

[kenneth2k1]

Excellent. Just to have the idea proposed and someone with foresight to solve the problem is a great resource.

I've soaked up more cool tricks in one week than I have in the last three years of developing...This site is the greatest FM guru collection of resources I've ever seen! I'm only happy to have been able to contribute something 'they' said was 'incredibly difficult'.

Now our only hope is that the Powers that Be will never charge to be a member of these forums!

[Ugo DI LUCA]

Excellent.

Another proof if necessary that :

1. this Forum kick some asses

2. relationships can solve many problems

3. keeping an eye over eachone methods is a "Must Do"

Stay with us for a big while, keep the good job and Steve will see Advertisers coming naturaly their way for a Banner here.

You'll soon see how effective your solution is....

[-Queue-]

Sweet! I can't wait...for all of the above.

[Fenton]

Here is an alternative "stripped-down" version. It does the same thing pretty much, with fewer fields.

Has a tendency to go blank until you tab out, when you delete the 1st of two entries; but otherwise the same.

SortCheckboxMY.zip

[-Queue-]

Excellent! This is where my mind was headed this morning, but lack of sleep prohibited me from 'shrinking' my logic right then.

My god, if it isn't beautiful. We should win an award or something.

Now, if we can just find a way to index the result (in a separate field) it without needing a script to force a refresh, Ugo will love us forever.

You know, I think basing the calc on the checkbox was an even more brilliant idea than the one I had that started this whole shenanigans.

[-Queue-]

Well, I figured out a nice way to force FileMaker into an infinite lookup loop, which I don't recommend. But it could be a nice security feature. Instead of closing all the files if someone 'illegally' tries to get into them, just open up a file that hangs FileMaker.

Still no dice on the index though.

[CobaltSky]

Hello JT,

I had a quick look at the file you have been working on. I have mixed news.

On the one hand, auto-indexing techniques are not applicable in this instance. The reason is simple enough. A value list based on field values is itself built from an index. The ValueListItems( ) function is therefore presenting the results of an indexing process. You are trying to index an index.

Where this goes awry is that indices are updated when you exit the record, and calcs which are to be indexed are (necessarily) evaluated first, *before* the indices are updated. Thus if you try to store and index a calculation that contains an index (ie ValueListItems( ) targetting a value list that is in turn based on an index), you are forcing the calc to evaluate before the information (index) that it is based on has itself been evaluated. Not good logic.

However there is no reason why you would ever need to do this. Ie you don't ever need to index an index, because you can just use the original index for all the purposes that the new one you were hoping to create could have been used for.

So, in this instance, the original checkbox field is indexed and can be used on the right-hand side of a relationship. If you were to succeed in indexing the index, all you would have at the end of it would be a much less efficient way of achieving the same outcome that you already have in the form of the original indexed checkbox field. The new index would be in alphabetical order, but then so was the original one - which is how you were sorting the values in the first place.

Unless there was something else you were trying to do that has escaped my notice, I don't think you have a problem.

[Ugo DI LUCA]

Hi Ray and JT,

The sort checkbox trick itself was sufficient for what I was into.

Now, with all the exercises that JT has done (and Fenton too), I also tried to index the sorted value list using all workarounds I could know, but the best I could get was an index of all values, sorted, less the last one checked.

I believe this is confirming Ray's latest statements.

I did so many tests that I had to download 5 times the samplers as I was experiencing the continuous loop mentionned by JT, even without any "error message" of any circular calculation.

The need for a sorted index at right side is fairly simple. Having it done within a script isn't a problem though, as mentionned earlier, for my proper needs.

I was looking for a way to create a Multi-key that might be referenced at left side by any other compound-key, whatever the order the checkboxes were checked at left side or right side.

This "requirement" was needed for the last project I had to finalize before the holidays. It's now done and I'll soon release the demo here with my greatfull thanks (may be before the end of the week-end), which, from others, will involve :

- Sorting Checkboxes (by JT Hugues)

- Extract a Value from a List with MiddleWords (by Ray Cologon)

- Set Repetition (by Ernst)

- Create an Auto-Multikey (by Goran Djukic).

Now, if I may ask a few more things without "breaking" of this wonderful thread....

The project I mentionned needed the creation of a coupound Multi-key for a 10 values checkbox field. I had to use a workaround to get this key successfully indexed, but after doing such, the following ideas raised to my poor brain...

Even if not relevant to the job required. If more than 10 choices were to be indexed, whatever workaround I would use, the calc would break.

As this multikey is scripted, I therefore splitted the Key in 2 separate strings. When looking for Matching Records, I trigger 2 separates GTRR or show the results in 2 separate portals. Is there anyway to have it done in a unique GTRR or Portal Based Solution ?

Thanks for all the help so far. I hope this thread could help anyone with same case.

[Ugo DI LUCA]

Hi all,

As everyone on this thread is so quick to provide any solution needed, here's the demo file originated from the Project I mentionned, and that uses this clever tip in the Multikey calculation and script.

Now, I'm still looking for an issue for the "more than 10 checkbox", but this is more about a personal challenge

Here's the link to the demo called"CheckboxFinds"

Thanks again for the great help here.

[Fenton]

Regarding what Ray said, about the uselessness of getting an Index of an Index. That's true. But originally we were just trying to present an alphabetized list, in a field, of checkbox choices. Yes, it's just the index. But it's the index of only 1 Record.

But it has to be Unstored. When I first stumbled across this (a while ago) I was excited. I thought it could be a way to get true stored relationships. However, FileMaker just can't do that, and there's no way to trick it into doing something it can't do (as we all find out somewhere).

There are other things that can be done with this however. You can use calculations on the value list calculation field, to limit it to ranges of values for instance, using Middle, Position and PatternCount, etc..

I did this recently, to show the "previous" record of a relationship (not the last) in its own portal, dynamically, with no lookups, etc.. It worked fine on my machine, but it was so slow over a network as to be unusuable. I reverted to earlier methods.

[CobaltSky]

Fenton said:

...but it has to be Unstored. When I first stumbled across this (a while ago) I was excited. I thought it could be a way to get true stored relationships. However, FileMaker just can't do that...

I'm sorry, but I think this is missing the point.

Quite the contrary, it *has* to be stored in order to be an index in the first place. Since it is already stored and indexed, you can already achieve 'true stored relationships' via the original/existing index.

Whether or not FileMaker can re-store and re-index an existing index is beside the point because it makes no sense to do so in the first place - it is circular logic. Anything you might hope to do with a 're-indexed index' can be achieved without tying yourself in knots by using the index that already exists.

[-Queue-]

Thank you, Ray. That makes complete sense to me. I'm not quite sure why Ugo needs the sort indexed. I'll have to check out his file to figure that out.

I guess that the file Fenton and I developed is sufficient then. I think I'll post in on the sample file forum when I get the chance, and prove that it is easy to sort a checkbox field without even needing to know what values it contains, and you can add more items to the valuelist without having to modify any calculations.

Thanks for all your help guys!

[Ugo DI LUCA]

Hi JT,

Just an overview.

Supposing you have Value A,B,C,D or E in a checkbox field entered in this order

B

A

E

The Multikey generated will be

B

B A

A

B A E

B E

A E

E

On the left side of a relationship, I enter from checkboxes

A

B

and Use Case(Patterncount, checkbox, "A", A) & " "& Case(Patterncount, checkbox, "B", : &....

which lead to A B

A B doesn't match B A !

Ooops...I'm realizing that I may not have updated the sampler with this calc, as I was sadly using Substitute(checkbox, " ", "-" )

I'm gonna correct it immediately.

A-B

I could have worked on the right side calculation with If Patterncout(checkbox,"A") and Multikey strings,.....but I simply lost my brain with this calc while DJ's auto-entered Multikey was a real "treasure" to work with if I could figure a way to order the values.

Hope this make sense.

Please point my "bad logic" if it is.

Thanks.

[Fenton]

CobaltSky said:

Whether or not FileMaker can re-store and re-index an existing index is beside the point because it makes no sense to do so in the first place - it is circular logic.

But we are not using the normal Index of the field. We are using the value list items calculated from the values in a field in only 1 record. It is a relational value list (in this case self-related; but it could be any relationship).

Like other related values, it's obvious that it can't be indexed, and still update properly.

[CobaltSky]

I'm sorry, but you *are* using the normal index of the field. That's exactly what relational value lists do. They retrieve the normal index of the field via the relationship. There is no other index.

And like other related values, it's obvious that it can and must be already indexed, otherwise it could not *be* a related value, since all relationships in FileMaker depend on the indexing of related values. If anything occurs which stops the index from 'updating properly', the relationship ceases to function, period.

[Fenton]

I agree. There is only 1 index for a field. If you go back to the first post, the question was just how to present checkbox choices as an alphabetized list.

We used a self-related value list, based on the unique ID for the record, to get the indexed entries for only that record.

This is a subset of the index for the field in the entire file. It's not a different index. The results cannot be a stored value and still update.

That's all I was saying. I may have used a word "index" in a sloppy way.

[-Queue-]

So, would I be correct in stating that you don't want to index the sorted checkbox items; you want to index the multi-key calc that you can produce from it?

[Ugo DI LUCA]

Yep.

That's why I never added a word to this funky search about indexing it as soon as I understood that it was impossible without a script.

Even if I was the first excited guy to mention it after your first sample....

The script process is what I'm using and it works just fine.

(Thanks for all the help. So Queue is not your LastName....Ouf )