6 Replies Latest reply on Nov 11, 2013 4:17 PM by EwanMacGregor

    One record calculation not working the other 168 perfect?

    EwanMacGregor

      Title

      One record calculation not working the other 168 perfect?

      Post

           I am creating a simple product database. I have one table that consists of the product, part numbers etc and a cost price. This is shown as a number. I also have a country field which is a Value field. I also have a table of exchange rate and freight and duty. In the main product table I perform a calculation. When I select the country the currency, exchange rate and freight and duty multiplier are automatically added. I them perform a calculation to get the landed cost chichi is the cost x freight and duty multiplier divided by the exchange rate. It works perfectly apart from on two products that show a landed cost of £0.00. All other calculations are correct. I have tried adding new products as a test and these also result in a £0 landed cost.

           I'm going round the bend slowly. Has anyone got any suggestions where to look to get all fields to perform this calculation for all records?

        • 1. Re: One record calculation not working the other 168 perfect?
          philmodjunk

               It sounds like your relationships are failing to match to a record in a related table  for these two products and thus a value that should be referenced in that related table is replaced by a null value and you then get 0 as the computed result.

               That's just a guess. To suggest more or how to fix such an issue would require a more detailed description of your tables, relationships and the calculations that use them to get the correct result.

          • 2. Re: One record calculation not working the other 168 perfect?
            EwanMacGregor

                 Hi,

                 Thanks for this but I'm not sure. I am just taking a cost force of 11 (variable depending on the product) then multiplying it fixed number (taken from a Value list) and dividing it by the currency exchange rate again a number taken from a Value list. As I said it works on all but 2 records. The initial data came from a Numbers spreadsheet which I dropped onto Filemaker. I have deleted and re entered the records without any change. When I try and enter new records the calculation is O. I've checked all elements are numbed etc. The products are entered by a form and everything has gone in the same but these two records give me a zero result.

                 Going nuts

            • 3. Re: One record calculation not working the other 168 perfect?
              philmodjunk

                   Which still sounds like a case where two products records are failing to look up a value from a related table.

                   To repeat:

                   To suggest more or how to fix such an issue would require a more detailed description of your tables, relationships and the calculations that use them to get the correct result.

              • 4. Re: One record calculation not working the other 168 perfect?
                EwanMacGregor

                     How would it be best to describe the tables & relationships? Can I send a copy of the database over?

                • 5. Re: One record calculation not working the other 168 perfect?
                  philmodjunk

                       Try using the "upload an image controls" to upload a screen shot of the relevant portion of Manage | Database | Relationships. Please include additional text that helps others to identify which "box" is which in that image. Also be careful to only upload files of one of the file types specified. If you upload a PDF it appears to upload, you don't get an error message, but no image appears in your post.

                       You can also copy and paste the calculations from the specify calculation dialog directly into Post a new Answer to share the exact syntax of your calculation expression.

                  • 6. Re: One record calculation not working the other 168 perfect?
                    EwanMacGregor

                         Think I've cracked it. I was using too many value lists when I didn't need too. Knocked them out, simplified the tables as they didn't need Value lists and the 0 is now a real number.

                         Thanks for replying to my post. Your response made me think and helped be find the solution.