Discussions

Expand all | Collapse all

Multiselect text to report

Sydney Wessels10-25-2018 14:11

QuickBaseCoach Dev./Training10-25-2018 14:11

Sydney Wessels10-25-2018 14:12

QuickBaseCoach Dev./Training10-25-2018 14:25

QuickBaseCoach Dev./Training10-25-2018 16:00

  • 1.  Multiselect text to report

    Posted 10-25-2018 13:41
    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?


  • 2.  RE: Multiselect text to report

    Posted 10-25-2018 13:57
    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.


  • 3.  RE: Multiselect text to report

    Posted 10-25-2018 14:00
    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. 


  • 4.  RE: Multiselect text to report

    Posted 10-25-2018 14:11
    Probably like 11 or so.


  • 5.  RE: Multiselect text to report

    Posted 10-25-2018 14:11
    Maybe. Can you tell me how many different items you are tracking?


  • 6.  RE: Multiselect text to report

    Posted 10-25-2018 14:12
    Maybe not even.


  • 7.  RE: Multiselect text to report

    Posted 10-25-2018 14:24
    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.


  • 8.  RE: Multiselect text to report

    Posted 10-25-2018 14:25
    I did a slight edit for a missing )


  • 9.  RE: Multiselect text to report

    Posted 10-25-2018 14:32
    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.


  • 10.  RE: Multiselect text to report

    Posted 10-25-2018 14:34
    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.


  • 11.  RE: Multiselect text to report

    Posted 10-25-2018 14:40
    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.




  • 12.  RE: Multiselect text to report

    Posted 10-25-2018 16:00
    I can't see you screen from here :)_

    Please post your formula.


  • 13.  RE: Multiselect text to report

    Posted 10-25-2018 18:53
    I just copied yours, right?

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



  • 14.  RE: Multiselect text to report

    Posted 10-25-2018 21:07
    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.









  • 15.  RE: Multiselect text to report

    Posted 10-26-2018 12:45
    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? 


  • 16.  RE: Multiselect text to report

    Posted 10-26-2018 12:49
    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.


  • 17.  RE: Multiselect text to report

    Posted 10-26-2018 13:33
    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.




  • 18.  RE: Multiselect text to report

    Posted 10-26-2018 13:44
    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.