Here I show an approach to being able to easily identify records that didn't match to connection records correctly when you do a CSV import into a Knack database app.
Interested in my Knack database services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave
If you are a fan of building great apps with Knack, please click the Subscribe button to join my channel, and ring the bell to receive notices on all my new instructional videos. Thanks!
Full Transcript
hey folks uh Dave Parish here at Knackbuilders do me a favor if you like these videos uh like it on YouTube and better yet subscribe to the YouTube channel this one's going to be pretty quick today here's the topic it's just a tip when you're importing records how to match connections and it's actually a trick if you're importing a lot of Records let's say you have a company it has contacts and you're going to your you're setting up your database and you're going to your and you used to have them on spreadsheets or different people have them in their own spreadsheets or contacts thing and you export them you aggregate them into a spreadsheet and you want to upload them but what you have to do is match the company I'll go through this in detail for the contact to your company's table and it has to be spelled exactly the same and if you're doing a lot of them you can do processes of trying to clean it all up but if you're doing a lot of them chances are you're still going to miss something and I'm going to show you a quick trick on how to deal with that after the fact when you end up with stuff that wasn't matched the company's blank and now you need to figure out get these signed appropriately to the right company thanks I'll be back[Music]okay here's the lowdown on this topicI'm just going to dive right into it I got aI'm using one of Mac's template apps and we have companies and we have a list of various companies and child records to that are context makes sense right each company or each contact is connected to the parent record a company based on this name now I mentioned before and by the way I'm doing this because I just had a client call the other day and he was doing a bunch of imports with the same type of thing a lot of usually you're dealing with a lot of Records it's just a handful which we have here for the explanation but you'll get the point so we have contacts now let's say that scenario I gave you you're going out to your sales people for example and saying give me a list of all your contacts and fill in this information so let's let's pretend we're doing that I have a list here now there's only three of them again this comes into play when you have hundreds if it was 50 of them you could probably just deal with it manually but you'll get the point here now here's what you end up finding and people are aggregating this from their own sources they're gonna have this here and you're gonna find this I see it all times this company called Corby this first one is called Corby comma and Inc second one's core Bay has a space in it and the last one is uh the correct one and by the way you have to put these first if you want to match them into companies so it has something to go find but in this example I'm gonna do an import of these we're going to see what's going to happen but here's what you doI'm gonna insert a column here's my trick and I'm going to call it what I'm going to do is copy the companies let's just do this and I'm going to read it we're doing a second thing I'm going to rename it special field I always put an asterisk in it and I'm not going to call it text okay I'm going to save that as a CSV and then I'm going to go over to my contacts thing and I'm going to do the same thing I'm going to add the field and I'm going to call it the same thing we're going to match two companies one's the connection one I'm calling this text company text and I'm going to put it right next to this thing you'll see why now let's go do an import Records here's all the contacts let's import records I'm gonna go get my aggregate list here I only have a few records but again in reality you'd have a bunch we're not matching it we want to put new ones each one and then here's what we're going to do we're going to do our our matching thing we'll do first name this is a whole other issue first name last name a lot of times you don't have those and the other sources but that's another topic company we're matching at this defaults right we're going to company and we're matching an accompany name which is what we want to do we're going to skip that and then this text thing it already matches it okay and we'll just saw the rest of the same let's do the import and see what happens foreign let's go look for himI'm going to redo this one let's just go look at company is blank these are the ones that mismatched we have two of them here again you'll have a bunch and let's just say we didn't do this company text thing you're going to in put these and these people the company is going to be blank you're not going to know who they are it's going to be a pain to figure it out you'll be calling emailing back and forth if we Double Deal this thing have the matching company and then put the text we have a reference to what that should have been and hopefully you don't have hundreds of these I've usually never do we have I don't know a couple dozen maybe that were matched improperly and so we're just going to do this manually this is the whole gist of my thing we know this was core bait but it just spelled differently so we just go here and go find Corby makes it really easy to find those outliers Corby now they're matched correctly after you do them all you can delete this you won't even need itI'm doing this through the back end but a lot of my clients don't deal with the back end and they don't want to so sometimes I'd go make a a special page that's temporary it's to fix it page where they basically see the same information and we already mentioned so there's no blank but it'll be you can have them all and they can scroll through or you can filter that table to only show blank connected companies and then they can just go one by one and do them I had one company that had actually a whole bunch of them but they just put some intern on it or maybe they even hired an attempt just to come in and do that it's a nice little trick I use it regularly when you're doing a lot of imports uh and that's the deal appreciate it thanks folks