Connected Tables vs. Cross-App Relationships vs. Automations

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
  • (Edited)
My company is currently building out a new set of apps to transition into over the next 30 days. 

We've been running in the same application for roughly 6 years, and it has been great to us, but it has also been a learning process. After some research, we've decided to transition to a multi-app setup to address security concerns with many of our third-party vendors, and to address the performance issues we've seen by having an excessive amount of formula fields and relationships in one app.

We have separated our apps by corporate department and are in the table-building stages currently. We have one central app dedicated to company-wide reporting and central data management for the important stuff, and five department apps for day-to-day processes. 

I have seen a lot of conversation around connected tables and cross-app relationships here, but I'm wondering if Automations may be a more useful solution to some of our problems.

Right now, all of the department apps have connected tables pulling our Customers, Employees, and Companies from our central app. From there, each app has it's own tables for that teams needs (We are a contractor, so we have Inventory and appointment management in one, processing tasks and jurisdiction info in another, accounting data in another, etc). From there, each app will be sending data back to our central app for reporting purposes (how long it takes to complete tasks, big-picture accounting data, etc).

I'm having trouble identifying when to use a connected table to send the info, when to lookup the info in a relationship, and when to use an automation to update the info across apps.

I'm thinking we're likely going to use a combination of all 3, but I don't have a complete enough understanding of them to properly identify what will be best in each situation.

My only thought walking into it is that we will likely avoid relationships, because I suspect that will put us in the same performance issue we've been having in a single app. However, I also believe that intelligent structure design will solve this problem.

Long story short, what are the pros and cons of Connected Tables, Cross-App Relationships, and Automations in multi-app data transfer so that I can better design the foundation of our apps?

I understand that this is a huge question, so thank you in advance.
Photo of Jacob Bailey

Jacob Bailey

  • 134 Points 100 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
If you are in a hole now, my first advice is to stop digging!

By that I mean as soon as you do a cross up relationship or even a simple report link field without a relationship between two applications, they become essentially one giant application under the covers. So from a performance point of you you will contaminate the new application with the performance problems of the existing application and dig the hole deeper in terms of performance for the old application.

Connected Sync tables however allow apps to share information even if say the customer list is up to one hour behind, and not in anyway affect performance. Typically those type of master files like employee list and customer lists are really good enough to be refreshed every hour. in my own anecdotal testing, it also seems to me that Quickbase has tuned the Sync process to take a second priority to regular users on the apps. In other words even if the Connected tables Sync each hour there is no perceptible performance hit on either of the two apps that are getting Synced.

As for automations, it depends what you’re trying to do, but in a simple situation where you want one source of truth for say an employee table or a customer table, it is better to have users do their updates in the original app as they will be infrequent updates. And then let the Sync take care of it from there.

However if you do find that you need automations, I do not believe that they cause the two apps to become joined from a performance perspective.
Photo of Jacob Bailey

Jacob Bailey

  • 134 Points 100 badge 2x thumb
Thank you!

I have been extremely diligent in the transition to avoid taking any action in an app that will require processing from another. To this point, I haven't made a single cross-app relationship or automation. Our new set of apps are not connected to the old one in any way, I have pulled the data into the new apps using table-to-table field imports that are set to only run manually. With our main focus being performance, I've kept in mind your exact sentiment. If our apps do become reliant, then not only have I still brought back every performance issue, but now I also have our data segregated, creating the worst of both worlds. 

You have confirmed by suspicion and I will avoid cross-app relationships altogether. 

That being said, my thought regarding sync tables versus automations is that it's simply an assessment of when it would be best to pull data versus push data. Can you think of anything else to keep in mind while building them?

Also, I have noticed an issue in some of our first few tests that I haven't been able to fully diagnose yet. I have a sync table in a department app for Customers. In that app I have a Customers to Tasks relationship. The lookup fields in Tasks seem to clear themselves every time the Customers table syncs. However, when I preview the form I am viewing them on, all of the data on every record appears again. This is very strange and I'm not sure how many different actions allow the field data to re-appear yet, but I'm wondering if this is something you've seen before? (Maybe this warrants it's own post)
For that problem where the tasks are becoming detached from the customers, hopefully you have a field such as a customer number and you can set the key field of your new connected table to be the customer number.

That way when the record IDs get jerked around during the Sync process the tasks will still be connected to the customer number, which has not changed.

I suppose if you were desperate you could set the key feel to be the customer name but of course if you then change the customer name spelling for some reason in the original table then all of thetasks in the Connected application will become orphans for that customer
Photo of Jacob Bailey

Jacob Bailey

  • 134 Points 100 badge 2x thumb
The tasks are staying connected, I have a dedicated customer ID that's drug around with them. The problem is that the data in the fields is disappearing, and I just have to open the "Edit Form" screen to get it to re-appear. I don't actually have to make any changes, or edit relationships. 

When the Sync table refreshes, all of the lookup fields that rely on it are blanked until I go into the form's edit page. 
That is too weird a behaviour for me to diagnose on this forum. It does not make sense.  I suggest putting in a support ticket to have the Quick Base support team have a look.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 12,338 Points 10k badge 2x thumb
Hi Jacob,

I am a little late to the game but I would agree with Mark that if you are seeing the behavior you should put in a support case and the Care team can take a closer look at that issue with you and see what might be at the source. Also there is a high level resource that might be helpful when thinking about the different options available to integrate data across applications that was put out by one of the members of our Best Practices team. For those interested it is a good look over the pros and cons of the various methods and some things to consider when using each option.

https://community.quickbase.com/quickbase/topics/sharing-data-across-quick-base-apps-part-1-cross-ap...

I hope that information is helpful.