Hi there,
I am trying to have a value from a related table populate a field. The tables are related based on related field, but I would also like to grab another value from the related table and populate the field of another table. How can this be done? Look up values are not working for me, I only get a blank value, do I need to calculate this?
Cheers,
Travis
If you have a field that you have defined as an auto-enter looked up value, and you created that with there already being pre-existing records, then it is quite likely that you will not have values in that new field. This relates to the way Auto-Enter options work. A helpful explanation is found here: Calculations and Auto-Enter Calcs | A Filemaker Miscellany
You might need to decide if you want the value in the new field to update automatically if the value in the related table updates, or not.
It is just two tables. They are related by one field. I want one of the related tables to grab a value from the other table, the field it will grab will have a value entered by the user when a new record is created. I've tried the lookup option, but it only returns a blank value, do I need this to be a calculation and if so, how?
Hi Travis. A calculation will do it well. Currently your field type is probably text or number with an Auto-Enter option to insert the looked up value. Change the actual field type to calculation. Then the field in your first table will always reflect the value from the field in the second.
Hope this helps. Chris
Wonderful! I see where you are going with this and it makes much sense.... However, what calculation action should I use? The value I want to get is a string/text, should I use a GET function?
Hi Travis. You don't need a get function or anything like that. You just define the calculation as = TableB::Value (with Value being whatever the name of the field is). Make sure you specify the result of the calculation as Text (see screenshot). All the best! Chris
Is the Auto Enter Lookup option not preferable to a Auto Enter Calculation (in the more limited situations where it provides the correct result) because it has the Relookup Field Contents function available, both in scripts and in the menus? This function can address the whole found set in one go.
You might not need any field at all. The calculation field option produces an unstored calculation that will automatically update every time the field referenced in the calculation is changed in the related record. But in such cases, you can just add the field from the related table to your layout and get the same result without defining a calculation field. Thus, there are really only a few special cases where such a calculation field is actually necessary.
But sometimes, you need to capture and store the value from the related table as the value that was present at the time the record was created and/or updated. An example of this is when the line item in an invoice looks up the current price for that item. You wouldn't want future price changes to change the prices used in past invoices and so you copy over the price that was in effect at the time the sale takes place.
If that's the situation, then you should be doing either an auto-enter calculation, a looked up value, or a script to copy over the data.
If you have just two tables, Table A linked to Table B and you want two fields in A to copy over values from B when you create a new record in A and select/enter match field data in that new record of A, the looked up value and auto-enter options should have worked for you. If instead, you want data to be copied over from the new record in A into a field in B, then you should use a script.
It sounds like you have three tables involved, no?
Table A is related to table B. You want to get related data from table B and place it into table A. You then want to get another piece of data from table B and place it in table C. Is that right? If so, the most general solution is to use a script to set both values.
Fields in table A and table C could be defined so that their calculated value is updated when a piece of data changes in table A and table C respectively. However, the fields that trigger the update have to be changed manually or by a script.