Is it possible to automatically populate a field based on a user's role in a bulk import?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

We have an app where different vendors will upload their workers to a table.

We give each vendor the same generic spreadsheet/template - purposely leaving out the "Vendor Name" field as it will be different for every vendor, and we also don't want every vendor knowing the other vendors etc.

When the vendor does the bulk upload of all their staff, we want each of the staff to have their field "Vendor Name" populated with whatever the user role of the person uploading is in.

This way, Vendor A uploads all staff into the table and all staff have their "Vendor Name" populated with "Vendor A" which is the name of the user role. Then Vendor B uploads all their staff and their "Vendor Name" is populated with "Vendor B", and so on.

Is this possible?

I have dynamic rules set on my main form to change the "Vendor Name" field "When the user - is in the role - Vendor A" -> "change - Vendor Name - A".

That's all good for adding individual workers but has no bearing on doing a bulk upload, it would seem.

Photo of Gavin

Gavin

  • 0 Points

Posted 3 years ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Yes this is possible. The simplest way would be to add a new column to the CSV file being imported with is a string representing the current user's role. This can be done automatically by calling API_GetUserRole to get the current user's role. There is a small issue in that some users may be in multiple roles but I assume you would resolve that issue or come up with some logic to determine which role should be used during import. You would have to instruct your users to use a simple custom build CSV import interface similar to what was done in my answer to this question:

Can i assign an id based on fields in a table being imported
Nancy Testimonial: "I see it now - thanks! and it is amazing"
https://quickbase-community.intuit.com/questions/1302318

I assume your users are importing a CSV or TSV file. if they are importing and Excel file you would have to use an additional library such as SheetJS XLSX to read the Excel file, convert it to a JavaScript representation, add the role column, convert back to CSV and finally upload it with API_ImportFromCSV. It may sound complicated but it is easy to do and the bulk of the code would be cannibalized from what was done in the above demo.

SheetJS XLSX
https://github.com/SheetJS/js-xlsx


SheetJS 
http://oss.sheetjs.com/

Feel free to contact me offline if you need help with this project.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
I think I have a simple solution but I will post later today.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Here is an idea.

 It does appear that each of your vendors to have separate user IDs in your application. Set up a table of users where the key field is the user ID. Then for each of those associates just have a field on that table which gives the vendor name.

 Now,  on the details table where the data is being uploaded to, create a field which is of type User and default that to be the current user. That is a field property setting for a user field. Call the field [User who uploaded]

Now make a relationship between the [User who uploaded] field and the User table to pull down the  vendor name in a relationship.

Note that when I say the User Table I mean a new at Table, not that User icon
Photo of Gavin

Gavin

  • 0 Points
Thanks a lot for this. I'm not super advanced on this side of things so, trying to piece together the script from the other answer is proving tricky! :)
Also, our end users can use csv's in order to avoid the steps to convert the file.
The end-users would explicitly have 1 role and they would be brand new to Quickbase so we're trying to make the process as simple as possible for them.
I'll try my best to figure this one out and get back to you - thanks again!
Photo of Gavin

Gavin

  • 0 Points
Thanks for this - this sounds doable so I will try this first to get my app up and running until I can figure out Dan's method! Thanks a lot for the help.