7 Replies Latest reply on Jun 5, 2015 5:24 AM by DavisSmith

    Calculations not working across records

    DavisSmith

      Title

      Calculations not working across records

      Post

      I have a layout that has many different calculation fields. The problem is that my calculation fields will not calculate independently from the other records. Ex. calcfield1 on record1 = 10 and calcfield1 on record2 also equals 10 yet they should have been different based on selections in other fields.

       

        • 1. Re: Calculations not working across records
          philmodjunk

          You'll need to supply some details, such as copy/pasting the actual expression. And such a calculation can be defined in a field of type calculation or it can be set up on a data field as an auto-enter calculation. This can make a difference in the results that you get in some cases so please indicate which that you have set up. (Sounds like an auto-enter calculation that is not updating, but other explanations are also possible.)

          • 2. Re: Calculations not working across records
            DavisSmith

            Its a case calculation. Its really long so i'll just post a piece of it.

            Case(
            Quote::LINEITEMS__Text2 = "801L - 60/40"; Stainless Steel Sinks::Stainless Steel Price[1];
            Quote::LINEITEMS__Text2 = "801R - 40/60"; Stainless Steel Sinks::Stainless Steel Price[2];

            )

             

            • 3. Re: Calculations not working across records
              DavisSmith

              Actually thats not enough information either..

              That is the calculation for the price of the item but then the actual field that is not displaying correctly is calculated like so..

              Pricing Level::Line Items Price 2 Copy * Quote::LINEITEMS__Amount2* (NOTE: another list of cases for whether or not to use a pricing multiplier. Ill just post a few though i dont think it makes a different)

              Case(
              Quote::LINEITEMS__Text2 = "801L - 60/40"; Extend( Pricing Level::Multiplier);
              Quote::LINEITEMS__Text2 = "801R - 40/60"; Extend( Pricing Level::Multiplier);

              Quote::LINEITEMS__Text2 = "ALIVE - ALIN175YU";1;
              Quote::LINEITEMS__Text2 = "ALIVE - ALIN200YU";1;

              )

              • 4. Re: Calculations not working across records
                philmodjunk

                At the top of the Specify Calculation dialog, there's a Context drop down. What is selected there?

                You have data from two tables, Quote and Stainless Steel Sinks with the added complication that you appear to have defined the second field as a repeating field. The relationship between these two tables will greatly affect what results is returned here. What are the details of that relationship? How do the values match between tables?

                • 5. Re: Calculations not working across records
                  DavisSmith

                  Its a cartesian relationship between the two tables. The context drop down displays the current table for each calculation and is grayed out. I should note that all the calculations work fine in a single record, its just when a new record is made they seem to break.

                  • 6. Re: Calculations not working across records
                    philmodjunk

                    If Quoteand Pricing Level are related in Cartesian join, this won't work.. Every reference to a field on the other side of the Cartesian Join will all refer to the same record--the first related record which will be the first record in the table unless you double click the relationship line and specify a sort order for the relationship.

                    And is this also part of a repeating field? That's a very significant detail to leave out. If it's not there's no reason to use the Extend function here. Repeating fields are very, rarely the best design option for Filemaker solutions ever since portals and related tables became possible with FileMaker 3.0.

                    • 7. Re: Calculations not working across records
                      DavisSmith

                      Basically, my problem is I have a quote form hosted on FM server that people use to give quotes customers. I recently made a number of changes to the old system (offline) and then I put the new system online. The main problem that has came up is that it seems like everyone is editing the same copy. In essence, this makes it so only 1 person can use the quote form at a time because they would be editing someone else's data that is also using it.

                      My thoughts on a solution was to make a new record on layout load and fix it that way but I haven't been able to get it to work.

                      My problem with people editing the same file/records not working could stem from the same issue, but I'm hoping there is an easier fix because I'm not sure where to start with fixing my database.