Subtitles of the Movie

So we're all set. Let's try it out. We'll enter a new record. We'll come down to the zip code and type in 91709, which is my zip code. To get it to work you have to simply exit the field. That can be done in many ways; click into another field, tab into another field, click to another record, click outside of a record, it's up to you. I'll just Tab and see it automatically looks it up and pulls that information in there. If it didn't have an exact match, a record that had 91709 on it, it would go ahead and look to the next higher value until it found a match. It'd go 91710, 91711 and maybe it finally found 91712 and that would copy the value over. So that's how that lookup Next Higher Value works. Now, there's also a feature for looking up on all of our existing records because this is an auto enter feature. It has not gone ahead and looked up all through all your records. There's already values in there because we typed them in but until we touch that record, for instance I'll show you on here, if I change this to 91708, it'll re-look up that value for just this one record. I don't want to go to each record and take the three off and put it back on to make that lookup occur. OK, that would be a lot of work. Not on 45 records but maybe you had 10,000 and so you may be thinking I'm a smart guy. I'll go ahead in here and go to Replace Field Contents and then use a calculation and refer to that same field zip code in here or postal code. And so it'll take the contents of postal code and put it into postal code. It'll essentially put the same thing on there, touch every record in the found set and it'll do that. Yes, that will work but let me show you the feature that is actually designed for this. You click into that field, you check your found set. We're looking at all record so we're working all of them and you simply use Re-Look Up Field Contents. It's that simple. You go ahead and look at that message, click OK and it's now touched all your records automatically for you and looked up the values again. So it's that simple. So we'll go back to this record and we'll actually go ahead and leave this here for right now because we're going to remove the lookups. The reason we're going to want to do that is because it's really Legacy code. What we have inside of an auto enter calc is newer code. It's more efficient and so I'd recommend using those. I've actually seen a little bit of performance issue so I don't actually use the Lookup feature at all anymore. So we're going to go in here and say OK, I don't want that to be a lookup value. I want it to be a calculated value and what we're going to do is we're going to go and pop this up and choose a related table here. We'll not see the related fields here. Double click on our City. It puts in Zips colon colon City, meaning that it's looking from Contacts and Zips. It essentially does the same thing as a lookup and that'll work great except it doesn't do our next higher value. So in order to do that we're going to have to come down here and find up a function. Now let's go ahead and lookup next. I'll get down there eventually and there's Lookup Next. You double click on it. We're going to take this and I'm going to cut and paste this into the source field and then we decide whether we want lower or higher and we're going to type in higher here. It's as simple as that. That's how you do the same thing. It's exactly the same functionality as Lookup but using current code from the current version. They had to program some scratch and do it a little bit differently. I found it to be more efficient. So we'll go ahead and click OK. We want to uncheck that and then we'll go to our State Field and do the same thing. Go ahead and go into Options, go to Lookup, uh, I actually want to uncheck that. Go to Calculated Value, go down to our State and just a note here. Woops, actually it's the wrong state. You got to make sure we go to the Zips first and then find the state. Now, this would actually work fine if there was one zip code for every city and state. But again, we have these ranges in there and that's why we have to do this but this'll work perfectly fine and be a simple, just like our auto enter calc we did for copying the billing information over to the shipping. But again, we need that lookup Next. So we'll come down here. In fact you can use Type Ahead here. So I'll make it faster. Double click on it. I'll cut this with the keyboard command, paste it there and then change this to Higher. There we go. Uncheck that and now it's all set up with the most current code, which is much more efficient and it should work exactly the same way. So let's try it out. Let's go ahead and type in 9 and we can see it pulls that over just the same exact way. You won't really notice any efficiency issues with the lookup on this small of a table but where it happens is sometimes on much bigger tables. So again, your homework is to go ahead and update all your records if you haven't done it, to get ahead and set up the auto enter calcs on the shipping fields if you want to do it also so you'll need another table occurrence. But you don't have to do that if you don't want to. I'm not going to do it on this example but if you want some practice, I'll allow you to do that.