Address Field Type - Use via Google Data Connector if connector field is constructed like address field?

  • 38
  • 2
  • Question
  • Updated 6 months ago
  • Doesn't Need an Answer
Address Field Type - Possible to use via Google Data Connector if a field is constructed with the address1,address2,city,st/province,code, country array structure?

I've kind of hoodwinked my tables with some complicated Quickbase Actions, a distinct table and looping relationships --

we use Google Drive Data Connector to bring in CSV files as tables. 

I'd like to somehow construct a field in the CSV that can then get converted into an ADDRESS type in the table.

I've scoured help but not seen anything. 

There doesn't seem to be a data type "address" within the Data Connector field definition process. 

Is there a way to do that. I realize I can do a manual import, but I'm looking for a data connector way to do this. 

Any ideas?
Photo of joe vandervest

joe vandervest

  • 104 Points 100 badge 2x thumb

Posted 2 years ago

  • 38
  • 2
Photo of joe vandervest

joe vandervest

  • 104 Points 100 badge 2x thumb
Figured this out on my own....

Here's the scoop ---

To do this I had to.....

1. Create a detail table just for the special address field
2. Using the google connector data create a webhook for new records added to the connected field that update the address sub-fields using field ID and write the values in them that we need. I have two relationships in both directions to the address table, so I write both key values needed in the detail table - one going, the other coming.    I bring the ADDRESS field into the main table via the reverse lookup relationship. 
3. I solved the State abbreviation through a relationship from the abbreviations I have to get state names. I'm thinking of expanding that by adding country code and then canadian provinces.
4. I used a Quickbase Action to update existing records in the detail table when something changes in the master. this allows me to address the address sub fields and fill them. 
5. you can use formulas in the master field to create fields that match to the address field sub-field expectations. 
6. just did some minor changes in existing data used by the data connector to force the QBA trigger to fire and thereby populate the tables. 

works just fine. 
Photo of Lee Gilmore

Lee Gilmore

  • 790 Points 500 badge 2x thumb
This might be exactly what I'm experiencing or similar. I have a Google Drive sync table that contains Address Fields similar to QB. However, the initial sync labeled these fields as Text fields. I'm unable to just change the field type to City, State, Zip, etc.

I decided I might add new fields and self populate them with data via an excel import/export but Quick Base disables the ability to import a CSV to a sync table.

If I understand your solution, I have to orchestrate an entirely new table to store and maintain addresses? Then use a webhook to populate the parent table based on the child table? I did get lost in your explanation.
Photo of joe vandervest

joe vandervest

  • 104 Points 100 badge 2x thumb
Sorry @LeeGilmore - I just saw this post. Yes - that's what I ended up doing. The inability to do much with the Address type field kind of ruins it's utility for dynamic database situations - especially when one is dealing with legacy data.  It would be nice if the "search function" for an address had some formula ability because then you could dynamically construct the address in the search field for the user to easily maintain, update. 

either way - I ended up using WEBHOOKS to do this so that I could keep the record ID's straight, and there two 1:M relationships -- with each being master and detail to one another.

There's a TON of technical debt with this approach.  I have a webhook triggered to create the address field table in Table B, and one to write back the related record ID when the record is created in Table B. 

It's very ugly. I'd like to be able to use Automations but they don't support the address type data field. 

You can't use QBA because they have issues accessing the same table. 

I looked into using a QBA/Automation to write back a record with the old style data fields pushing them into the Table - so I could get rid of the second table.  No Dice. 

It's is a pure PITA. And confusing. I'm thinking to eliminate all the complexity and tech debt - eliminating the ADDRESS type, the relationship, webhooks, and complexity by just sticking to the tried add true old fashioned data fields.