FM - Relookup Field Contents
We have a field, CAM, that was equal to the field %INS via a calculation. However, once in a great while there are exceptions, so since you can't edit a calc field, I made it a number field with an auto-enter to Lookup the value in the %INS field. This wiped out the data in the CAM field as I expected (in my db copy). I clicked in the %INS field and went to Record:Relookup to populate all the CAM fields; however, I got an error message, "there are no fields that look up values based on the field '%INS'." This has worked for me before, so not sure of the problem, unless it is because both are in the same db. Thank you.
ASKER
Thanks for the suggestion, or I could simply script it so an entry in INS gets duplicated over to CAM. However, still unsure why Relookup doesn't work. I didn't want to update %INS itself, I put the curser there and clicked on Records:Relookup expecting all the CAM fields to poplate, but received the error code. Why is that? I've used Relookups successfully before.
Rather than using a lookup, just enter the referenced field in Auto-enter specify calculation. If you want me to take a look at the file you can upload it here or put it on an FTP site and post a temporary link.
ASKER
That works, so last question (Looked it up in help and it wasn't specific): How is the auto-entry calc different than making the field a calc field? I know you can't edit a calc field, so is the ability to edit the advantage of an auto-entry calc field? Will an auto-entry calc field always change when the field it is referencing changes or only on record creation? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, very thorough; better than Help. A further advantage to the auto-enter calc seems to be that it is not as sensitive as a calc field. For example, I have an email that is set off by the calc field CAPRATE, which is a calc in Real Estate of NOI/Purchase Price. However, I couldn't put a trigger on the calc field because they seem to recalc any time there is any change anywhere on the layout. The way I got around this is to run a script trigger on the Layout itself, "On Record Commit" and then compare the new CAPRATE to an old one I had stored. However, possibly I don't need all of this if I used an auto-enter calc. Will investigage.
ASKER
P.S. - Do you know of anywhere that I can get a free zipcode/town table? I couldn't find one, so just manually entered it from my state.
The layout level On Record Commit is, I think, an appropriate way to manage that.
Brian Dunning ( http://www.briandunning.com/zipcodesfm/ ) sells postal code databases downloads and subscriptions for US and Canada which include Long/Lat data as well as Preferred, Allowed and Not Allowed city names. You can also pay a bit more and get his calculations, however, I think if you look around you can find custom functions and examples that will do a lot of the calculations such radius search based on postal code (for which you need the Long/Lat data). The basic US postal code list without Plus4 is relatively small. The Canadian one with over 180,000 records is considerably larger.
Brian Dunning ( http://www.briandunning.com/zipcodesfm/ ) sells postal code databases downloads and subscriptions for US and Canada which include Long/Lat data as well as Preferred, Allowed and Not Allowed city names. You can also pay a bit more and get his calculations, however, I think if you look around you can find custom functions and examples that will do a lot of the calculations such radius search based on postal code (for which you need the Long/Lat data). The basic US postal code list without Plus4 is relatively small. The Canadian one with over 180,000 records is considerably larger.
If not isempty( CAM_Override ) ; CAM_Override ; CAM )
Then layer the CAM_Override field on top of CAM making it transparent and also change the behavior of CAM so that you can't directly enter it in Browse mode. If need to change the value in CAM, click on CAM_Override and enter the needed value which will be used instead of the calculated value.