Field change in connected table triggers field change in local table's corresponding record - possible?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have an app that uses connected tables and regular tables. My records are "products" in this situation. 

When work is done on a product, my vendor updates the status for that record/product to "complete." I have a table within my own app that more or less mirrors the vendor's table. The two tables have fields with matching values, which I'm hoping I can use to leverage a relationship. 

What I want to achieve is: when the vendor updates a product's status to "complete" in the connected app, I want the corresponding record in my app to update to "complete." Ideally, this would be a text value and not a checkbox, but I'm open to any/all thoughts on the best way to approach this. 
Photo of Adam

Adam

  • 730 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
If the Key field of your table, for example the Item number or even record ID# is also in the connected table, you can make a relationship where 1 of your records is related to Many connected table records.

Then just make a summary count of the # of Completed connected records.
Photo of Adam

Adam

  • 730 Points 500 badge 2x thumb
Ack. Just realized that the field in my table that I wanted to use as a key field is a formula.

I had to concatenate a couple of other fields to get a unique match the the corresponding items in the connected table.

Is there any way around this key field/formula field issue? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Actually there is.  Almost nobody knows this.  You can enter the API call to change the key field of your table to a formula field.  It is not really documented and to be truthful, I have not used this myself.  I heard about this at EMPOWER and have yet to have to opportunity to et answers from QuickBase as to how safe it is to use.  Perhaps Quick Base support can tell you.

The API call is here
https://help.quickbase.com/api-guide/index.html#setkeyfield.html

So you would go to the table in question and type this after temporarily disabling the need for Application Tokens.

I would suggest testing on a copy of your app first, but that could be difficult since you have a connected table.

https://target_domain/db/target_dbid?a=API_SetKeyField&fid=7
Photo of Adam

Adam

  • 730 Points 500 badge 2x thumb
Wow. This is great info. I will clone my app, test, and report my results. Thanks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Yes, Kirk Trachy mentioned it at EMPOWER at an informal session and we were astonished.  I appreciate the testing.
Photo of Adam

Adam

  • 730 Points 500 badge 2x thumb
Wow. Success! What a great trick. 

To wrap up the original question - I created the relationship (now to a formula key field) and summary as described. Then used my table's status field to reference the summary field. 

Thank you very much for the help. This was a huge stumbling block to automation. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Thx for reporting back with your success.

The concept is similar to the refresh key on a CSV Sync table where you can specify up to four field to form the unique refresh key.