Hi All,

I always have problems about value list in filemaker which limits to do alot of thing ... I have a list like this

DV0101

VD0101

VF0101

I want it sort like this

DV0101

VF0101

VD0101

It means I want value list sorting from number 0101 and I don't want it sort by letter first. IS there a way to achieve this filemer expert???

IS there a case that i can see a value list i want when I click it ... the data in that cell is different???

Thanks ALL

Anh Tran

Why would VF come before VD (bad alpha characters for examples). especiall when the numbers are all the same, or am I missing something here.

Lee

I always have problems about value list in filemaker which limits to do alot of thing ... I have a list like this

DV0101

VD0102

VF0101

I want it sort like this

DV0101

VF0101

VD0102

It means I want value list sorting from number 0101 and I don't want it sort by letter first. IS there a way to achieve this filemer expert???

IS there a case that i can see a value list i want when I click it ... the data in that cell is different???

Thanks ALL

Anh Tran

HEllo Lee,

I want to see the value list sorts is by number first not letter first.

that is why the result is above. Usually the value list sort the letter first but i want them sort number first ....i want to see number sorted each other not by letter. but when i click it i want to see the whole thing ...is means i want to see letter first then number after.

I am not too clear, I will try to explain again.

thanks Lee

Anh Tran

Well,

A workaround could consist in extracting the number from your key and store it in another field.

Then use the function "other value" and choose sorted by 2nd value .

Though your list would look like :

DV0101 0101

VF0101 0101

VD0102 0102

Would you be so kind this time to tell if the answers provided were helpful

OH DiLUCA ...

It must be my fault that i am not reporting what I have asked and what helpes me or not. The truth is all the answer from here did help me all the time.

For this example, i am going to try it on monday.

I am pretty sure to give an answer

Thanks alot DILUCA

Anh Tran

another work around myght be to just format the field the value list ist derived from as number, not text. That might cause other problems, though.

YEs cjaeger,

I got the problem about text ....for second field. Since I make a calculate field for second field as text then when I click the field, I see only second field and it does not take value first value. Is there a case to solve the problem , when i make the second field as number I saw alot of nil.

I really need help

Thanks All

Anh Tran

The second field would have to show the value in reverse. ie: VF0101 would have to display as 0101VF in order to get a sort as you request. The calculation (text) to create this reverse field is:

Right(TextToNum(1 & code), Length(TextToNum(1 & code)) - 1) & Left(code, Length(code) - (Length(TextToNum(1 & code)) - 1))

Unfortunately, I can't see how you could get the sort order you request without being forced to show this second field for sort purposes.

The only other option, if appropriate for your solution, would be to display the list in a portal via a relationship with the appropriate sort order. You could then create a script that would use the value selected in the portal list.

Hello Peter,

I got your idea but when I click sort by second field, I only see the list of second field and sometimes i see the list of first field and second field. It is really weird to me ...in my case the value list is used in portal, is that an advantage, FM is really limited for me.

Thanks

Anh Tran

hello Peter Fenner

I try without portal it works fine ...but the value list is in portal wich have the relationship with main file then I only second field or sometimes first field ...why is that happen ...do you have an idea how to solve it?

I really need help now.

It is trying me crazy.

Thanks

Anh Tran

Yeah, it sorts by second field but I only see the second field when i click in the field, I could not see the first field. It is too weird to me ...

Somebody can help me??

Thanks

Anh Tran

Ok

I did figured it out already. It works but not the way i want. i want to sort second field ...but can i hide some extracted string from second field ? ..it must be weird.

Thanks Anyway People

You helped me alot

Anh Tran

No, you can't "hide" the second field.

PS: Filemaker really should allow one to hide the second field and they really should allow one to use the sort order that is determined in the relationship (if used for a value list) - I would imagine this is an elementary aspect lacking in Filemakers incredibly poor handling of value lists.

Pete

try creating another value list with sorting based on the first value list ...

Hi Christian

And how would one do that? It ain't possible, right?

Pete

or create an unstored calc field with design function ValueListItems(Status(CurrentFileName,"your value list") and base your new value list on this calc field.

I haven't tested it, but unless I am missing something fundamental, I just don't see what you are achieving here. The new value list based on your other value list will produce the same result. If it is the basis for a sort order then the result will be a mess.

Have I lost the plot?

Pete

Hi,

How could it work within related records if it is unsorted anyway ?

I too didn't tested it, but I cannot see the process.

Thanks for clearing me up on this.

hello cjaeger,

could you tell me more about ....creating another value list with sorting based on the first value list???

what do you mean by this???

thanks

Forget what I said the post above since I am lost as other people

Anh Tran

I have tested it but it gives you the same result as sorted as first field.

Anh Tran

Af far as I know you cannot achieve the list you require without showing the second field (which is also your sort field). You will need to create the second field (for sort purposes) as I explained earlier. There is just no way around this.

The only other option (it's not a great option) would be to have the value list just showing the list of your number in reverse (ie:0101VF). You would then have to create relationships to get the real value into your record. If it is not a problem for users to select the number in reverse then you will be able to find a solution.

Peter

Hello Peter,

I have done what you said ...and i got it ..it is just i don't want to know the first one and the reverse one ...that works but it makes me feel not confortable, so i try the way of Christian using calculate field (Valuelistitems) and base on cal field but it does not work.

Anh Tran

you are right. doesn't work as expected ...

just delete the previous posts.

Well, there is a work around which might work for you

simply make the field a number field.

But caution. Some letters may be translated into Numbers:

Y es, N o, T rue, F alse

AND: Your numbers have to be UNIQUE

any duplicates will NOT show up ...

AH0101

BH0101 => AH0101 only

Hi Anh

As I see it you have 3 options:

1. Give up trying to achieve what you are trying to achieve.

2. Allow users to enter the code in reverse and make plans to get the real way into your record.

3. Allow the reversed field to shows as a second field and feel uncomfortable.

Life just isn't easy!

Pete

Sorry Christian it just won't work. If you have 2 results (or more), such as VD0101 and VF0101 - only one will be in the value list. The text parts are effectively ignored.

Keep trying though! I really have.

Pete

PS: OK I see you have edited your post.

I give up ....I tried but still does not work ... FM has some limitations

Anh Tran

Peter ...

ihihih I used the reverse way ...something better than nothing .... That is true life is not easy ...

You guys make my day

Thanks All

Anh Tran

Hi,

Well if you're OK with the reverse method, that means that you did reverse the ID and found a way to retrieve the original one.

If all your IDs are DV0101, VD0101, VF0101, that is 2 text + 4 nums,

then you could also have worked this way :

c_textPartofID = Middle(ID, 1,2)

c_numPartID = Right(ID, 4)

Then use a new value list based on c_textPartofID and sorted by c_numPartID...

You will end with :

DV 0101

VF 0101

VD 0101

which is closer from

DV0101

VD0101

VF0101

than

0101DV

0101VF

0102VD

Hi Ugo

All very well, but now you are only entering the 2 text characters when using the value list to enter data.

Not only that, but when you sort by the second field, and their are duplicate values in the second field, the value list will only supply one of the first field values, even if the first field is different. Its hard to believe, but it's unfortunately true.

Pete

there simply is NO way to do this via value lists in FM <=6.04. You will have to come up with a clever layout method/script to simulate a value list using portals(?) or similar.

Btw, I just does not understand why anybody would sort based on the tail of an id, which is not even unique .... sometimes you just have to get your specs right, then the problem disappears by itself.

There is a workround however.

Check the attached file for the implementation.

Dj

SortingLists.fp5.zip

Hi Dj,

unfortunately the file does not do the job. He wanted to show the real key (and ONLY the real key) in the drop-down, AND have it sortet by numerical value of last 4 chars, then alpha-sort of first 2.

1 -Your sample shows sort key + realkey.

2- sortkey calc is not flexible, you have to assign a value for any combination of A-Z&A-Z to make it work with the full range of capital letters - that's 26^2 combos ...

cannot see any improvement over a sortkey consisting of ####LL. (Right(key,4) & left(key,2) + combo-value list.

------------

I have tried another approach, which then proved to work only with a num value up to 0050:

Value List would be sorted by a second field sortfield=Left("....... this is a ton of spaces ......" ,TexttoNum(Right(key,4))

Even tried this using the ASCII 0 character instead of <space>.

Unfortunately, FileMaker did not show the other values in the list when there were too many spaces. And the ASCII 0 and other invisible chars did not work out either, only the first value was shown.

Here is about the only trick that will work:

transpose the characters & numbers in sortfield to a high ASCII value which sorts OK, but use a FONT that consists of only the caps letters A-Z and numbers 0-9. it should not show anything for other chars, not even a pixel.

Hi,

Well, there seems to be another solution...

I could have posted the test I've made but I thought it would have been somehow unrespectable as it is a direct adaptation of Ray's GroupRanking files.

In his file, each customer, part of a group is given a rank based on an amount. While I would have thought this wasn't feasable without a script, or some kinds of previously defined relationship, he just prooved the contrary by indexing the amount using some other keys...and the smartrange technique...

Now, just using his file, I concanated the Group and the Amount into a text field I called ID, and used a value list showing these new text IDs.

It worked like a charm without involving any other sort key.....

What can be done therefore is to extract the num part of the ID and use it with Ray's Ranking method, then reuse this index number with the text to keep the ID Unique.

For those interresting who would like to test it, and /or interrested by some other clever approach of ranking, check this thread where the solution was given, and from where you can download the GroupRanking sample.

Ray's GroupRanking

Right, I didn't follow the thread , I've just gave it an quick look.

So in case the requests are those you've mentioned the methode i've suggested is not appropriate.

Dj

Thanks All

You guys are expert. I have learned alot from you guys since I feel myself very limited in FM.

Thanks All

Anh Tran

Well,

Here is the sample working, waiting for Ray's comments and accurate explanations.

I felt that if the numerical part of the ID was indexed, you could recover the entire ID as an indexed ranked ..... but I'm not "smart enough" to give the explanation about these "smart range"...

VALUELIST SORTED.zip

Ugo,

I'm afraid that the adaptation you posted is not working as you think it is.

For example, if you enter more than one record with the same number value (but a different text prefix) only the first will show up in the value list.

This is because the sorting of the list in the file you've posted is controlled by the fact that the calc which concatenates the ID component values is defined as a number field. It has nothing to do with the other ranking calculations which also happen to be sitting in the related file.

I note that the first suggestion Christian made was that the field be defined as number, and the flaw in that approach was subsequently pointed out.

FileMaker, as it stands, provides better control over value lists and value list sorting than many other programs, but the options it offers are predicated on the assumption that users will want to provide an intuitive interface. When someone requests something which is somewhat counter-intuitive, such as the requirement under discussion on this thread, an alternative mechanism for selection of the related value would be more appropriate.

Rather than attempting to make value lists sort in improbable ways, I would be inclined to recommend instead, that Ahn use a pop-up 'dialog' selection window which presents a portal listing in a layout in a related values file (scripted to lock the window as a dialog, prompt the user to click on a value to select it, and automatically dismiss the pop-up, place the value in the appropriate field of the current record and return the user to the original file as soon as a selection is made).

Hi Ray,

My apologizes for both the use of your files and the bad use of your files

I was so convinced....that I just forgot the obvious, though Pete already pointed this out.

Well, I retain that your ranking solution is awesome, and I'm pleased if more people may have discovered it while following this thread