Every time I think I’m starting to make a little progress in Knack I seem to hit a roadblock. Is it possible to link imported pre-existing records? I’ve been reading the documentation and I don’t see any indication of that. Somewhere in the docs it says Knack handles PK’s and FK’s invisibly behind the scene when you add new records. What about pre-existing records such as in imported records. How do you link those? Or what if down the road you want to link Table A to Table B and you already have lots of records? There must be a way - right?
You can’t link to external data, you have to map the data on import. As you correctly state, Knack doesn’t use primary and foreign keys in the same way that Microsoft Access does where you have to set them manually. The Record ID is not exposed within the builder, you simply use connections and set the display field to show in the child table.
What happens when you have tables that are (or need to be) linked to different tables that you have yet to upload? Knack seems to be rather unforgiving in this respect?
This is more of challenge to make sure you import primary field matches with the connected table. I’ve not experienced any easier solution in other SAAS products or in the two decades with MS Access.
I’m no coder but there may be a script import that could be created…beyond my skill level 
You should think about the order in which you import your records before starting your imports. for example: if you want object ‘country’ and object ‘state’ to connect to object Location’ Then you’d first import the Country, then the State, then the location. This way you can connect the states to the countries before connecting the locations to the states and countries.
I recommend having an auto-increment on every object for many reasons but one of them is relevant here.
If you import countries and then export them as a csv. and then do a vlookup in excel on your countries data and then assign the country-auto-increment to the state rather than the country name text. When you import the ‘state’ and match via auto-increment # it will connect 100% of the time. I’ve found then when you try and match text , some portion of your import connections will fail. I’ve been using Knack since 2011 ( correction must have been 2013). These are just things that I’ve picked up over the years.
Thanks Justin. Yeah, I realized I need to start from the bottom up - e.g. import my lookup tables first. But more than that I would need to map out my entire db, all the relations and all the lookups. That’s a big job. We’ll have to see where I go with this.
Regarding auto-increment. Most of my tables are linked on autoinc. Trouble is, you can’t import a field as autoinc. You have to assign it as a number field. And so when it gets into Knack you have to create a new autoinc and then an equation field to put you in the right place (depending on the data), So I don’t know where that would put me in terms of matching link records if you wanted to export?
If you already have an the field imported that matches a field on the connection object but the connection is not there then you can connect them by exporting and reimporting them as an update with a matched field or by building a little integromat scenario to link them. I’ve found that integromat is a game changer. and reduces the need for custom js 10x. If you don’t already have an account, I highly recommend getting one. Good luck with your project 
1 Like