Here's a short video on a trick I use to make finding fields for text formulas, especially fields in related tables, a lot easier. If you have ever used text formulas in your Knack database app, sometimes it can be confusing finding the related field that you want. This trick solves that issue and you can be confident that you get the right one every time. Check it out.
Interested in my Knack database app services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave
Full Transcript of this Knack Database App Post
0:00 Hey folks, Dave Parish here again, putting out another NAC functionality video. Here's today's topic and like and subscribe to YouTube if you can, I appreciate that. 0:11 Quick hack for text formulas. This isn't going to be a long video, but it's a trick I use, it's really helpful. 0:18 If you ever do a text formulas, it's really helpful. I deal with them quite a bit. Especially if you have a lot of fields in tables and you have a lot of connected tables. 0:29 Finding, here's a text formula, finding what you want can be really difficult. The way NAC presents these, and I don't know if there's a better way, especially in related tables, it's hard to find. 0:48 And I have a trick that makes it a lot easier because I could get confused on it and I spend a lot of time and you got to test and see if the right thing populates and then it doesn't. 0:56 Just make sure it's right the first time and keep on running. I'll show you what I do think.
0:00 Okay. Here's my trick for using text formulas. I think this will apply equally to regular equations where you can go get other values or fields. 0:12 But let's jump into this. What I have here, I do a lot of accounting apps. Here's an accounting app. And there's a bunch of naming structures. 0:23 This whole thing here is put together from multiple values depending on what's going on. Year, market, type of program, all kinds of stuff. 0:36 This whole program creates a budget for this organization. Annual budget and then invoices and expense items are put against this line item budgets here. 0:50 Now depending on, it's a government funded thing, depending on the type of grant, the year, the market, this program thing, program type consumer, we got other ones, we got a bunch of them, food service. 1:00 It doesn't matter. They all have special codes. So we need to put together the right code here, which represents this particular type of activity and this whole name. 1:11 It's all done through text formulas. And we also have to put together this thing called ledger code that the accountant uses and dependent on all kinds of variables. 1:20 It's changed and it's got to be right. When they were doing this the old way, people would be manually putting it in, needing to know the structure of it, and they'd get it wrong all the time and cause all kinds of problems. 1:32 But let's go a bit to the back end and I'm going to show you a couple things here. In terms of, I'll show you the back end. This gets really sort of complicated. 1:43 We're depending on all kinds of things. We're grabbing values, we're grabbing some parts of the value, we're grabbing, we're changing the year, we're putting all this stuff together using text formulas, and I'm going to show you one of these. 2:00 Go down to the bottom. This depends on the type of thing it is, what the ledger code would be. It's putting in static values, it's going to all kinds of different places within the app, and when I say places, I mean other tables, and going to get a particular type of thing. 2:16 And as I mentioned before text for me here's something of some behavior that I don't think is quite right within the app. 2:25 Notice this, if I create a text field here let me move this. Look at how many fields are here. There's a ton of them, and it's letting me get into all kinds of related fields too. 2:39 Now if I do a search, I'm just going to put something in here. Code. Okay okay. I'm going to go back. 2:48 Now, if I search again, or even scroll, this only gives me the native ones. So for me to get back to access to the connected ones, I've got to close out of the thing. 3:01 And come back into it. I don't know if they're working on that or not. I don't know if they're even aware of that. 3:07 But here's the deal. Let's say I'm going to get some code value. I can click in code, and as I mentioned before, or you can scroll, and that doesn't let you, by the way, if we in this particular table, or I'm looking at, if it has connected values, they do not appear as native. 3:27 You have to jump over to the other table and then find those value, because the value went there. But if I did, I typed in something like code and I'm looking at this stuff and it's like every time, maybe it's just me. 3:40 I get confused on what it is. And I end up having it just as frustrating. Here's what I do. Watch this. 3:46 Let's say I'm want to go get the code for a particular market. Here's a market. Every market has a code here that is used in a number of different places. 4:00 Now I don't want to fart around looking. Searching for this or dealing with whatever. What I'm going to do is just go to the code field. 4:07 I want to put a character in front of it that I hardly ever use. I just put it in. I'm going to go here. 4:12 I think you know what I'm getting at. I'm going to go back to my place that I want to do a text format. 4:19 I'm going to make one. I want to put the text format. Instead of scrolling, instead of looking, instead of not being sure, all I have to do is type that in. 4:30 And it goes right to it because it's a weird one. I usually don't start it with an ampersand. I got my thing. 4:38 I can save it. I can go do that multiple times with a different field. And every time I do it I'm going to immediately go back to where I did the identifiable one. 4:51 I'm going to make it correct it and put it back to how it was. That's what I do. It saves me a ton of frustration. 4:58 I hope that works for you. Thanks.
<video> Knack Database Video Text Formula Hack