Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using FilemakerPro, How do you polulate a field in table B with values from a field in table C.

Posted on 2014-07-25
6
Medium Priority
?
492 Views
Last Modified: 2014-07-25
I have two tables related by ID#. I want to add a field in Table B and populate it with values from a field in Table C based on the ID#. I don't know if Calculate is better or Look-up. I don't expect the existing values in the field in Table B to change but more records will be added.
0
Comment
Question by:maryj152
[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
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 40219929
Hi Mary - In most circumstances I think it's preferable to use the Auto-Enter Calculation option over Lookup. Lookup and Re-Lookup can draw values from a related table but, if remember the ver early version of FileMaker, the Lookup was basically the predecessor of Calculation option and it kept primarily for backwards compatibility and a small number of specific uses.

The Auto-Enter calculation option is more flexible because you can not only specify a single field that you want to auto-enter a value from (which is what Lookup does) but you can also specify conditions, use If statements, include other field values, etc., anything you can do in the calculation dialog. So, for example, you might put in something like this:

If( not isempty( ItemID ) and not isempty( Quantity ) ; Quantity * Price )

This calc will only perform the auto-enter when both ItemID and Quantity have values.

In addition, Auto-Enter Calculation option also includes the ability to have the value be updated automatically if something changes via the "Do not replace existing value of field (if any)" checkbox.

Do not replace existing….
This checkbox is ticked by default, meaning that once the field has a value, the Auto-Enter calc will no longer be applied. If you uncheck it as above, the value in the field will be updated anytime one of the referenced fields in the calculation changes. This is extremely useful for when you need to have an indexable Number or Text field based on a calculation but the calculation itself is not indexable. Using the Auto-Enter option with the checkbox unchecked means that the field will be updated like a calculation field but will be indexed so it will produce quick results or can be used in a relationship.
0
 

Author Comment

by:maryj152
ID: 40219990
so what I would do is
unsorted, from bldg to cat, = IF (bldg::ID# = cat::ID#; cat::oclc = bldg::code;)
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 40220027
If the relationship is based on ID, then all you should need in the calculation is the name of the field in Table C that you want to auto-enter into the local field. I think the IF statement is superfluous; if the relationship is based on ID then if the relationship is valid the field should auto-fill, and if it's not it won't.

One additional tip: avoid using characters like # and other punctuation in field names. It can cause problems in calculations.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:maryj152
ID: 40220094
How do I fill in about 150,000 records in the Table C after adding the new field?
Thanks for the tip but that was not the field name, just used it to make question shorter.
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 2000 total points
ID: 40220137
You can populate a field for a found set or all records by using the Replace command form the "Records" menu.

1. Go to a layout that has the new field on it.
2. To do this on all records, select "Show Al Records" from the Records menu.
3. Click in the new field, and then select "Replace…" from the Records menu.
4. In the Replace dialog, click on the "Specify" button next to "Replace with calculated result:"   and enter the related field or calculation that you wish to use (the same one you set in the Auto-Enter)
5. Click the "Replace" button

Note that there is no "Undo" for the replace function. In this case you're using it on a new field that has no previous value, but if you use it on a field that does have a value, then be sure you have a backup in case your calculation is wrong. If in doubt, just create a test field first.

Replace dialogSpecify calculated result for Replace...
0
 

Author Closing Comment

by:maryj152
ID: 40220166
Thank you. Directions were very clear.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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