- Define a custom value list with 1 as it's only value.
- Make this value list the value list you select for your check box field.
- Resize the checkbox field so that only the check box is visible.
- A text field in the same record is used to display the value that the user is selecting when the click this check box.
Thanks. Just need some guidance now on setting up the report. I'm experimenting with a value list that has 2 choices--ChoiceA and ChoiceB.
I created 3 records in the "review" table and ran the script for each and made selections. In the related table with the value list entries, I now see 2 records for each entry in the Review table. So there's a total of 6 records in the values table--ChoiceA and ChoiceB for each ID.
If I select the "1" for ChoiceA in Review, the related record shows as "ChoiceA 1". Is that correct?
I tried creating a field to sum up the value list field but was unsuccessful--If ( value name = "ChoiceA" ; Sum ( value name ) ; 0 )
All you need is a summary field that computes the "total of" your check box field.
In a report based on your portal's table, you can add sub summary parts "when sorted by" the value name field and you can put the value name field and the summary field in this sub summary part.
You can remove the body part from this layout as you don't need it.
Now sort your records by the value name field and you'll see a report with a total for choice A and a total for Choice B.
Something's not right--
I created the summary field and it is displaying the correct sum for all selections when it's on the regular layout with the value list records. (It displays 4 which is the total of the combined ChoiceA and ChoiceB selections.)
I created the report [sorted by value name] with a [sub-summary by value name] that includes the value name and summary fields. The report only shows "ChoiceB 1" and "4" though.
The value name records show as "ChoiceA" or "ChoiceB" when no selection was made in the Review table and as "ChoiceA 1" or "ChoiceB 1" if they were selected. Is that right?
Thanks again for the help.
Are the records sorted?
Sub Summary layout parts are will not be visible unless you sort the records by the "sort by" field selected when you created the sub summary part
You need two fields in your records, one for the check box field, where a 1 is entered or cleared when you click the check box and the other is a text field where you enter the name of that value. From your last few posts, I'm not sure if that's what you set up or not.
Got it now--didn't have 2 separate fields. Thanks so much!
One last thing I'm stuck on--I'm creating a chart to display the results and can't get the sorting right.
The fields I have are value name, counter (populated with 1 when selected), counter sum (=total of counter) and counter summary (=GetSummary ( counter sum ; value name ).
I created a bar chart that displays counter summary on the X-axis using data from current found set with data points shown as groups. When I sort by value name the chart displays the data correctly.
I'd like to display the chart with the counts displayed in descending order. I've tried putting the chart in a subsummary part but that doesn't work.
No need for the calculation field with GetSummary.
When you sort your records, use the "Reorder based on summary field" option and select your Counter Sum field.
One thing I forgot about...when I had the value lists in a checkbox field, I had an email that sent out the values that had been selected. I need to be able to do the same thing with this new structure (i.e., values in their own table.) Is there a way to list the values that were selected for each record? I was thinking I could use an if statement looking for a value of 1 in the counter field but I haven't been able to set it up.
You can use the List Function to return a list of value names:
List ( relatedtable::valueName ) (Listed value names are listed with returns separating each name)
Substitute ( List ( RelatedTable::valueName ) ; ¶ ; ", " ) (turns the "vertical" list into a horizontal list with names separated by commas.)
Hmm let's rework that idea a bit. After posting, I realized that this would list all values, not just the selected values.
You can either define a relationship that links by ID and by the value 1 to get the above calculation to work or you can define this calculation in the related values table:
If ( checkBoxField ; ValueName )
And use this calculation field's name in the List function.