Skip to main content
Question

Multiselect text to report

  • October 25, 2018
  • 0 replies
  • 116 views

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?
This topic has been closed for replies.

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.

  • Author
  • Registered
  • October 25, 2018
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. 

Maybe. Can you tell me how many different items you are tracking?

  • Author
  • Registered
  • October 25, 2018
Probably like 11 or so.

  • Author
  • Registered
  • October 25, 2018
Maybe not even.

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.

  • Author
  • Registered
  • October 25, 2018
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.

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.

  • Author
  • Registered
  • October 25, 2018
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.



I can't see you screen from here :)_

Please post your formula.

  • Author
  • Registered
  • October 25, 2018
I just copied yours, right?

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


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.








  • Author
  • Registered
  • October 26, 2018
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? 

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.

  • Author
  • Registered
  • October 26, 2018
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.



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.