Discussions

Expand all | Collapse all

Excel uploads with User Fields

  • 1.  Excel uploads with User Fields

    Posted 01-23-2020 15:30
    Hi, 

    I'm having an issue with a user field that someone could shed some light on. We currently upload data through excel csv and one of the fields is designer responsible. Our goal is use this upload data to link back to their employee table so that we can see all their projects or jobs in their employee table. 

    When we upload the data it doesn't connect the name to the user. We've been told it's not possible because Quick Base can't match text name to a user. I've thought about automation, dynamic rules, or even having a field that was a formula that read the name in our designer name text field and selected the appropriate user. Any ideas on how this could work?

    Thanks!

    ------------------------------
    Catelyn Atkinson
    ------------------------------


  • 2.  RE: Excel uploads with User Fields

    Posted 01-23-2020 16:08
    You can have a table of employees where the Key field is set to employee name.  Add a field to that table for the Userid.

    Then make that table a parent in a relationship to your jobs table that you are uploading to, and the name will match up with the Employees table and then it can pull down the user field as a lookup.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Excel uploads with User Fields

    Posted 01-23-2020 18:26
    Hi Mark, 

    Could you be more specific on how we would do this? We currently have a table of employees where the full name is the primary key. We have the employee table as the parent relationship, but when we upload to our designer responsible field which is a reference/lookup field to the employee table it shows the name as "Catelyn Atkinson" instead of pulling from the employee table. We are able to edit the record and it then connects to the employee table, but it has to be manipulated manually.

    ------------------------------
    Catelyn Atkinson
    ------------------------------



  • 4.  RE: Excel uploads with User Fields

    Posted 01-23-2020 18:32
    1. Is the CSV file a list of Jobs?
    2. And on the file there is an employee name?
    3. On your jobs Table is there a field for Employee Name which is the reference field to a relationship to the the Parent Employe table?
    4. Is that Employee name field the field you are mapping your excel Employee name field into?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Excel uploads with User Fields

    Posted 01-23-2020 18:37

    Hi Mark, 

    Hope this helps. 
    1. Is the CSV file a list of Jobs? Yes, we have a list of jobs that are being matched by what we call a notification number as the primary key. 

    2. And on the file there is an employee name? Yes, we have the employee name as designer responsible which is how we match it when the csv file gets uploaded. 
    3. On your jobs Table is there a field for Employee Name which is the reference field to a relationship to the the Parent Employe table? Yes, we use the same take designer responsible as that reference field to the parent table. 
    4. Is that Employee name field the field you are mapping your excel Employee name field into?
    This is the same field that gets updated in the csv file. 

    Thank you for your help. 



    ------------------------------
    Catelyn Atkinson
    ------------------------------



  • 6.  RE: Excel uploads with User Fields

    Posted 01-23-2020 18:39
    Is it possible that the EE name has trailing spaces in the upload file?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Excel uploads with User Fields

    Posted 01-23-2020 21:28
    Catelyn,

    Does you application look like this?



    When you load the CSV data to the Jobs table, the Designer Responsible must match 100% with one of the Employee Full Name records in the Employees table.   

    If it does, then the Job record will now know the User from the Employees table.  That obviously empowers reports and workflows where as the Employee name is for UI consumption.

    Where is your CSV data coming from? What system is generating that data?  Can you make it any richer with the Record ID# of the Employee?   As Mark mentioned characters before or after will break this trick.   "Catelyn" is not " Catelyn" is not "Catelyn " and so on.

    Another way to do this is email address.  If you put the Employee email address in both tables as an Email field, it will correct for trailing spaces on the import.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------