Forum Discussion

LanceLance's avatar
LanceLance
Qrew Member
7 years ago

I am wondering if there is a way to ""group"" like fields from a parent table and display them on a single form?

I'll try to be brief & clear:
I work in a Pharmacy. We have created a Quick Base program to import an excel spreadsheet of data which contains prescriptions and patients (among other fields). This data goes onto the Parent Table called "Records". We then assign these records to different employees. Employees then edit the record assigned to them and call patients regarding the prescriptions and capture notes/comments on further text/drop-down fields as necessary. 

The spreadsheet we import into Quick Base is divided up into rows by prescription name (each row also contains patient data as well). As such, there may be multiple listings for the same patient but with different prescriptions.

Right now, we sort and group the imported records by patient name and just assign employees multiple records for the same patient. When the employee works the record the employee has to go in and edit the first one and then come back out and edit the next one, etc, for each separate prescription. It would be nice if we could somehow assign the work to an employee but if there were multiple records for the same patient, all of those would display on the same form so the employee won't have to back out and complete another record, they could somehow complete both records on the same form. An additional bonus would be for the employee to fill out the fields on one record and that info will possibly perform API call to fill in the same responses to the other records.

Hope this makes sense?
APIs and custom code
Data import and export
Forms
  • DebbieTaylor's avatar
    DebbieTaylor
    Qrew Trainee
    7 years ago
    Christofer 

    Quick Base can do it.  I'll explain the basic steps below.  Feel free to contact me via the CloudBase Services  for more details.  OK, here it goes.

    1. Create a new table called Patients.  In that table, you will store information about each patient (eg first name, last name, patient ID).  Make the patient ID unique - e.g. each patient has an ID. No two patients have the same ID.  Tell Quick Base the patient ID is unique by checking the "Required", "Unique", and "Check Existing Entries" check boxes at the top of the Field Properties page for Patient ID.

    2. Create a new table called Employees.  In that table, you will store information about each employee - including their Quick Base user name.   Tell Quick Base the employee name OR email is unique by checking the "Required", "Unique", and "Check Existing Entries" check boxes at the top of the Field Properties page.

    3. Rename the table you originally built "Prescriptions."  Add a date field called "Date Filled."

    4. Create a relationship between the Patients table and the Prescriptions table.  One patient will have many prescriptions.  When you get to screen below, or a similar one, choose "Patient ID" as the reference field AND choose Patient Name as a look-up field. [ I can walk you through creating the relationship OR you can do with the help of Quick Base's explanations.  ]

    5. Create a relationship between the Employees  table and the Prescriptions table.  One employee will have many prescriptions.  I can walk you through creating the relationship OR you can do with the help of Quick Base's explanations.  When you get to screen below, or a similar one, choose the unique field you created in #2  as the reference field AND choose Employee user name as a look-up field.

    6.  Now, go to the form in the Patient table.  You may now be able to see all the prescriptions for that patient OR you may need to add some things to the form.  Happy to walk you through that in an email or phone call.  

    Debbie









  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    I think key issue is that the block of CSV that you import has data the should properly be part of the both the Patients and Prescriptions tables. For example let's say the CSV data looked like this:
    [PatientID], [PatientName], [Drug], [Quantity], [Date], [Notes]
    ID101, Jane, Hydrocodone,   30, 1/1/2019, lorem ipsum
    ID101, Jane, Levothyroxine, 10, 1/1/2019, dolor sit 
    ID101, Jane, Prednisone,    20, 1/1/2019, amet consectetur 
    ID201, John, Amoxicillin,   60, 1/2/2019, adipiscing elit
    ID201, John, Gabapentin,    60, 1/3/2019, Aliquam gravida 
    ID201, John, Lisinopril,    10, 1/4/2019, sollic tincidunt
    ID301, Mary, Atorvastatin,  20, 1/6/2019, Nam enim 
    ID301, Mary, Metformin,     40, 1/7/2019, mi mattis 
    ID301, Mary, Ondansetron,   10, 1/7/2019, vel molestie 
    ID301, Mary, Ibuprofen,     10, 1/9/2019, id porta 
    Instead of importing everything into one table, the first two fields [Patient ID] and [PatientName] should be imported into the Patients table while  [Drug], [Quantity], [Date], [Notes] fields should be imported into Prescriptions table and also be related to the corresponding Patients record.

    The way to accomplish this it to write a short script that reads the CSV data, groups it by [PatietID] and make two calls to API_ImportFromCSV - one to the Patients table and a second one to the Prescriptions table. 

    Any other procedure that does not first group the data by Patient before importing will not get around the problem you are experiencing.

  • LanceLance's avatar
    LanceLance
    Qrew Member
    7 years ago

    Thank you for your responses and help! Sounds like I have some work to do.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    It is not difficult - see these examples:

    Got CSV To Import?
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=280

    How To: Select File, Transform and Import ?
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=356

    In fact, with the new mergeFieldID parameter on API_ImportFromCSV it is a lot easier:

    Surprising Use For API_ImportFromCSV with mergeFiledID
    https://community.quickbase.com/quickbase/topics/surprising-use-for-api-importfromcsv-with-mergefile...
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    I should have mentioned using Underscore's groupBy() method which makes this sooooooooo easy to do. Once you read your CSV data from the selected file and convert it to an array of arrays you have something like this:

    var csv = [
      ["ID101","Jane","Hydrocodone","30","1/1/2019","lorem ipsum"],
      ["ID101","Jane","Levothyroxine","10","1/1/2019","dolor sit"],
      ["ID101","Jane","Prednisone","20","1/1/2019","amet consectetur"],
      ["ID201","John","Amoxicillin","60","1/2/2019","adipiscing elit"],
      ["ID201","John","Gabapentin","60","1/3/2019","Aliquam gravida"],
      ["ID201","John","Lisinopril","10","1/4/2019","sollic tincidunt"],
      ["ID301","Mary","Atorvastatin","20","1/6/2019","Nam enim"],
      ["ID301","Mary","Metformin","40","1/7/2019","mi mattis"],
      ["ID301","Mary","Ondansetron","10","1/7/2019","vel molestie"],
      ["ID301","Mary","Ibuprofen","10","1/9/2019","id porta"]
    ];
    With this one statement you can group by the [PatientID] in the 0'th column:
    var groupedData = _.groupBy(csv, 0);
    console.dir(JSON.stringify(groupedData, null, "  "));
    Now you have identified the three groups ("ID101", "ID201", ID301") by [PatientID] and can easily pluck out the information that should go into the Patients table and the information that should go into the related Prescriptions table.

    You might be able to visualize this better if you copy and paste the following JSON into a JSON visualization tool such as http://jsonviewer.stack.hu/


    {
      "ID101": [
        [
          "ID101",
          "Jane",
          "Hydrocodone",
          "30",
          "1/1/2019",
          "lorem ipsum"
        ],
        [
          "ID101",
          "Jane",
          "Levothyroxine",
          "10",
          "1/1/2019",
          "dolor sit"
        ],
        [
          "ID101",
          "Jane",
          "Prednisone",
          "20",
          "1/1/2019",
          "amet consectetur"
        ]
      ],
      "ID201": [
        [
          "ID201",
          "John",
          "Amoxicillin",
          "60",
          "1/2/2019",
          "adipiscing elit"
        ],
        [
          "ID201",
          "John",
          "Gabapentin",
          "60",
          "1/3/2019",
          "Aliquam gravida"
        ],
        [
          "ID201",
          "John",
          "Lisinopril",
          "10",
          "1/4/2019",
          "sollic tincidunt"
        ]
      ],
      "ID301": [
        [
          "ID301",
          "Mary",
          "Atorvastatin",
          "20",
          "1/6/2019",
          "Nam enim"
        ],
        [
          "ID301",
          "Mary",
          "Metformin",
          "40",
          "1/7/2019",
          "mi mattis"
        ],
        [
          "ID301",
          "Mary",
          "Ondansetron",
          "10",
          "1/7/2019",
          "vel molestie"
        ],
        [
          "ID301",
          "Mary",
          "Ibuprofen",
          "10",
          "1/9/2019",
          "id porta"
        ]
      ]
    }