Link tables by more than ONE field
S
Scott Snyder
Please create the ability to link a table to another table by more than just one field. Currently, as I understand it, a table can only be linked by one field (The primary key).
If we want to establish a link between Table A and Table B, we are limited to using a single field, the Primary Key field in each table. This is fine when the data in the linked fields, the primary key fields, is descriptive text. When it is descriptive, it can also serve a purpose when a user sees it when manually linking records. If I have an Invoice table and a Customer table, I may want to link the Invoices to Customers and when I enter the "Link to Customer" field in the Invoice table I see the names of Customers (along with other fields we can configure in that modal that pops up).
But in my use case, I want users to be able to see the customer name in that linked customer field when manually linking records. But I also import data from QuickBooks that uses RecordID values. The Invoice record includes a CompanyID value that is a numeric value. It isn't the descriptive name of the company. I would like to create a QBO_Comp_ID field in my Company table and then link it to the Invoice table using the value pulled from QuickBooks programmatically. But I also need the ability to deal with exceptions, so I want to be able to manually link records by use of the lookup that we have when using the current primary key in the Company record, the Company Name.
Now I believe I can link the tables with external automation like make.com, but it would be helpful to have this linking capability within SmartSuite and it could be achieved with the automation if an additional linked field was an option.
You could even require that any field to be used to link records to other tables must be a field that requires unique values (at least on one side. You do need to support one-to-many relationships).
This would enable much more flexibility and foster greater use of SmartSuite as a data repository / database.
Note this would also potentially increase use of automations which seems to be a motive.
(Edited for clarity)
Jon Darbyshire
Thank you for posting, Scott Snyder! I have a few more questions for you:
- Can you provide a specific use case where linking tables by more than one field would be beneficial?
- Are there any specific fields you frequently find yourself wanting to use as a link, beyond the primary key?
- How do you envision this feature impacting your workflow or the efficiency of your tasks?
S
Scott Snyder
Jon Darbyshire I attempted to clarify my post. Hope I didn't muddy the water. I have a sincere interest in exploring SmartSuite as a database and continue to struggle with how SmartSuite implements database functionality. Individual tables it does well, but how it provides database capabilities: relationships between tables and how referential integrity are maintained elude me. I understand that many, many automations can be created and managed to keep all the relationships intact, but it seems challenging for scaling up to support many relationships.
For example, I have many relationships already created and I have really just started building a system to manage our data. I can't create something that we can't manage. There have to be defined, programatic joins if we can expect it to support us as we add tables and records over the coming years. One way to do this is with a linking formula I've described elsewhere. The foundation of the work seems to have been done with the recent Get_List() function that effectively fetches records from another table based on values to use to lookup the matching records in the other table. The function then allows that fetched data to be calculated, displayed, etc. If I have a company ID field in my invoice table, I should be able to fetch almost any field from a related Company table because I can tell SmartSuite which unique companyID to go find and then it should be able to return any field in the table. I shouldn't have to manually link the record and I shouldn't have to rely on automation. Automation is for other tasks.
I've attended many Office Hours and they are great. But you know what? Most of the people I see on those hours are grappling with a lot of tables too. They are also trying to get their arms around a lot of data. It just seems to me that anything other than figuring out how to elegantly connect tables is low long term value effort. I readily admit that is just one uninformed person's opinion so I will be interested to watch what unfolds this year. I am happy to elaborate or clarify if i can be of any help. Best wishes.