Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

Help with List-User field and reporting

I have 23 list-user fields in one table.  Before I go and create 460 formula fields to extract all users into a single field for exporting to Excel (I don't want a single field with multiple names) I wanted to see if anybody had a solution that would be easier?

I can extract the name with a formula, but to do that potentially 460 times will slow things down.

Thoughts?
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    This is very simple to do with script. You basically form a URL for your query and specify you want CSV output instead of NORMAL output. The URL will look like this:

    https://SUMDOMAIN.quickbase.com/db/DBID?a=q&qt=tab&dvqid=1&opts=csv.nos.

    In script you substitute semicolons for commas and you will then have a blob of CSV that will potentially contain up to 20*23 comma separated fields. But the fields are not going to line up all that well as among your 23 list user fields I would imagine there will be some that are not at the maximum of 20 users. So there are going to be empty cells in between full cells in your CSV and it will differ line by line.

    This is simple enough to reformat but I have no idea what format you want or what you are going to do to these users in Excel.

    Also, you are going to get a text representation of users in what may look like an unusual format such as these:

    da.ndiebolt@gmail.com <60149618.bamg>
    Dan Diebolt <57557593.cntq>
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      8 years ago
      That will work.  I can set up Excel to strip out the blank cells and format how I need.

      Our client will not have access to QB and wants reports in Excel for now.  Maybe that will change in the future, above my pay grade.

      Cheers!
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    I'm sure you have your reasons, but it seems odd to have that many fields like that on one table/form.  Why not have a joined table?  With a joined table you could have much better reporting capabilities and export options.
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      8 years ago
      All these fields are part of the Task table where the task is assigned to 1, 2, 3, ... people.  There are 23 various Tasks & Sub-Tasks that I have set up form rules to show only the relevant Tasks & Sub-Tasks. The number of people assigned varies based on need.  The list-user field is to make notifications easier.  I have tried a version with a joined table, but settled on the straight forward list-user.