Forum Discussion

JohnD's avatar
JohnD
Qrew Trainee
1 year ago
Solved

Converting delimited text into checkboxes

Greetings,

I have downloaded a publicly available spreadsheet from the FDA containing their warning letters and have imported into quickbase.  It has worked well but I am stumped on how to do the following.  Under their "subject" column, they list multiple causes for the warning letter.  I have been able to convert those causes to different text columns because they are delimited with "/".  However, what I think really would like to do is to create a checklist of all causes and then associate that back to the record.  That way, I think, it will be easier for me to analyze and report the occurrence of the causes.  

In case it matters, the order of the causes is not always the same.

The column in question (in the form in which it is downloaded) looks like this:

Subject
CGMP/QSR/Medical Devices/PMA/Adulterated/Misbranded
CGMP/QSR/Medical Devices/Adulterated/Misbranded
Medical Device Reporting/Misbranded

 

I presume it needs some sort of formula so I have tagged it that way.  Any and all help would be appreciated.  Including if their is a completely better way to do it.  

John

formulas

7 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    1 year ago

    Can you help me understand this?

    "However, what I think really would like to do is to create a checklist of all causes and then associate that back to the record". 

    Are you asking how you can have a master table of causes and then have the causes on the record actors a look up from that master table of causes? Or are you asking for a particular format to combine all the causes on a FTA letter and have them be available as a dynamic filter on a report? I am trying to understand your goal here 

    • JohnD's avatar
      JohnD
      Qrew Trainee
      1 year ago

      I think your intuition is correct.  I want to take the blob of delimited causes and find a way to easily get counts on how many times each appears in the data and also be able to filter on those.  

      If it helps, here is a screenshot of the CSV imported into excel

      What I am interested in is getting a good feel for the occurence of the various "subjects" in column E as those are actually the causes FDA cites in their warning letters.  So I want to be ato say things like "43% of FDA warning letters include Misbranded as a cause".  Or "In the prior two years, x, y, and z are the most cited causes for warning letters"

      I am a quickbase newbie so felt checklist might be the way to go but am open to any good solution.

      Does that help?

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        1 year ago

         several ideas but let's take this step by step. If you create a new field of type Formula multi select text with this formula

        Split(List(";",
        [Cause 1], [cause 2], [Cause 3],   .... etc,  [Cause 9]))

        then you will be able to use that field as a dynamic filter. It would not do any fancy percentage calculations for you but if there are not too many different causes then you could quickly filter your master list down and write down on a piece of paper :) how many came up on the filtered report as you cycle through each Dynamic Filter option in turn.

        Do you have more ideas but why don't you start by doing that and post back. 

        Also can you tell me whether or not you have access to a master list of the causes, or do you need to generate them out of the data.   

  • DonLarson's avatar
    DonLarson
    Qrew Deputy
    1 year ago

    I agree with Mark to split the data up.   My solution would look like this:

     

    Your ability to analyze things will be greatly enhanced with table in between your letters and the causes.