9 Replies Latest reply on Nov 1, 2011 10:47 AM by philmodjunk

    Cannot get Lookup to work with GetFieldName

    elizabethvanness

      Title

      Cannot get Lookup to work with GetFieldName

      Post

      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!

        • 1. Re: Cannot get Lookup to work with GetFieldName
          philmodjunk

          First problem:

          "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.)"

          That's a very difficult to work with structure. If you break up those hundreds of different product/price configurations into separate records, They will be much easier to work with. All records from the same "rate card" can have a common value so that you first select the "rate card" and then select the product ID in order to link to and refer to data from a specific record in your table. This can be implemented with a relationship that matches on two fields, "rate card" and "ProductID".

          Contract---<AdConfig>----Rates

          Contract::ContractID = AdConfig::ContractID

          AdConfig::ProductID = Rates::RateID AND
          AdConfig::RateCard = Rates::RageCard

          with this setup, you can use looked up value settings in AdConfig to copy pricing data from the related record in Rates.

          To answer why your current approach doesn't work touches on a number of issues. have you looked up these functions in FileMaker help?

          GetFieldname, for one example should be used like this:

          GetFieldName (TableOccurrencename::FieldName)

          It then returns "TableOccurrenceName::FieldName" as text. This is useful when passing a field name in a script parameter so that the field name passed, automatically updates should a developer open up Manage | Database and change the name of the field at some later date, but it doesn't really work for you here as you have found out.

          • 2. Re: Cannot get Lookup to work with GetFieldName
            elizabethvanness

            Hi, Thank you for the response!

            I appreciate what you're saying about the table layout, and I actually did consider the same setup you suggest for my Rates.  However, our rate cards change at least annually per product, and sometimes more frequently, and it also is quite typical that only some of the rates change on any given rate card (but every rate card does have to include all of those 300 prices.)  

            So for these reasons, it seemed more efficient and elegant to have one table with 300-plus fields and a few records, than to have one table with a few fields and thousands of records, the majority of which would be duplicates except for the rate card number. 

            This also allows me to create a new rate card via the layout, changing only a few fields and the rate card number.  Otherwise I think I would have to  use a program like excel, and import the 300 new records every time.  

            For this reason, the "GetFieldName" really seemed like an ideal solution.  I did search Filemaker Help and also several reference books, but still wasn't able to parse how to use GetFieldName when the TableOccurencename is known (Rates::) but the FieldName varies.  I originally tried something like GetFieldName (Rates::FieldName) but I couldn't enter that value because Filemaker returned a message that "the designated field cannot be found."  

            It's vexing.  If I can't figure it out, I probably will go back to the table idea you suggest, but I would like to avoid that if at all possible. 

            • 3. Re: Cannot get Lookup to work with GetFieldName
              philmodjunk

              I appreciate what you're saying about the table layout, and I actually did consider the same setup you suggest for my Rates.  However, our rate cards change at least annually per product, and sometimes more frequently, and it also is quite typical that only some of the rates change on any given rate card (but every rate card does have to include all of those 300 prices.) 

              That makes it even more important to use individual records as managing price changes will be easier to do with individual records than a single record with many fields. You can still pull up all of these values on a single screen in order to make the necessary changes by performing a find for all records with a specified RateCard ID or name and then you would sort these records so that you see a consistent record each time.

              I really, truly think that what you want to avoid is actually much less trouble to work with than trying to put multiple rates in separate fields of the same rates record.

              • 4. Re: Cannot get Lookup to work with GetFieldName
                elizabethvanness

                Ok, I see what you are saying, and I promise to consider it (even though I will have to re-enter all of the data, times four ....aaargh!)  

                Still, -- even conceding your point :-) I would really love to know how to write the calculation/lookup.  

                  • What is the best way to look up data from a field in a related table whose field name matches the data in a given record?
                  • Does it make a difference that the data begins with an underscore (and if it does, is there a way to write the lookup to circumvent that?) 
                  • Does it make a difference that the data in the first table's field ("AdConfig," which matches the field name in the second table) is text and the data I want to pull in from the other table is a number?
                  • When you wrote that GetFieldName "is useful when passing a field name in a script parameter so that the field name passed, automatically updates should a developer open up Manage | Database and change the name of the field at some later date, but it doesn't really work for you here as you have found out," does that mean that it is ONLY useful for that purpose, or can it actually be used to pull in data?

                Thanks and I hope you don't mind my taking this another round.  I really love the program and I'd like to know as much about it (and its capabilities) as I can. 

                • 5. Re: Cannot get Lookup to work with GetFieldName
                  philmodjunk

                  You can write a script to move the current data into a new table of the correct structure. This not only saves you time, but avoids data entry errors.

                  I'm still making some assumptions that may be incorrect. You'll have to look this over and let me know if I assume something wrong...

                  If you have these relationships:

                  Contract---<AdConfig>----Rates

                  Contract::ContractID = AdConfig::ContractID

                  AdConfig::ProductID = Rates::RateID AND
                  AdConfig::RateCard = Rates::RageCard

                  Then you can place a portal to AdConfig on a Contract layout. (This can be an existing layout or a new one, as long as it refers to the Contract table.)

                  In Adconfig, You can define a price field and use the looked up value field option to copy the price field from the corresponding Rates Records (since you now have one price in each record, this becomes a straight forward look up here.) To set up a looked up value field option, select the field definition for Price in the AdConfig table and double click it or click the options button. Then click the auto-enter tab in the field options dialog that appears. Select the looked up value check box and you'll get a dialog box where you can select the price field in the related Rates table.

                  In your portal, you palce a field for RateID with a drop down list or pop up menu to select the specific "Rate Card". The field next to it will be a drop down list of ProductIDs from this same Rates table. Select a Rate Card in the first drop down and a ProductId in the second and you'll see the corresponding price appear in the Price field in the same portal row.

                  This is an extension of the typical invoicing database structure with Contract used in place of INvoice and ADConfig used in place of LineItems. You may want to download and examine this very simple demo file created by Comment: http://fmforums.com/forum/showpost.php?post/309136/ The key difference is using two fields in the same relationship to specify a specific price for a selected product.

                  Once you get the basics working, you can make the ProductID field's value list a conditional value list where you only see the Products that are listed for the RateCard selected. This makes it possible to set up groups of "rates" that are smaller and thus easier to select from when setting up a set of AdConfig records.

                  • 6. Re: Cannot get Lookup to work with GetFieldName
                    elizabethvanness

                    Hi, sorry for delayed response, I've been struggling with this since I received your answer (and thank you for that!)  

                    There are two of your assumptions that don't seem to fit, unless I've missing something -I don't think I can use the relationship AdConfig::RateCard=Rates::RateCard (for reasons outlined below) ....and I don't actually have one price in each record, no matter how I configure it.  (Again, see following:)

                    My biggest issue is that the AdConfig (basically a product ID) can use any of many RateCards, but only one at a time.  

                    In our current real life, paper-based system, what we do is:  An advertiser decides what kind of ad to purchase (size, shape, color, frequency) and we issue a contract.  The contract tells us (1) the configuration of the ad and (2) the rate card that applies to that contract.  (And, unfortunately, it is not date-based.  For example, for the next two months, our advertisers can select either of two current rate cards.)   We then go to the relevant rate card, which is a table with many rows (the rows hold various SIZES, and PRODUCTS - e.g., magazine, directory, etc.) and 12 columns (the columns hold three COLORS - B/W, 2-C, 4-C - times four FREQUENCIES - 1x, 3x, 6x, or 12x)  

                    In real-life, it is totally easy to find the right price for any given combination - find the size the client wants, then move across the columns to locate the color and frequency.  The price is in that cel.  

                    But setting up the database has been not so simple :-)

                    After receiving your advice, I transposed the table and reimported the adconfigs so that each adconfig is its own record.  But now I seem to have a new version of same problem I had in the first place.

                    I currently have three different fields in each record, which contain the same type of data, i.e., prices.   So eg, the record I have for AdConfig Record "1l2_B_3x" has three fields, each containing a price -- one for Rate Card 33B, one for Rate Card 34, and one for RateCard 34B.

                    And of course, we have many advertisers who run half page black and white ads in various products.  So in order to find the correct price for any given Advertiser's contract, I have to locate one, but only one, of the prices on the record (currently there are three, as my starting point, but eventually there will be many more.)  I put each of the prices in three fields on the records - one for each rate card.  The data in the fields are the respective prices. 

                    I created a ratecard table, but I'm not sure what to do with it. Should the records of that table be one for each rate card, or one for each contract ID, or one for each adconfig??  However I do it, I can't figure out how to use that table to help me end up with one price in each record.  

                    I do have another ADCONFIG TABLE which parses out the AdConfig into respective sizes, shapes and frequencies (which we need for various production and marketing reasons) and that relationship is perfect, whether the Rates Table is configured by a few records of rate cards (as I originally organized it) or by many records of AdConfigs (which I've now changed it to.) 

                    But when it comes to putting a price in the CONTRACT Table's PRICE field, I have the same problem either way.  I have to look up the correct FIELD in the Rates table DEPENDING ON what the DATA is in the RATE CARD field.  

                    I am sure there must be a way to do this, and two things I've been trying to figure out are Join Tables, and "If" scripts.  The If scripts worry me because eventually we may end up with dozens of rate cards, which will mean dozens of different prices for any given adconfig (and would it also mean rewriting the scripts every time we added a new rate card?)   And I can't figure out how to use the join tables - or any version of the example you sent in your earlier reply - because I don't just have many-to-many, I have many-to-many-to many.  (Even our paper-rate cards are multi-tiered, with columns of color options nested underneath column headings of frequencies)

                    I'm very new to Filemaker and I'm probably missing something really obvious. I know you've spent a lot of time on this, and I do not want to take advantage.  But if you have any further ideas or if it's clear to you what I'm missing, I would be most grateful!   Thanks so much, Elizabeth

                    ps - it would seem to be so simple if the program could simply "read" the data from the field name in a related table, where that field name exactly matches the data returned by a specified field in the first table ... is there no way to do this, even in advanced filemaker?  Again, thanks!!

                     

                    • 7. Re: Cannot get Lookup to work with GetFieldName
                      philmodjunk

                      My biggest issue is that the AdConfig (basically a product ID) can use any of many RateCards, but only one at a time

                      This can easily be handled here with a conditional value list that limits the items selected to a specific rate card specified by selecting that card in a field in the contract record.

                      I currently have three different fields in each record, which contain the same type of data, i.e., prices.

                      My original suggestion was just one record = one price, not three prices for one record. What you describe can be made to work, but itmay or may not be the best approach to use here.

                      ps - it would seem to be so simple if the program could simply "read" the data from the field name in a related table, where that field name exactly matches the data returned by a specified field in the first table ... is there no way to do this, even in advanced filemaker?  Again, thanks!!

                      This is actually possible to do but not with the functions you were trying to use. I have deliberately avoided providing you with that info as it would be giving you enough rope with which to tie your entire system up in complex, difficult to work with knots.

                      I'm most interested in understanding more about this statement:

                      We then go to the relevant rate card, which is a table with many rows (the rows hold various SIZES, and PRODUCTS - e.g., magazine, directory, etc.) and 12 columns (the columns hold three COLORS - B/W, 2-C, 4-C - times four FREQUENCIES - 1x, 3x, 6x, or 12x) 

                      Each product (a row on your rate card) should definitely be a separate record in your rates table. Likewise, I'd be inclined to put each of the color options in separate records, producing 3 records for each product on the rate card. The Frequency based pricing can be handled one of three ways:

                      1) If, for a given product and color, the different frequency options can be plugged into a math formula to compute a price based on that third variable, you can set up a calculation at this point to compute the price. Keep that option in mind for possible future use. You can even define different formulas for different products and or rate cards should you choose to implement that approach.

                      The more practical decision point is whether to 2) define 4 price fields in each record--one for each frequency or 3) generate up to 12 separate records to store one price in each record as I originally suggested.

                      Both can function pretty much the same for the person using the database. The difference lies in how you, as developer work with the two different approaches. With 4 separate frequency fields, adding more frequency options than the original 4 requires adding more fields both in the rates table and in the adconfig table in order to implement that change. With one frequency option to each rate record, the same change would simply require adding more records--reducing this change to a data entry operation with no need to modify the database structure.

                      Let's stick with 4 frequencies in one record as I think you'll find it less of a struggle to set up, but keep the other option in the background as a possible future enhancement.

                      This leaves you with a minimum of 6 fields in one Rate record: Product, ColorOption, Freq1x, Freq3x, Freq6x, and Freq12x.

                      Define matching fields in Adconfig. Define two more data fields in AdConfig: ContractID, Frequency.

                      Now we can add the following Calculation field, cPrice, to compute the price displayed to the user after a Product, ColorOption and Frequency are specified:

                      Case ( Frequency = "1x" ; Freq1x;
                                Frequency = "3x" ; freq3x;
                                Frequency = "6x" ; Freq6x;
                                Frequency - "12x" ; Freq12x )

                      Define these relationships:

                      Contract::ContractID = AdConfig::ContractID

                      AdConfig::Product = Rates::Product AND
                      AdConfig::ColorOption = Rates::ColorOption

                      Now return to the field definitions for AdConfig and configure the Freq1x field to have an auto-enter looked up value field option that copies the contents of the Freq1X field in Rates into this field. Repeat these steps for each of the Freq fields, each copying data from the matching field in Rates. (By copying these prices, you can change a price in the Rate table and only new AdConfig records will automatically use the new rate info. Existing AdConfig records will remain unchanged.)

                      Now you can add a portal to AdConfig on your Contract table and put AdConfig::Product, AdConfig::ColorOption, AdConfig::Frequency and AdConfig::cPrice in the portal row. The first three fields should be configured with value lists so that the user selects a product, ColorOption and Frequency and the Price then automatically pops up in the cPrice field.

                      I am assuming here that in one contract, you might specify more than one Advertisement, such as advertising the same customer product in two or more more publications/media outlets. Each such case would require it's own row in the AdConfig Portal on this layout.

                      I have so far deliberately left out any reference to a RateCard here, nor have I described how you might set up a layout for reviewing and editing ratecard  data. Those will be future steps to take on only if the current suggested approach makes sense to you and you are able to get it working in a small demo file. Just keep in mind that I haven't forgotten those issues and that it is possible with either rate structure, to set up a table on your layout that looks just like your current rate card table for times when you want to review and edit this data.

                       

                      • 8. Re: Cannot get Lookup to work with GetFieldName
                        elizabethvanness

                        Brilliant!  I can also see how, if this works, it might actually offer us another opportunity, i.e., if someone has signed up for 3x a year, and then decides to use a fourth ad, I think our staff might be able to quote new 6x rates and rate adjustments from the database.  (Well just a thought, I don't want to get ahead of myself. :-)

                        I will need the weekend to test everything, but thank you so, so much!  I am very excited to work with it, and I also (for the first time, I confess!) see how this is ultimately more "elegant and efficient" than my original idea.

                        We don't use math to create the rate cards, they are based on marketing and accounting concerns, some of which are intuitive - but it's unlikely that frequencies will ever change, so I do think we can work with this plan.  (Again, I will have to test it out .. but it seems very practical, even if time-consuming at the beginning.)

                        I appreciate your understanding the rate card layout issue, too.  That was my motivation in structuring things as I originally did. So it is encouraging to think that we won't have to sacrifice that efficiency. 

                        ps re your response to my ps:  

                        "ps - it would seem to be so simple if the program could simply "read" the data from the field name in a related table, where that field name exactly matches the data returned by a specified field in the first table ... is there no way to do this, even in advanced filemaker?  Again, thanks!!

                        This is actually possible to do but not with the functions you were trying to use. I have deliberately avoided providing you with that info as it would be giving you enough rope with which to tie your entire system up in complex, difficult to work with knots."

                        Ok, I promise not to do it... but could you please tell me anyway?  I would really love to know as much about the program as possible. :-)

                        For example, despite all my research, I didn't know about conditional value lists. I can see how this will be incredibly useful, not only with the rates, but in other parts of the database.  

                        Thank you, PhilModJunk!

                        • 9. Re: Cannot get Lookup to work with GetFieldName
                          philmodjunk

                          First some study materials on conditional value lists:

                          Forum Tutorial: Custom Value List?

                          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                          Thie first two links have a lot of overlap so you can read one and skim the other for additional info. I include both because some readers find one or the other easier to understand. The last link is a demo file where you can see some of these options in action as well as see a link between how a portal works and how a conditional value list works that I've found helps people better understand how conditional formats work.

                          To access a field by it's name indirectly, uses two main functions: GetField and Set Field By Name. The first is for "reading" the data, the second is for changing it. GetFieldName is useful to use in certain specific cases to keep from locking you in to a specific field name in Manage | database | fields for one of these names as it enables you to change a field name there and not have one of these indirect references suddenly stop working as a result.

                          GetField ["TableOccurrenceName::FieldName"] will return the contents of that named field.

                          GetField [TableOccurrencename::FieldName] will return the contents of the field name stored in that field. If you enter "MasterTable::Name", into the field named Globals::SelectedField, GetField [Globals::SelectedField] will return the contents of MasterTable::Name.

                          Set Field by Name works like set field, but you use a calcualtion that evaluates to the table occurrence and field name of the field to receive the calculated result fo the second parameter.

                          Set Field by Name ["MasterTable::Name" ; 5]

                          will store a 5 into the Name field defined in MasterTable.

                          In similar fashion as GetField, if you enter "MasterTable::Name" into Globals::SelectedField, then

                          Set Field By Name [Globals::SelectedField ; 5 ]

                          will also store a 5 into the Name field in MasterTable.

                          GetFieldName can be a very useful way to pass the name of a field to the script without locking in that name as I mentioned earlier.

                          I can set up a script to add 5 to the field whose name is passed as a script parameter:

                          Set Field By name [ Get (ScriptParameter) ; GetField ( Get ( ScriptParameter ) ) + 5 ]

                          THen I can set up a button for each field that passes the name of the field like this:

                          GetFieldName ( MasterTable::CountField )

                          and "MasterTable::CountField" is passed as the script parameter. But if I then open up Manage | Database | Fields and change the name of CountField to Counter, the system automatically updates to pass "MasterTable::Counter" to the script and everything continues to work.

                          If you've been wondering why I used the term "table occurrence" here, you can also read this thread on the topic:  Tutorial: What are Table Occurrences?