Solved

FM - Relookup Field Contents

Posted on 2010-11-09
8
787 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
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Will Loving
Comment Utility
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
Comment Utility
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 24

Expert Comment

by:Will Loving
Comment Utility
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
 
LVL 2

Author Comment

by:rvfowler2
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 24

Expert Comment

by:Will Loving
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now