11 Replies Latest reply on Dec 30, 2009 3:55 PM by philmodjunk

    Using lookup function with existing data

    trouty

      Title

      Using lookup function with existing data

      Post

      Hi, don't know if anyone has required this in the past, but have a field in my database with existing numeric data populating it.  What I wish to do now is to keep the existing data within it, but wondered if it was possible to now use the Lookup function within this field for any new data, whilst leaving the existing in place?

       

      The first table A, has the agent's name with a field containing the amount they earned, the second table B, has a scale of service charge %ages for each agent which is associated to table A by the agent's name. What I wish to do is multiply the service charge %age by the agent's earned amount to give an amount they have to pay the company for standing charges.  Can this be done using lookups, and will the existing data be left alone?  The reason I wish to use lookups is that I believe they allow me to change the service charge %age for each agent later on, whilst leaving existing data in place.  It would be really useful if you could show the syntax, if you know the solution to this.

       

      Any help much appreciated

        • 1. Re: Using lookup function with existing data
          philmodjunk
            

          What's missing from your post is a description of the relationship linking records in your two tables. Let's assume your relationship reads like this:

           

          TableA::AgentID = TableB::AgentID

           

          With that relationship in place we can define a field table that uses a looked up value auto-enter option to copy data from your service charge field in a matching record in Table B.

           

          Define a number field in TableA.

          Click the options button or double-click the field definition.

          Click the Auto-enter tab.

          Click the Looked Up Value check box.

          Select TableB as the table from which to copy.

          Select service charge %age as the field to copy

          Dismiss your dialogs by clicking OK.

           

          Now, when you create a new record and select/enter the agent's ID, the current value from Table B will be copied into Table A. In any calculations that use this value, refer to the value in Table A.

           

           

          • 2. Re: Using lookup function with existing data
            trouty
              

            Thanks PhilModJunk,  I have that bit working now.  Although that has led me to another problem.

             

            What I'm trying to do is to use this lookup value and then multiply it by the agent sales amount to get the company service charge.  eg.  Say the service charge lookup value is 0.08 and the amount of agent commission earned is £200, then the company service charge is £16.  I wish to then take this £16 and put it into a field as static data.  The problem I have now is that the £200 is not there until I've entered it into the field (the lookup for the service charge has already happened at this point.  Does this mean that the lookup won't work, as the lookup has actuated once I've selected the Agent ID.

             

            Is there another way of moving the calculated amount (£16) into a static field?

            • 3. Re: Using lookup function with existing data
              philmodjunk
                

              In which table will you enter the £200 ?

               

              Not sure what you mean by 'static data'.

               

              If the £200 is entered in Table A, then you can either use your calculation as the definition for a calculation field--the value won't change as long as the £200 isn't changed and you also don't modify the AgentID field.

               

              You can also specify a number field with an auto-entered calculation option. This stores the value as a number which can be edited directly and there is an option: "Do not replace existing value" which can be used to keep the original value from changing.

               

              Is that what you need?

              • 4. Re: Using lookup function with existing data
                trouty
                  

                Hi,  the £200 will be entered into Table A.  What I mean by static data is data that I don't want to change later on, if I change the service charge rate later on in Table B.

                What i really want to do is to perform a calculation, and then take the result and put into a field that won't subsequently change if I change one of the variables of the calculation.  This would be done in the same table, once the lookup has pulled info from the other table.

                 

                Regards

                 

                Trouty 

                • 5. Re: Using lookup function with existing data
                  philmodjunk
                    

                  "What I mean by static data is data that I don't want to change later on, if I change the service charge rate later on in Table B."

                   

                  That's the whole point of setting this up with the looked up value option. Changes to data in table B won't affect values in Table A unless the "look up" is specifically triggered. The only way this will happen is if you edit the AgentID field or use the Relookup command.

                  • 6. Re: Using lookup function with existing data
                    trouty
                      

                    Hi, my question was about looking up within the same table (I've sorted the lookup to the other table). 

                     

                    Are you able to perform lookups within the same table in Filemaker.

                     

                    Also, can you lookup a calculated field.

                     

                    Also, if the data isn't in one of the fields when the calculation is performed, will this prevent the lookup from occuring when the data is finally entered.

                     

                    Sorry, I probably didn't say this clearly before. 

                    • 7. Re: Using lookup function with existing data
                      philmodjunk
                        

                      Are you able to perform lookups within the same table in Filemaker.

                      Yes. You can relate a table to itself (called a self relationship) by making a second table occurrence for the same data source table. Then refer to the second table occurrence just like any other looked up value.

                       

                      Also, can you lookup a calculated field.

                      Can a calculated field provide the value you want copied into the other table by this method? Yes. Any stored field can function as the source of data for a looked up value.

                       

                      Also, if the data isn't in one of the fields when the calculation is performed, will this prevent the lookup from occuring when the data is finally entered.

                      What calculation are we talking about here? A lookup copies a value from a field in one table to a field in a second table when a value in the match field in the second table is entered that matches a value in the first table. Whatever value is stored in the source field at that moment is the value that will be copied over. Changes to this value after the fact will not automatically be copied which is what makes this tool work the way you need it to. Changes to any other field in the calculation won't affect the lookup in any way, but the calculation itself will update to include the new value in computing a new result.

                       

                      What's confusing me in these questions is that you have at least 4 fields involved here and I don't know which one you are referring to as a calculation field.

                      Table 1 key (changing the value in this field triggers the look up)

                      Table 1 data (recieves the looked up value)

                      Table 2 key (matching table 1 key to a record with a matching value in table 2 key selects the record from which a value will be looked up)

                      Table 2 data (value that is copied into table 2 data when the key fields match)

                       

                      Table 1 key, Table 2 Key and Table 2 data can be calculation fields. Table 1 data may not be calculation, though an auto-entered calculation that both looks up the value and then incorporates it in a calculation can be set up here instead of using "looked up value".

                       

                      You might want to set up a sample data base and play with the looked up value option to see how these work for you.

                       

                      Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

                      Table vs. Table Occurrence (Tutorial)

                       

                       

                       

                      • 8. Re: Using lookup function with existing data
                        trouty
                          

                        Hi PhilModJunk, thanks for your efforts, your last post was very helpful.  I suspect my problem is that because the lookup occurs instantly upon creating a new record, but one of the bits of information that is used in the calculated field isn't in place until I enter it, then this is too late for the lookup and consequently, the calculated data isn't being looked-up.

                        Do you think this sounds right?

                        Perhaps I should have a button to restart the lookup so that when I've entered the missing data, I can press the button.

                         

                        What do you think?

                         

                         

                        Trouty 

                        • 9. Re: Using lookup function with existing data
                          philmodjunk
                            

                          "one of the bits of information that is used in the calculated field isn't in place until I enter it, then this is too late for the lookup"

                          That definitely will keep the look up from working. That also describes a very unusual data model. Look ups are generally used to look up information that applies to many records where changes to that information cannot be permitted to affect existing records. Unit prices and sales tax rates are classic examples.

                           

                          I'd take a very careful look at how you've set this up to see if there is any way to modify things so you aren't entering data and then looking it up. There may be an auto-entered calculation option that will work better. What I'm thinking of here is that you look up the other values used in your calculation, but perform the calculation in your parent table instead of the look up table.

                           

                          If you want, spell out what you are trying to do in detail and maybe I or another forum member can suggest a design change that will make this work for you.

                           

                          "Perhaps I should have a button to restart the lookup so that when I've entered the missing data, I can press the button."

                          That option can be made to work, but I would hope there's a simpler way. A simple way to re-lookup a value for the current record is to simply set the key field's value to itself and commit the record. Your script might look like this:

                           

                          Set Field [yourtable::keyfield; yourtable::keyfield]

                          commit record

                           

                          Where keyfield is a field used to define the relationship linking yourtable to the table of looked up values.

                          • 10. Re: Using lookup function with existing data
                            trouty
                              

                            Hi PhilModJunk,

                             

                            Thanks for looking at that for me, the problem I have with the lookup, is that there's about 6-7 years of data already in some of the fields, so cannot change database architecture.  If I could set a button to re-lookup once the missing data has been entered, I think this would be the best option.  I'm worried that the autoenter option would wipe old data away.

                             

                            Regards

                             

                            trouty 

                            • 11. Re: Using lookup function with existing data
                              philmodjunk
                                

                              I don't think you follow what I am suggesting. Here's an example.

                               

                              You have three fields in your look up table: ID, Price, Discount

                               

                              When you enter an item's ID in the parent table, you want both the current price and discount data to be used to compute the extended cost for the item, but you haven't yet entered a quantity and this is needed to see if the discount applies.

                               

                              In your parent table, define these fields:

                              ID

                              Qty

                              UnitPrice (looks up Price from lookup table)

                              Discount (looks up Discount from lookup table)

                              Cost   :  If (Qty > 5, Qty * UnitPrice - Discount; Qty * UnitPrice)

                               

                              Because I put the calculation in the Parent table--not the look up table, the look ups and calculation work correctly yet changes to price or discount will not affect previously created records.