For example, you could:
- combine the first two fields in Excel, before importing. For example, as A1&" - "&B1
- import the new combined field and the Fee Rate into its own table in FM
- you could use a value list showing the two fields - or rather use the Fee Rate as the first field, the MunicipalityAndDescription as the second field, and 'Only show values from second field'.
- attach that value list to the Fee Rate in the field where you want the data held, and they will see the Municipality in the pop-up but it will enter the Fee Rate in the field
- Another way to achieve the same thing is to have two fields in your end table, chose the Municipality from a pop-up of its values and have the Fee Rate come across by lookup. Both have the same effect (but your destination table would then have access to both values, which may be useful).
- You can reimport the updated fees any time, either by deleting and reimportimg, or updating the existing values. Although why not just do the update to the fees directly in the FM file?
I've gone in and imported both fields from Excel and created another table called Municipality and have worked up a value list for the pop-up. That part works perfectly in my new table.
I'm not able to get the fee rate to lookup on my table. I'm using a rate field on my main table and even after setting up a relationship between the two fee fields in both tables nothing comes up in the price field?
I'm sure I'm not doing something just right there. I've read the help on ghow to do lookups but I must not be getting the concept. I'm almost there. I just need to figure out how to populate the free rate field once I select the municipality from the pop-up menu I've created.
Thanks a bunch,
If you went with the Lookup option, then you should have:
- in the main table a field called MunicipalityAndFeeDescription. Presumably this is a pop-up list, simply derived from the new (concatonated) field of MunicipalityAndFeeDescription you imported from Excel.
- a relationship between the main table and this Fees table, linked by MunicipalityAndFeeDescription = MunicipalityAndFeeDescription
Then the Fee Rate field is set as:
- It is set as a number field, with the auto-entry option set to 'by lookup'. It should 'lookup' using that MunicipalityAndFeeDescription = MunicipalityAndFeeDescription relationship, and should return to the FeeRate field the Fee Rate in that other table.
Don't try to combine both options - either use the lookup option with one field as the MunicipalityAndFeeDescription and the second field as the Fee Rate, or use a two-field value list and only have a field for FeeRate.
Hi again, I've got things wqorking perfectly now.
Thanks soo much for all your help!