Cannot get Lookup to work with GetFieldName
Hi, I am new to Filemaker and have encountered a problem I can't seem to solve.
I need to populate a PRICE field in a CONTRACT table. The price must reference one of several different rate card records in a RATES table; each rate card has more than 300 product/price configurations (entered as fields.)
There is a third table, ADCONFIG, which holds all of the 300 product/price configurations as separate records, with additional identifying fields (size, shape, etc.)
The tables are linked by RateCardID (Contract to Rates) and AdConfigID (Contract to AdConfig)
The AdConfigID data exactly matches the field names in the Rates table.
For example, a contract might be issued for a 1/2 page horizontal black and white ad at three-time rate. The AdConfig ID on the contract - "_1l2_H_B_3x" - comes from a value list from the AdConfig table. and the price for that ad on every rate card is held in the field "_1l2_H_B_3x" in the Rate table. The contract has a field for the RateCardID.
So what I'd like to do is have the Price field be a Lookup (or Calculation) that would find the price entered for the "_1l2_H_B_3x" field on the Rates table.
The result would be equivalent to: Lookup (Rates::_1l2_H_B_3x )
...but I want to create the lookup so that the field name will change, based on the text string data of the AdConfig ID.
I have tried various versions of the idea below, sometimes using "Evaluate" as part of the Lookup.
Lookup (GetField ("Rates::" & GetFieldName("AdConfig")))
It seems that however I configure the lookup, I only get "?" as a result. If I plug in any given field, however, the rates do display correctly, so I am pretty confident that my tables, fields and relationships are in order.
I contacted one forum member who had a similar problem, and thanks to that advice, I am wondering whether the leading underscores in my field names and value list (which were added by excel and/or filemaker when I imported the rate card data) might be part of the issue....? If so, is there a way to write the lookup to get around that?
I would really appreciate any help that anyone might be able to provide. Thank you!