9 Replies Latest reply on Jul 22, 2013 12:12 PM by GuyStevens

    Multiply 2 fields from different tables

    BeachedWhale

      Title

      Multiply 2 fields from different tables

      Post

           I have two number fields from different tables and I need to multiply them in a calculation to create a field in one of the tables. However when I do, I get no value as in question marks.

           I don't know what the problem is or how to get around it, help please :)

        • 1. Re: Multiply 2 fields from different tables
          GuyStevens

               Are the two tables related? And if so how?

               Can you give us a little more information?

               Because this shouldn't be a problem, the only important bit is the relationship between these two tables.

          • 2. Re: Multiply 2 fields from different tables
            BeachedWhale
            One field is Orders so it deals with the entire order,  the other is order line items so it deals with each item ordered. I have a field in orders that is a percentage so for example 0.8 and I want to multiply this field in the order table by all the individual items within the order line items table and its not working. For example (just to be clear) This is the order: Type.         Quantity.        Price Book A.           2.                  $3 Book B.            1.                  $2 Book C.            4.                  $1 Total.               7.                  $12 So orders only can contain fields for the whole thing such as the totals whereas order line items is Book A or Book B or Book C. What I tried to do was take an orders field of 0.8 and multiply it to the price of each line item in the order line items table but it wont let me.
            • 3. Re: Multiply 2 fields from different tables
              philmodjunk

                   A question mark means that FileMaker cannot display a value in a field. The two most common causes are: a) the field is too narrow to display the result--often this occurs because specfiying currency data formatting requires a wider field than expected. If this is the case, you will see the expected value (without the formatting) when you click into the field. 2) An error in the calculation expression is producing a math error such as dividing by zero. In these cases, when you click or tab into the field, you still get a question mark.

                   Thus, I suspect that your field is too narrow to display the computed value and resizing the field would then remove the question mark. Since you are specifying a percentage, there could also be a data entry error compounding the issue. If you type 2 into your percent formatted field, you are specifying 200%, not 2% so your computed result may be larger than expected and thus requires a wider field to display correctly.

              • 4. Re: Multiply 2 fields from different tables
                GuyStevens

                     Your problem might be in the

                     

                          "multiply it to the price of each line item in the order line items table"

                     In your Order Line Items table you should have a summary field that gives you the total of your "total" field.

                     Then you multiply your 0.8 percentage field by this summary total field.

                     Just make sure your relationships are set up correctly.

                      

                • 5. Re: Multiply 2 fields from different tables
                  BeachedWhale
                  I did widen the field but the question mark remained so it must be a calculation error. @DaSaint that's how its originally set up but I need to be able to isolate each book with the discount for reporting purposes, which is why I need the percentage field multiplied by each line item.
                  • 6. Re: Multiply 2 fields from different tables
                    philmodjunk

                         What do you see when you click into the field in browse mode?

                    • 7. Re: Multiply 2 fields from different tables
                      GuyStevens

                           If you want to do the calculation for every line item then you need to do it in the OrderLineItems table.

                           Calculate the price multiplied by the percentage from the orders table in the OrdersLineItems table.

                           Make a summary total of that and you have your total discount you can show or use on the orders table.

                      • 8. Re: Multiply 2 fields from different tables
                        JimMac

                        cool then Phil's math error "?" is the problem, which is probably cause by DaSaints comment on missing data from calculation... from the relationship being incorrect

                             Jim...

                        • 9. Re: Multiply 2 fields from different tables
                          GuyStevens

                               The relationship between the Order and the OrderLineItems table should be something like this:

                               Order table should have an ID. A number field set to Auto Enter a Serial Number.

                               The OrderLineItem table should have the same ID field plus an OrderIdFk field (The foreign Key for the Order ID)

                               That's a number field that contains the ID from the order that this OrderLineItem is related to.

                               With that relationship in place you should be able to create any kind of calculation you want between those two tables.