Link to home
Start Free TrialLog in
Avatar of maryj152
maryj152

asked on

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

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.
Avatar of Will Loving
Will Loving
Flag of United States of America image

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.

User generated image
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.
Avatar of maryj152
maryj152

ASKER

so what I would do is
unsorted, from bldg to cat, = IF (bldg::ID# = cat::ID#; cat::oclc = bldg::code;)
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Will Loving
Will Loving
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. Directions were very clear.