Pulling a value from a list

[jcarson]

I've got a membership database, and I keep track of what years someone was a member with a checkbox.

I would like to see the last year everyone in the database was a member. Is there a way to pull the last selection from the list that has been checked off?

Thanks,

Jeff

[comment]

Try:

Substitute ( RightValues ( yourlist ; 1 ) ;

[jcarson]

Thanks comment. That worked. But it's led me to notice that when dealing with check boxes, it keeps them in the order that I check them off and not necessarily in the order of the list. For example, if I check off 2005 and then 2004, it will return 2004. Any ideas? or should I just be careful what order I check things off?

[comment]

Should have thought of that. This is a bit more complex, but there is a way - see attached.

MaxOfCheckBox.fp7.zip

[Søren Dyhr]

If say it isn't the newest/latest but instead the order that needs to correct, could it be done this way:

Case ( PatternCount ( Entryfield & "

[comment]

You're not thinking "seven'ish", as you would say.

FilterValues ( ValueListItems ( Get (FileName) ; "yourValueList" ) ; Entryfield ) ;

does the sorting. In this case we only take the last (sorted) value, but the entire field content is there, sorted.

[Søren Dyhr]

Yes I'm thinking pan-version'ish, and the aspect has nothing to do with 7.0. What if the order of the list isn't cronological - ValuelistItems( sorts mercyless!

Unless you apply the valuelists sortorder to the field as well.. in default. But doens't it make all fields sort like the list??

--sd

[jcarson]

Thanks for the help comment.

Also, you're right Soren about the problem if the list isn't cronological. But in this case it's easiest for me to just keep the list in order rather than having to update the calculation field every time I wanted add a value to the list.

Thanks.

[comment]

I must be missing something.

FilterValues("Put

[jcarson]

Exactly, so what Sorem's saying is if the list is out of date the return will be too. Let's say I put 2009 before 2008. It'll recognize 2008 as the last one, just like in your string it recognized "Any" as the value because it came last in the sequence.

[Søren Dyhr]

Filtervalues is definately the best! I was thinking pre seven'ish. But meanwhile did I toy with pulling the max value out, and came up with this weird idea:

Case ( ValueCount ( Entryfield )-1;Evaluate ( "Max(" & Substitute ( Entryfield ; "

[comment]

Let's say I put 2009 before 2008

I don't know how to reply to that, except to say that would be a mistake.

The desired sort order is part of the calculation. If the formula refers to a value list, then the sort order of the value list is the desired order. This is set once by the developer - and from now on, it doesn't matter in which order the user clicks the checkboxes.

It seems to me you're saying something like "Let's say I put a plus sign instead of a minus sign".

[comment]

Filtervalues is definately the best!

Yes, the more I look at it, the more I like it. There's great potential here, don't you think? Think of a problem like "the best 4 results".

[Lougee]

I have a similar situation -- tracking how many years students have been in our program. Instead of checkboxes, I created a table called ACTIVEYEARS with the fields 'studentID' and 'schoolYear' -- related to the STUDENTS table by 'studentID.' With a portal from ACTIVEYEARS on my STUDENTS layout, I can see exactly when each student was a program participant. I can easily expand the fields in activeYears to include awards, etc.

Seems much cleaner, not to mention easier.

Abbe 8-)