Multiselect text to report

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • In Progress
Question from a QB newbie. I am creating a form for patient bags we give out to patients. I have it set up asking the following questions: Date given (date), Patient name (text), Contents in bag (multiselect text with each of the items- chapstick, icescraper, phone charger etc). The goal of this form will be to keep track of who has received bags but more importantly to track how much of each item we are using each month. For example, we put chapsticks in each bag. So for the month of October we want to be able to see how many chapsticks we've given out for ordering purposes. Does anyone have any ideas on how to make this work in a report? I am having issues getting the report to reflect that information. Is there a better way to set this up?
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb

Posted 4 weeks ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
You should not use.p a multi select field if you need that kind of analysis.

In stead you need a Many to many relationship setup with a new middle table.

One PT has Many items Assigned
One Item Master has Many Items Assigned.

You can then run your usage reports off that middle table.
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Is there any other way to set it up so that it's just one table? Sorry I'm new to this but that seems complicated/ above my understanding level. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Maybe. Can you tell me how many different items you are tracking?
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Probably like 11 or so.
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Maybe not even.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Ok. We will make 11 Formulas.


The last 10 will be just basically a copy if the 1st one.


This formula will count the number of chapsticks


If(Contains(ToText([my multi select field], "chapstick”)),1,0)


Let s get that working first and then copy it similarly 10 times. There is a button to duplicate this field.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
I did a slight edit for a missing )
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Ok- I don't have any idea how to do that/where. I can try to do some investigating but I really only know how to do simple forms/reports.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
No problem.

Just make a new field of type formula numeric and paste in that formula. Then correct the field name to be the field name if the multi select field.
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Ok, when I do that it says this error message

Formula syntax error

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
I can't see you screen from here :)_

Please post your formula.
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
I just copied yours, right?

If(Contains(ToText([my multi select field], "chapstick”)),1,0)

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Sydney,
The formula works like excel except that in excel you refer to cell numbers like A1 and in Quick Base you refer to field names.


You need to replace this

If(Contains(ToText([my multi select field], "chapstick")),1,0)

use the formula above as I corrected an error in the quotes,  but where it says in square brackets

 [my multi select field]

 You need to out in the name of your field that is the multi select field.







(Edited)
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Ok got it, so then I just repeat that with each different item? Thank you for walking me through this haha! And then how would I set the report up? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
OK, good for you.  yes, repeat 10 more times.

As for the report, i suggest that you make a summary report type.  it will let you configure the 11 count fields, and then group the rows by  say the date of the record and group by either month or week.

I have to run to meeting now, but I think it will let you summarize 11 values without hitting a limit.
Photo of Sydney Wessels

Sydney Wessels

  • 200 Points 100 badge 2x thumb
Ok- so I just tried it out with 4 values. This is what is coming up. I think on the right track, but I need to know how many of each item was used each month and this only does the total. I uploaded what the report is showing and the settings I have. Whenever you have a chance let me know if you have any suggestions for me haha, I'm probably missing something silly.


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
The fields to summarize need to be the 11 fields.  Hover to the left and it will let you add more fields to summarize.  Do not summarize the # of Logs


The Rows should be to group by month. 

Do not group columns by anything.
(Edited)