Solved

FM - Relookup Field Contents

Posted on 2010-11-09
8
831 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:rvfowler2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 34095300
Relookup will update all fields that have auto-enter values based on %INS, not the %INS field itself. If I understand correctly what you are doing, you want to have the value in %INS be the same as CAM most, but not all of the time. I suggest that you change CAM back to a calculation but add another field called "CAM_Override". Change your calculation to say

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.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34095328
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34095474
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:rvfowler2
ID: 34097055
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.
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 34097765
An Auto-entry calculation allows you to set the value of a field when the record is created based on any calculation you choose to define. For instance, if under certain circumstances you want the auto-entered value to be pulled from one place, but if certain other criteria are met, from different location. Or, you can use it to only enter a value if certain other fields are populated at all or in a particular way.

In the field options dialog, there is a checkbox that says: "Do Not Replace Existing Value of Field (if any)", which is on by default. If you uncheck this box, then anytime the value on which the auto-enter is based is changed, a new value will be re-calculated and entered over-writing the current one.

How is this different from just using a calculation field and why would you use it? If you have a calculation field that cannot be indexed because it references a field via a relationship, a global field or contains certain calculation functions that cannot be indexed, then you cannot use that calculation field as the child key in a relationship. The child key, the "To" field in a relationship, must be indexable, though the parent key, the "From" field, does not need to be. In addition, unindexed fields are much slow to search, especially across a network.

So, if want to use the value in a field as a key field, it must be indexed rather than a calculation. By using the Auto-enter calculation, and unchecking the "Do Not Replace Existing..." checkbox, you will wind up with an indexable field that updates but is not a live, unindexed calculation.

Keep in mind that the auto-enter will only update the field if the relationship is used in the calculation is changed. For example, you have a pop-up menu for selecting a Product ID. When a new product is selected, you also want it to populate a global Text field with the Category - call it Category_Selected - of the item so you can display in a separate portal, all items in that category, using the Category_Selected field as the key. You can't use a calculation field as the key because it's not indexed. By using the auto-enter calculation, you can populate the Category_Selected field using the Category value for the Product that was selected. Since this is an indexed field, it can act as the key back to the Products table (via a separate table occurrence of Products) to display all products in that category.

A simpler example might be a City and State auto-enter based on the postal code. This is assuming you a Contacts table with an postal code field and a separate table with a complete list of city, state/province and postal code.  If the "Do Not Replace..." is turned off for City and State in Contacts, each time you enter a new postal code the City and State will be updated. The City and State fields are local and indexed rather than references to the Postal code table which means that searches will go much faster and that you can overwrite them if you need to enter an exception.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34103042
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.  
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 34103054
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34103357
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
File Maker is not opening 4 572
FM - Efficiently Writing an IF Statement 3 265
audit trail 6 214
Adding a portal to a layout using existing fields 2 81
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question