Listing Checked Value Items in One Text/Calculation Field

[chikanyc]

Hi I have a database of records (songs) that for which i need to add keywords that describe each song, e.g. "melancholy", "dark", "romantic", "driving", "ethereal" etc. Each keyword has a checkbox next to it.

I want to create a field that lists out the checked keywords, e.g. "melancholy, romantic". Is there a way to make this calculation field? Thx:)

[Hare]

Try

substitute ( TextField ; ¶ ; ", " )

Edited May 23, 2008 by Guest

[chikanyc]

I don't think this address my problem...I just want my calculated text field to report back which keywords have a check next to them. Does this make sense? Thanks:)

[Hare]

That's how I understood the question... TextField is your field with the checkbox. If you check multi items, it looks like a ¶-delimited list like

melancholy¶

ethereal¶

intense

so then substitute ( textfield ; ¶ ; ", " ) will return

melancholy, ethereal, intense.

Isn't that what you asked for? Then please explain.

Edited May 23, 2008 by Guest

[Lee Smith]

Hare has given you what you asked for.

When responding when the help didn't do as you expected, you need to explain what you got that was not right.

In other words, what were your Results?

BTW, the Results should be Text, not Number as it will default to for a calculation.

You can always attach a copy of your file, or a mock up, when asking a question.

HTH

Lee

Edited May 23, 2008 by Guest

[chikanyc]

Hi Hare!

Yes it did work! Thanks! I was confused since I thought I had to put "Value List" somewhere in my calculation. I also have several value lists/fields with its own set of keywords, e.g. the keywords "Russian" "Middle Eastern" would fall under the value list and field entitled: "World"

I am attempting to combine the applied keywords from the various value lists to appear in one field. So I made two calculation fields based on my keyword fields/valuelists:

Field #1

List (World; Dark)

----Here World and Dark are its own fields with their own value lists ("World" and "Dark", respectively).

Field #2

Substitute (Field #1; ¶ ; ", " )

----

Field #2 writes out the list of keywords from both value lists & fields (World and Dark) but doesn't place the keywords in alphabetical order. Is there a calculation function that does list values alphabetically?

Thx!

[comment]

Why are you using a separate field for each keyword? That's not very efficient. If you want, you can use the same field with different value lists. It's still a lot of work, but I guess it looks nice.

You can use FilterValues() to sort your values. And you can nest functions in the same calculation, for example:

Substitute ( List ( World ; Dark ) ; ¶ ; ", " )

[Hare]

You can use FilterValues() to sort your values.

Hi Comment,

Interesting, I hadn't thought of that. But it took me some time to figure out. If I tested well, it will not work for a hardcoded value list. At least the method I created based on your suggestion makes use of ValueListItems(). The behaviour for a hardcoded list seems to be that it follows that order. If you create a value list based on field values, it will work, but if you have more than one value list such as the OP has, you need at least three lists (two conditional for selecting and a full list for sorting),

My idea was to create a conditional value list based on the selection in the field (or fields) (so relate record to itself by record ID) and use substitute on that.

Here's a sample. Please let me know if I misinterpreted your suggestion.

UseFilterValues.fp7.zip

[comment]

I think there are two separate issues here.

The first issue is that a checkbox field stores selected values in the order they were selected. You can use FilterValues ( ValueList ; CheckboxField ) to sort the selected values in the order they appear in ValueList.

FilterValues() doesn't care which value list you use, or how it's entered: it will work the same way with ValueListItems() or with hardcoded literal string, i.e. return selected values that appear in the list, in the order of the list.

The other issue is the order of the value list itself. Value lists based on a field are sorted alphabetically, while custom value lists (including hard-coded lists) are left unsorted.

if you have more than one value list such as the OP has, you need at least three lists

Yes, of course. Actually, the way I understand this, there is only one 'master' value list, split into multiple sub-lists. You want to use the master list for the final sorting. I think that would be a lot simpler than having a 'private' value list for each record - see the modified file.

BTW, I have streamlined your calc a bit and changed the matchfields for VL filtering to unstored calculations - there's no need to store all this information.

UseFilterValues2.fp7.zip

[Hare]

thanks for the elaborate response and modified file. good stuff to learn.

Edited May 24, 2008 by Guest

[chikanyc]

Thanks Hare and Comment!

I followed the file you uploaded and it seems to be working!! : :

[chikanyc]

I have a question when in FIND view.

I want to check off different keyword checkboxes, but the checkbox fields disappear and are replaced with "". How can I get the checkboxes to work in Find mode?

[comment]

Change the calculation fields that filter the values into global calculations.

[chikanyc]

That worked! Thanks so much:)

[chikanyc]

thanks for your help before...i have another question tho :

i wanted to make a layout that had a summary or a count of how many times i checked off a checkbox field value. is there an easy way to do this with the way have the checkbox fields set up?

thanks again..

[comment]

You can count related records from the point-of-view of the values table. However, you will need to add a "master" unfiltered relationship between the two tables, since a filtered relationship works only in one direction - see attached example.

CountChecked.fp7.zip

[chikanyc]

i will try it right now...thank you so much

[chikanyc]

WOW it worked perfectly, comment...thanks again!