Value list Sorting ?

[Anh_Tran]

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

[Lee Smith]

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

[Anh_Tran]

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

[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

[Ugo DI LUCA]

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

[Anh_Tran]

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

[cjaeger]

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.

[Anh_Tran]

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

[Peter Fenner]

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.

[Anh_Tran]

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

[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

[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

[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

[Peter Fenner]

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

[cjaeger]

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

[Peter Fenner]

Hi Christian

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

Pete

[cjaeger]

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.

[Peter Fenner]

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

[Ugo DI LUCA]

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.

[Anh_Tran]

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

[Anh_Tran]

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

Anh Tran

[Anh_Tran]

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

Anh Tran

[Peter Fenner]

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

[Anh_Tran]

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

[cjaeger]

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

just delete the previous posts.

[cjaeger]

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

[Peter Fenner]

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

[Peter Fenner]

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.

[Anh_Tran]

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

Anh Tran

[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

[Ugo DI LUCA]

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

[Peter Fenner]

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

[cjaeger]

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.

[djgogi]

There is a workround however.

Check the attached file for the implementation.

Dj

SortingLists.fp5.zip

[cjaeger]

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.