Hi,

 

I need the value list number, not the "Value" base on "A" number.

 

So, lets say I have a value list relationship of ID's ...

 

345

567

678

786

799

801

 

Value 799 is number "5" in the list.

 

I can get the value 799 if I manually enter "5", but I have a field where I've built a relationship that puts the ID of 799 into an ID fields base on the customer selection of drop down menus.

 

Based on this ID field ( 799 ) I would like to extract "5" from the ID value list relationship above.  And the following functions below don't seem to give me that option of extracting the "5" ( it would appear ).

 

 

ValueListItems ( Get ( FileName ) ; "Inventory ID" ) My value list of ID's

 

GetValue ( ValueListItems ( Get ( FileName ) ; "Inventory ID" ) ; "5" ) This get the "Value 799" not the list no "5".

 

ValueCount ( text ) ?? Not sure if I can use this or not.

 

Any assistance in this matter I would be grateful.

 

Thank you.

 

Tom

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

Try =

ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & searchValue & ¶ ; 1 ; 1 ) ) )

Since you are using ValueListItems to produce your listOfValues, each value will be unique and a recursive calculation is not necessary.
 

Hi Consultant :-)

 

ValueCount ( Left ( List ( Navigation Search One to Inventory OneActiveOne::Inventory ID ) ; Position ( ¶ & List ( Navigation Search One to Inventory OneActiveOne::Inventory ID ) & ¶ ; ¶ & Navigation Search One to Preference One::Default Showroom PicID & ¶ ; 1 ; 1 ) ) )

 

The above calculation works GREAT!!  Gives me exactly what I wanted.  Thank you.

 

What throughs me are the "1's" and the "List" function in this calculation.  Can you explain them.  Am I to assume that the "No." I wanted is embedded within the "List" function ??  Being a robot is OK but can you break out the calculation and explain how the List No. is extracted.  I would be grateful :-)

 

When I use the "List" function in a "Message" box, all I get is the value(s) of the list not the List No. Sequence.  The above calculation did the trick, again thank you.

Good lord, eos, that is one over-engineered custom function!

Here is an alternative, which is essentially the same as comment's. I like the way it's formatted:

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

 

The 1's are the start and occurence parameters of the Position function.

Not sure what you want to know about List, but here's the link.

can you break out the calculation and explain how the List No. is extracted.

 

1. What Fitch said.

2. Note that your implementation has to calculate the same

List ( Navigation Search One to Inventory OneActiveOne::Inventory ID )

twice; try the more efficient (and somewhat more readable) =

 

Let (
listOfValues = List ( Navigation Search One to Inventory OneActiveOne::Inventory ID ) ;
;
ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & Navigation Search One to Preference One::Default Showroom PicID & ¶ ; 1 ; 1 ) ) )
)

or, if you prefer, break it out to =

 

Let ( [
listOfValues = List ( Navigation Search One to Inventory OneActiveOne::Inventory ID ) ;
searchValue = Navigation Search One to Preference One::Default Showroom PicID ;

pos = Position ( ¶ & listOfValues & ¶ ; ¶ & searchValue & ¶ ; 1 ; 1 ) ;
shortList = Left ( listOfValues ; pos )
] ;
ValueCount ( shortList )
)

 

 

Being a robot is OK

 

Flattery will get you everywhere...

[…] and a recursive calculation is not necessary.

 

Good lord, eos, that is one over-engineered custom function! […]

 

OK, I got it! 

Hi,

 

I'm still confused here ... sorry ? 

 

Position ( ¶ & listOfValues & ¶ ; ¶ & searchValue & ¶ ; 1 ; 1 )

ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & searchValue & ¶ ; 1 ; 1 ) ) )

 

I read threw all the above possibilities and others before entering the forum, and when I stated that the "1's" through me, I meant that I could only getting the "Values" not the value "List No." when testing results ... so, when I saw the 1's in the position calculation, I still have the question.

 

Where is the List No.'s coming from Is the "LIST No." embedded within the "Position" functions?? And I'm I to assume that it's in the first position??  In my testing and reading FM Functions I could not produce a visual list that showed me the "List No." sequence, only the values, hmm?

 

Any assistance here I would be grateful.  I apologize for this lack of understanding here.

 

Thank you.

 

Tom :-)

I am not sure where exactly your difficulty lies. The calculation finds the position of searchValue in the list, then uses that to produce a short list. Using the example in your original post, the position of "799" in the given list (or more precisely, the position of "¶799¶" in the list surrounded by carriage returns) is 17. The first 17 characters of the list are:

 

345
567
678
786
7

 

and this short list contains 5 values.

BTW, the correct name for this result is "index",  not "LIST No." - see:

http://en.wikipedia.org/wiki/Array_data_structure#One-dimensional_arrays

Hi,

 

Sorry for the incorrect naming.  I can not test the file until after 1 AM due to user being on the file.  So I apologize for these annoying question ...

 

My trouble lies in how the "Index" is determined / extracted.

 

So when you say "Position" and you've enter "1" and "1" I would still get "5" values if I enter "1" and "2" in the "Position" function ?? if "799" was the search value.

 

This would yield 18 characters in the short list of "5" values.  Yes / No ??

 

Thank you.

 

Tom :-)

So when you say "Position" and you've enter "1" and "1" I would still get "5" values if I enter "1" and "2" in the "Position" function ?? if "799" was the search value.

 

This would yield 18 characters in the short list of "5" values.  Yes / No ?

 

 

No, you would get 0 from the Position() function, and consequently an empty string from the Left() function and 0 again as the result of ValueCount().. Since you have the Advanced version, you can open Data Viewer and test this yourself. Please read the help on the Position() function and the meaning of the start and occurrence parameters. Tom (Fitch) gave you the link in post #5 above.

Hi,

 

Me again.

 

Well after reading and trying to understand ... I didn't want to click the "Post" button but I am just not getting it!  Please forgive me here. 

 

Position ( "Mississippi" ; "iss"; 1; 1 ) returns 2.  So, the first "1" is the starting point and the second "1" are the number of occurrences of "iss" you are looking for. Yes / No ? So how to you get two when you are asking for "1" occurrence of "iss".  Strange ??

 

Position ( "Mississippi" ; "iss"; 1; 2 ) returns 5.  Have no idea how "5" is ascertained ??

Position ( "Mississippi" ; "iss"; 3; 1 ) returns 5.  Again, no idea how "5" is ascertained ??

 

Tom :-(  Yikes!

Come now, it's not that difficult:

 

Hi Consultant,

 

A ha! I see it!  Makes perfect sense now.

 

Without having an image of a bicycle already in my mind, for me, the bicycle is often invisible.

 

Now that I have your picture, I have my reference.

 

Thank you for your patience with me.  I am grateful

 

Tom :-)

Hi Consultant,

 

Tom :-)

WHO!

 

The user handle is located in the Black Area above their post. i.e. Snozzles.

 

Lee

Black Area? 

I do not see any Black Area, maybe you have a custom skin?

You can't see your name above your picture. On my skin it is black. What skin are you using?

I do not know the name of my skin, neither I know where can I change it.

The bar above my picture ( avatar ) is bluish.

Mobile?

 

The others all use a dark color such as black.

Default looks like this to me

 

Me too, Doug.  :-)

Me too.

 

@ Lee

Can I know WHERE ( which is the menu ) I can change ( eventually ) my skin ?

Here:

 

Hi Staff,

 

Here is a snap shot of what I see on my screen :-)

 

Again, thank you everybody.  Love this forum :-)

 

Tom.

 

Tom, that now make sense to me. 

 

FYI, the Forum provides a few different looks called Skins. These are just different browser view of the Forum. The Skin can be changed as shown in Doug's post, and with the different choices things can appear differently. Your skin appears to be the Default.

 

The forum allows you to modify the tag line, i.e. newbie to; consultant, lifetime learner, ChindoguMaker, etc. However, you can't do this until you have reached a minimum number of posts (I can't remember that number, but maybe someone else does).

Here

 

Thanks Doug,

not exactly in the most visible, nor logical place 

Hi Lee,

 

Thank you.

 

Tom :-)