How to mass update one field in an existing table

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

We use a table to keep track of devices.  One field is the serial number of the device (Unique).  Another field is the date the device was last seen on the network.  This date is exported from another app, and saved in a .CSV file.  How can I update the Last Seen" field with the new date?

Photo of Jay Ginn

Jay Ginn

  • 152 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matt NZ

Matt NZ

  • 296 Points 250 badge 2x thumb
Hi Jay,
You have several options, the most basic would be to use the import the data from the clipboard. For this you need:
  • admin access to your QuickBase(QB) app.
  • Make sure the device serial number is the key field in your table.
Then just go to your table in QB, click more, Import/Export, then select Import into a table from the clipboard.
Copy the data from your CSV and paste it in the box provided. Then follow the instructions to get your data into your table (make sure you select the right fields).

Other options:
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
How far into creating / using the app are you?  

If the serial number is always unique, then you can set that field as the "Key Field" for the table, then any imports you do from the other system will always use that unique serial number and then the "Last Date Seen" will be updated during the import.

if you need more help of how to import the records let me know.
Photo of Jay Ginn

Jay Ginn

  • 152 Points 100 badge 2x thumb

Thank you for the quick responses.  They are good ideas, and the other app is a non-QuickBase app (so, no direct relationship...).  I have no problems Importing from a file or the clipboard when I want to create new recordsin a table.  The problem is that QuickBase wants to create a new record instead of overwriting the existing "Last Seen" field - even when I have set the, unique, Serial Number as a key field.  I have even tried importing where those are the only two fields being imported, and it still wants to create new records. Of course, it can't since the Serial Number is set as Unique. It gives me errors stating that the serial number already exists. Is there a way to keep it from trying to create new records every time?  A setting that I'm missing?

Thank You.

Photo of Avinash Sikenpore

Avinash Sikenpore

  • 162 Points 100 badge 2x thumb
Like Matthew suggested, you need to set that field ("Serial Number") as the key field. 

Other option is to map the record ID with Serial number and import using the record ID .
Photo of Jay Ginn

Jay Ginn

  • 152 Points 100 badge 2x thumb
Thanks, Avi.  Please check my 2nd post.  I have set "Serial Number" as the key field.  I have also tried using the record number as the key field.  QuickBase gave me the same response - it tried to create new records, but couldn't since they are unique.  I'm thinking there is some setting that I'm missing that allows imports to overwrite existing data instead of creating new records.  I just can't find that setting.  I can export the records from QuickBase and the other app to .CSV files, make the changes to the 1000+ records, and import the records back to QuickBase after deleting all of the records in the table, but that seems inefficient to me.  I'm looking for a more direct approach so the data is updated in the field in QuickBase without having to delete and "restore" all of the records.
Photo of Avinash Sikenpore

Avinash Sikenpore

  • 162 Points 100 badge 2x thumb
Jay, I dont think there exists a setting like that. Quickbase by default will update records if the key field exists. You may want to check if there are leading or trailing spaces in your csv data. use the excel formula  Trim(cell number) on the serial number field and try to reimport. I've had that issue of leading/trailing spaces. 

Second thing I can suggest is using google drive / box or dropfox folder and placing that CSV in that folder. Create a connected table and then do a lookup to your destination table. setup your csv connected table to refresh everyday (sometime after you place the new CSV file) and it will keep it connected. The sync to CSV is very effective and thorough. 
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
You might have duplicate Serial numbers in your csv file... Just an idea
Photo of Jay Ginn

Jay Ginn

  • 152 Points 100 badge 2x thumb
OK.... I'm a little embarrassed.  Turns out that I had *not* set the Serial Number as the Key Field.  I thought I was doing it, but it wasn't correct.  I finally got it set, and it's working as advertised.  I also checked my .CSV file for leading/trailing spaces, and it's clear (good idea, Avi).  Thank you all for your help.