Help with List-User field and reporting

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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>
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb
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!
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Neil Shepard

Neil Shepard

  • 1,248 Points 1k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
I didn't test this but something similar to this should do the job:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=597
(Edited)