Questions to help me understand Connections better:
SCENARIO 1:
URL TABLE:
CONNECTIONS: I connect this table with a “Clients” Table because each Client has their own website and their own URL and so I want to associate it with them. I have added a connection in the URL table where each URL links to one Client and each Client connects with one URL. That seems to make sense to me because URLs connected to a company can only have one associated URL.
*My understanding is that by setting up this connection it will be possible to always see the URLS associated with the Client. In the Client table I do not have a connection to URLS. Can someone explain what would a scenario where I would also add a connection in the Client table to URLS? *
SCENARIO 2:
I have a table URLS. The Display field is set to the field that contains the physical URLS. There are other elements in the table such as “Thematic Area” which get assigned when a record is created. All fine so far.
Now I have another table called “External” this table contains information that needs to be associated with each URL record. I have therefore added a Connection to the table URLS (many/many) as each EXTERNAL could be linked both ways to multiple URLS. I have also added a Connection to Clients as each EXTERNAL is associated with Clients.
I create a form and in that form I want to be able to add fields from URLS and fields from EXTERNAL at the same time. However when I select the data sources to create the form I will first select “URLS” and it will populate the form with those URLS, it will then give me an option to add more fields, but those additional FIELD will only be for the “Display field” set for the EXTERNAL table.
Do I have to migrate all the field information over to URLS from EXTERNAL to be able to edit a record set in this way?
For me that has been one of the hardest things for me to understand because I am used to creating tables, or fields and having access to them across forms. From what I read about database structures it is best to have small tables so when searches are performed it can be done quickly. Do I need to bear that in mind when building a Knack database?
The Clients table does not need a connection field to URLS because the URLS table already has a connection field to Clients. Only one connection field is needed to connect two tables.
You only need to add one connection field to create a connection between two tables, so which table should you add that field to?
For one-to-one and many-to-many relationships, we suggest placing the connection field on whichever table will be used or edited more frequently. This will affect where the connection field lookup will appear and make it easier to edit with a form view.
In a one-to-many relationship, in most cases, it is best practice to add the connection to the table with many connections. Or to put it another way, in a parent-child relationship, it works best to add the connection to the child table.
Regarding Scenario 2:
Clients don’t need a connection to External, because Clients are already connected to URLs which are connected to External.
Try thinking of it this way: A form is for updating/adding a record… and the URLs table and External table contain different records. If you want to update multiple records at the same time with a form, you’re probably looking for Record Rules, which allow you to update a connected record. For example, on the form that updates a URL record, create a Record Rule to update the connected External record.
Thank you for your feedback here, I really appreciate it. That last paragraph got quite intense but I am still learning so I will come back and digest that when I am more skilled.
You’re welcome! I’ve seen your posts around the forum. You’ve clearly put a lot of thought into Knack and have a lot of experience with similar platforms. I’m sure you’ll have mastered it in no time.
Looks like you have already been helped but the key thing to remember is that 90% of the time when connecting objects, the connection field goes on the child object. Always evaluate whether a many to many relationship is necessary.
When displaying connection values, pass these also to a text formula field if not blank and then, if different, set to a short text field to capture that connection value. This also ensures if a connection is broken or if you want to import/export connection data, you have a field that has it.
