Good afternoon jcondle,
I hope your day is going well. I'd like to offer you some assistance, but I'm not quite sure what your goal is. What are you attempting to achieve? Thanks and have a great day!
Thanks for the response. I have a database with codes for the ambulance names. I need the names to be in the database not the codes. The work intensive way would be to do a find/replace in excel on all 220 codes. I hoped there would be a better way to do it in Filemaker. I just need to display the ambulance names, so I could keep the codes in the database and just some way to display them in a report. Or create new fields like (Rig1,Rig2,etc.) and some way populate them with rig Names from a lookup table.
Thanks in Advance
If you setup 4 relationships:
DataTable ------ rigLookupTable_1
rig1d = rigID
DataTable ------ rigLookupTable_4
rig4d = rigID
Then the rigName value will be available to you for each of the four fields, in the form of:
At which point you could display it directly from the related tables, use replace field contents on a new field in the data table and remove the relationships etc..
But, here's an alternative approach if you just want it to display the name but retain the rigID.
Setup a value list using values from a field as follows:
Apply that value list to the data fields rig1d...rig4d.
A quick and dirty sample file is attached with both of those in place.
Example1.fmp12.zip 67.0 K
You did understand the question. Your solution is better. I have deleted my wrong posts not to confuse you.
I will try this tomorrow when i get to work
Good morning jcondle,
I hope your day is going well. I may have misunderstood the issue you are experiencing, but is there a reason you couldn't add one additional field to your FileMaker Pro table, the one that contains the ambulance codes, and define an auto-enter calculation - ExecuteSQL, for example - that would insert the ambulance name that corresponds to the existing code? Have a great day!
I have learned a lot about this process over the last week and I appreciate all of your help. I know explaiing the issue is really tuff over these chat sessions. Please bear with me.
I have only two tables. Import file and the lookup file.
The Import file has many rigid references in it. The lookup file has two fields the rigID and the rigName
What I need is to have the corresponding rig names along with the rig IDs in my import file.
I have created 4 rig name fields as text to store the rig names that are looked up.
I have set relationships as follows
rig2d | __________ RigID(lookup table)
When I do this I get only the value for the first rig1d in the file. The others (rig2d,rig3d and rig4d) which are different rigs come back with value rig1d1.
If this is too frustrating my fall back position is to use find/replace in excel.
When you say you need it in your import file...that confuses me. If you want it before importing, then where does FileMaker (after importing) come into it? Are you wanting to import, process the data and then export a new file for later importing into another db, perhaps?
The example I sent you above will allow you to do just that, but here's the same file with the 'value list' stuff stripped out and 4 new fields in your main data table, as an example.
These additional fields are just calculated fields pulling the information from the lookup tables.
Following this setup, you could import your data, then export again, including the additional 4 calculated fields.. in which case you then have the info in a new 'import file'
Example2.fmp12.zip 67.0 K