5 Replies Latest reply on Dec 22, 2015 10:07 AM by Mike_Mitchell

    get price data from an input

    bparry

      I have two tables.  one is the data form, the other is a price table.

       

      the price table has uid for each price set with an m1, m2, m3 etc for the field and each has a provided data value.

       

      In the form they have a field called damage which they input a number which represents the damage %

       

      that damage number I want to use to look up the price for the %.  each m number in the price table is to represent the % value.

       

      example if I used 50 in the damage field on the form I want it to look up m50 data from the table to get that price damage and output to

      another field total cost.

       

      I have tries many ways to figure this out on my own and hit a wall on how to do the calculation.

       

      I tried to use my programming background to make total cost = MTABLE::"m"+damage" but its invalid to do that way.

       

      Any help would be great thanks.

       

      Ben

        • 1. Re: get price data from an input
          Mike_Mitchell

          bparry wrote:

           

          I tried to use my programming background to make total cost = MTABLE::"m"+damage" but its invalid to do that way.

           

           

          You could use the following calculation:

           

                Evaluate ( "MTABLE::m" & damage )

           

          to facilitate this.

           

          However, I would suggest you take a look at your data model. Whenever I see something like, "field1, field2, field3" or similar, I immediately smell a flaw in the data model. Your percentages should probably live in a separate table rather than in a table with the prices, joined back by discount to the main table.

          • 2. Re: get price data from an input
            bparry

            The % is located in a separate table MTABLE with the fields M1-100 which represents the % damage  each Mx field has a data value with is a $ value.  on the main table form they enter in damage number with out the %.  I need it to then take that number and find the m value to look up.  if the damage is 10 Id want it to look up m10 on the other table and grab its value to show on another field on the main form called total damage.

             

            because the value for each % is not linear each % must have a unique price.

             

            The Evaluate ( "MTABLE::m" & damage ) did not work in the total damage calculation to grab the value from the table.

             

            perhaps you are right Mike maybe I am approaching this incorrectly but I am not sure of another way to do.

             

            Any help would be appreciated.

             

            thanks,

            Ben

            • 3. Re: get price data from an input
              Mike_Mitchell

              Each M value (1 - 100) should be a record in a related table. Just basic normalization principles.

               

              If the calculation I gave you isn't working, it's probably because either your relationship is not set up correctly, or the value in the table isn't evaluating correctly. Post your Relationships Graph and the calculation in question.

              • 4. Re: get price data from an input
                bparry

                That could be the issue as there is not relation ship currently between tables.

                 

                my understanding is with relationships I would need to link a field from one table to the other correct?

                I put the mx number in another table just because I will be updating them from time to time and figured it would be easier to import new prices monthly rather than key them in manually into the main form table.

                • 5. Re: get price data from an input
                  Mike_Mitchell

                  Yes, you have to connect the two table occurrences (not tables) on the Relationships Graph for table A to be able to see data from table B. This should typically be done using a unique key field in one table, and a corresponding field in the other table (which may or may not be unique).

                   

                  I suggest you grab a copy of the FileMaker Training Series and go through it. You're lacking some significant basics that will be required for you to succeed.