get id from valuelist

[Jonas Löfvenmark]

Good day dear FM friends, I have hot stuck on something much trivial - something I though would be a breeze to set up, but apparently not. Please help, dear friends!

I thought I could easily find a function similar to GetValue but the other way around. With a valuelist based on cities::id to show cities::name I would need to do something like:

getValueListItem("cities value list"; "Paris") to return cities::id.

I understand I can use position to find the position in the list, but how about the corresponding id?

[comment]

Not sure I understand your question. Perhaps you mean something like this?

http://www.briandunning.com/cf/908

[Jonas Löfvenmark]

Found it! Thanks for your fast reply though.

GetNthRecord ( cities::id ; Position(List(cities::name) ; "Paris" ;0 ;1 ))

[comment]

GetNthRecord ( cities::id ; Position(List(cities::name) ; "Paris" ;0 ;1 ))

That doesn't look right: if your Cities table contains Amsterdam, Paris and Stockholm (in that order), then =

Position(List(cities::name)  ; "Paris" ;0 ;1 )

will return 11 instead of the 2 you need.

[Jonas Löfvenmark]

I see.... hm. Thought I had id! Kudos to your hawk eyes. Any idea on how to do this? basically an automation of what happends when I manually select a entry in a valuelist that is set to only show the second column.

[comment]

The CF I linked to earlier will do it. Using the earlier example =

CorrespondingValue ( List ( Cities::Name ) ; "Paris" ; List ( Cities::ID ) )

will return the ID of Paris.

The function is not recursive, so you could also use its formula directly in your calculation field.

[Jonas Löfvenmark]

Thanks! Will look into that. For now I came up with:

getnthrecord( cities::id ; PatternCount(Left( List( cities::name) ; Position( List( cities::name ) ; "Paris" ;0 ;1 )); ¶) +1)

[comment]

That's very similar to what the function does - except it includes protection against values being contained in other values.

[Jonas Löfvenmark]

Yes, that could be usefull! Thanks for input!!

Position( ¶ & List( cities::name ) & ¶ ; ¶ & "Paris" & ¶ ;0 ;1 )