I have an application that has about 20 real users. When the user adds a record, they need to choose someone to assign the work to, and so they choose from a dropdown of users. However, the dropdown needs to show all 1800 of our quickbase account holders and not just the 20 who have real access to this application.
I am sure I could add all 1800 of our quickbase users to this application, but since new QB users are added daily, and removed daily, that would be horribly inefficient to have to manually add/remove them from this application daily.
I have tried adding @domain to the user list, and that is allowing the 1800 users to add records, but it does not allow them to be listed in the dropdown of users.
1. Create a new table for your 1,800 users with the following fields: Email (Text type), Description (Text type) and Username (Formula-User).
2. Formula within Username field would be: ToUser([Email]), then save this field.
3. Make the Email field the key field for this table.
4. Go to Advanced Setting in this new table and go to the "Record Picker" section. Set column 1 to Email, column 2 to Username and column 3 to Description then save your changes.
5. Add records (user's emails) to this new table. You can use the Description field to store details about the user, something your 20 users can understand and obviously the Username field would turn the email into a username Quickbase recognizes.
6. Now, go to the table you need to show the 1800 users from and create a new field called "Account Holder Email" with data type "Text".
7. Create a 1 to many relationship between your users table and the table you open in step 6 where 1 user may have many records related.
8. From the Details Table (inside the relationship settings), select the text field you created in step 6 as the reference field and then lookup fields should be Description and Username.
9. Go to field settings (field created in step 6), this new field should be set as Text(reference) by now, mark the option "Use a record picker instead of a drop down menu" and save your changes.
10. Create a new field within the app you need to show the 1800 users called "Account Holder Username" with data type "Formula-User", Formula in this field should be: ToUser([Account Holder Email]).
11. Finally, the field you will use for assignation is the one created in step 10.
If you go and play with the Account Holder Email field, you will see it says <browse choices...>, click there and you will be displayed with a new pop up for your 20 users to search among the 1800 users.
I just tested it and it worked for me. If you need further help, you might contact me at the email on my profile, I am a web developer based in Central America with a couple of years working with Quickbase and cloud based databases.
I hope you find it useful.
Can you tell me how your solution deals with this issue raised by "aimpulsive"
"I am sure I could add all 1800 of our quickbase users to this application, but since new QB users are added daily, and removed daily, that would be horribly inefficient to have to manually add/remove them from this application daily."