4 Replies Latest reply on Jun 14, 2013 12:23 PM by pruppert

    Conditional lookup - Is this possible?

    pruppert

      Title

      Conditional lookup - Is this possible?

      Post

           Hi, I am new to FileMaker and want to know if the program could accomplish the following.

           I'd like to have three tables (1 data entry table, and 2 refernce tables):

                                                                                                                                                                                                                                                                                                                                                                                                 
                Data Entry Table
                          Condition                     Raw Score                     Converted Score
                          1                     1                      
                          1                     2                      
                          2                     3                      
                          2                     4                      

            

                                                                                                                                                                                                                                                                                                                 
                Condition 1 Lookup
                          Raw score                     Converted Score
                          1                     10
                          2                     20
                          3                     30
                          4                     40

            

                                                                                                                                                                                                                                                                                                                 
                Condition 2 Lookup
                          Raw Score                     Converted Score
                          1                     100
                          2                     200
                          3                     300
                          4                     400

            

           I would like it so that when values are entered into the "Raw Score" column of the Data Entry table, values are then automatically looked up and entered into the "Converted Score" column of the Data Entry table. I would like the automatically entered values to come from one of the reference tables based upon a given conditoin value in the first column of the Data Entry table.

           Does this make sense? Is this even possible in FileMaker. If so, how would I accomplish this, or what aspects of FileMaker would I need to learn about to be able to achieve this (i.e., scripting, calculations, etc.)?

           Thank you.

        • 1. Re: Conditional lookup - Is this possible?
          philmodjunk

               Can your provide an example of the "given  condition value in the first column of the data entry table"?

               It might not be necessary to have more than one table for your converted scores and then your solution's design can be simplified.

               But with that said, you can set up auto-enter calculations in field options with an If or case function that will copy data from different tables depending on how the case or If function evaluates.

          • 2. Re: Conditional lookup - Is this possible?
            pruppert

                 Thanks for the reply. In an attempt to most simply communicate what I was wanting to accomplish, I was just using an example of a Field that has a value of 1 or 2 as the condition. So can the value be looked up from one of the condition tables depending on if their is a 1 or a 2 in the condition field?

                 Like I said, I am very new to FileMaker, and so am just trying to get an idea of where to even begin to learn about how to do this. It sounds like I would accomplish this through a calculation? If so, where can I find some good documentation on learning about the syntaxt to use in calculations?

                 Thanks.

            • 3. Re: Conditional lookup - Is this possible?
              philmodjunk

                   What you can do is produce a table that looks like this:

                   Condition    Raw Score        Converted Score

                   1                       1                         10
                   2                       1                         100
                   1                       2                          20
                   2                       2                          200
                   1                       3                          30
                   2                       3                          300

                   Then you can set up this relationship:

                   DataEntry>------LookUp

                   DataEntry::RawScore = LookUp::RawScore AND
                   DataEntry::Condition = Lookup::Condition

                   And then a looked up value setting on DataEntry::ConvertedScore can look up the correct converted Score from the LookUp table.

                   Note that with this approach, you can add or remove "condition" values and not need to add or remove tables nor modify an auto-enter calculation, you just edit the records in the LookUp table.

              • 4. Re: Conditional lookup - Is this possible?
                pruppert

                     Thank you for this. Though, I am having a hard time understanding what to do with this. I am having a hell of a time even understanding the basics of Filemaker. Hard to get out of excel-think.