14 Replies Latest reply on Aug 24, 2015 8:30 PM by taylorsharpe

    Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600

    lbl159

      I have an If formula where the 'value if false' is a field that is a calculation (S4_Investor_Entities 1::S4_FMV).  Instead of returning just the correct number, it returns it repeating over and over again. Or sometimes it won't be exactly repeating and will look like: 17751057510575105751057510575106596.16 or 19612.519612.519612.519612.519612.519612.519612.5

       

      As a test I added the field (S4_Investor_Entities 1::S4_FMV) to the layout on its own. That returned the correct values without any repetition. It is only when this field is being referred to as the 'value if false' in my If formula.

       

      Any idea why this may be occurring?

        • 1. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
          electon

          Can you post the calculation / formula?

          Also if the calculation resides in the same table or related?

          • 2. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
            lbl159

            Here is the formula:

             

            If (S4_BVD Sub Series Number = 1 and not IsEmpty (S3_Investments::S3_Net Distribution 1) ; S3_Investments::S3_Net Distribution 1 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 2 and not IsEmpty (S3_Investments::S3_Net Distribution 2) ; S3_Investments::S3_Net Distribution 2 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 3 and not IsEmpty (S3_Investments::S3_Net Distribution 3) ; S3_Investments::S3_Net Distribution 3 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 4 and not IsEmpty (S3_Investments::S3_Net Distribution 4) ; S3_Investments::S3_Net Distribution 4 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 5 and not IsEmpty (S3_Investments::S3_Net Distribution 5) ; S3_Investments::S3_Net Distribution 5 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 6 and not IsEmpty (S3_Investments::S3_Net Distribution 6) ; S3_Investments::S3_Net Distribution 6 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV ) &

            If (S4_BVD Sub Series Number = 7 and not IsEmpty (S3_Investments::S3_Net Distribution 7) ; S3_Investments::S3_Net Distribution 7 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV )

             

             

            And here is the calculation for S4_FMV:

             

            If ( S4_BVD Sub Series Number = 1 ; S4_Investor_Entities 1::S4_convMult 1 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 2 ; S4_Investor_Entities 2::S4_convMult 2 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 3 ; S4_Investor_Entities 3::S4_convMult 3 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 4 ; S4_Investor_Entities 4::S4_convMult 4 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 5 ; S4_Investor_Entities 5::S4_convMult 5 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 6 ; S4_Investor_Entities 6::S4_convMult 6 * S4_Inv Amount) &

            If ( S4_BVD Sub Series Number = 7 ; S4_Investor_Entities 7::S4_convMult 7 * S4_Inv Amount)

             

            The calculation for S4_FMV is evaluated from the context of a related table: S4_Investor_Entities Direct.

            • 3. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
              electon

              According to the calculation the results could produce, depending on data, concatenated values.

              If you're after a single value only you should use an OR operative instead of &.

               

              EDIT: not sure about the OR yet, but are you after a single value? Can there be, out of all possible combinations, only one result per row?

               

              Am I right?

              • 4. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                lbl159

                No I am not after a single value.  This layout takes a specific investor and provides data categorized first by company and then by individual investment. So I put this field in the layout and it gives me a value for each individual investment by a specific investor.

                 

                The calculation for S4_FMV works as is. I created it for/am using it in another layout. When I put the S4_Investor_Entities 1::S4_FMV field directly into this layout where I am having the issue everything works fine. It's just when it is being referenced in the formula. 

                • 5. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                  electon

                  It is challenging to understand exactly what's going on without looking at the solution.

                  Looks like a context issue to me. Depending on where and whether you put the calculation field on the layout can behave differently.

                   

                  Is it a list view or form view? portals?

                   

                  Also, just a tip for your calculations:

                  If you reference a field repeatedly in a calculation, use a Let () function to define it into a local variable.

                  This way the field will not need to be evaluated over and over again by the engine.

                  Faster, less demanding on the machine.

                   

                  Let (

                       invAmount = S4_Inv Amount;

                       If ( S4_BVD Sub Series Number = 1 ; S4_Investor_Entities 1::S4_convMult 1 * invAmount) &

                       If ( S4_BVD Sub Series Number = 2 ; S4_Investor_Entities 2::S4_convMult 2 * invAmount)

                  ... etc

                  )

                   

                  I'm afraid I'd need to poke around myself in the solution to see what's going wrong.

                  • 6. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                    DavidJondreau

                    Your calculation is returning what you've asked it to. Using the "&" means you'll have 7 values all combined in a row. What results do you want?

                     

                    Your calculation is also confusing. My guess is you want a Case() here and not an If() and &.

                     

                    Also, the fields in your calc indicate you should reconsider your table structure. Anytime you have number fields like, S3_Investments::S3_Net Distribution 2 then S3_Investments::S3_Net Distribution 3 and so on means those fields should probably be in a child table.

                    • 7. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                      lbl159

                      If it's returning what I've asked it to then why does this calculation return the correct individual amounts (in a different layout and in this one) even when it has the "&" ?

                       

                      S4_FMV:

                      Let (invAmount = S4_Inv Amount;

                      If ( S4_BVD Sub Series Number = 1 ; S4_Investor_Entities 1::S4_convMult 1 * invAmount) &

                      If ( S4_BVD Sub Series Number = 2 ; S4_Investor_Entities 2::S4_convMult 2 * invAmount) &

                      If ( S4_BVD Sub Series Number = 3 ; S4_Investor_Entities 3::S4_convMult 3 * invAmount) &

                      If ( S4_BVD Sub Series Number = 4 ; S4_Investor_Entities 4::S4_convMult 4 * invAmount) &

                      If ( S4_BVD Sub Series Number = 5 ; S4_Investor_Entities 5::S4_convMult 5 * invAmount) &

                      If ( S4_BVD Sub Series Number = 6 ; S4_Investor_Entities 6::S4_convMult 6 * invAmount) &

                      If ( S4_BVD Sub Series Number = 7 ; S4_Investor_Entities 7::S4_convMult 7 * invAmount))

                      • 8. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                        electon

                        Personally I think the confusion is in the title of the post:

                         

                        7600760076007600 instead of only 7600

                         

                        Implying that you want a single result instead of a series of results glued together.

                        But from your explanation it seems that a series is what you want to return.

                        • 9. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                          lbl159

                          Below you will see the Fair Market Value Layout where S4_FMV= *Current Fair Market Valuation

                          This layout is working as intended and is how the other layout below is supposed to work.  I want a single result for each individual investment.

                          FMP 2.png

                           

                          This is the layout that is having difficulties. There are different Series rounds of investments. S4_BVD Sub Series Number 1-7 refer to each potential Series as set up in the database.  I'm trying to say If this investment took place in Series 1, then use this data relevant to Series 1 to calculate, if not then just use S4_FMV, which works above.

                          FMP.png

                          • 10. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                            lbl159

                            I don't want each individual row to return a series of values, but for the calculation to return a series of values down the line that is calculated based off of the data in the appropriate series

                            • 11. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                              DavidJondreau

                              Because in this calculation, you don't have a default result for your If(). In the other you do.

                              "Description

                              If test is True (any non-zero numeric result), FileMaker Pro returns result1. If test is False (0), result2 is returned. Test must be an expression that returns either a numeric or Boolean (True, False) result. "

                               

                              So this calc:

                              If ( S4_BVD Sub Series Number = 1 ; S4_Investor_Entities 1::S4_convMult 1 * invAmount)

                              means if the sub series isn't 1, return nothing. You do that 7 times and the series matches once, you get one number.


                              While this calc:

                              If (S4_BVD Sub Series Number = 1 and not IsEmpty (S3_Investments::S3_Net Distribution 1) ; S3_Investments::S3_Net Distribution 1 * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV )

                              means if the series isn't 1 (or the Net Distribution is empty) return the FMV. Do that 7 times (with one Series match) and you get the FMV 6 times with the number you want mixed in there somewhere.


                              Does that make sense?


                              You've got a default answer for the If() in the one that isn't working, while you've got no default answer in the one that is.

                              • 12. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                                DavidJondreau

                                Try something like this...

                                 

                                Let([

                                distribution = Case ( S4_BVD Sub Series Number = 1 and not IsEmpty (S3_Investments::S3_Net Distribution 1) ; S3_Investments::S3_Net Distribution 1  ;

                                S4_BVD Sub Series Number = 2 and not IsEmpty (S3_Investments::S3_Net Distribution 2) ; S3_Investments::S3_Net Distribution 2  ;

                                S4_BVD Sub Series Number = 3 and not IsEmpty (S3_Investments::S3_Net Distribution 3) ; S3_Investments::S3_Net Distribution 3  ;

                                S4_BVD Sub Series Number = 4 and not IsEmpty (S3_Investments::S3_Net Distribution 4) ; S3_Investments::S3_Net Distribution 4  ;

                                S4_BVD Sub Series Number = 5 ; not IsEmpty (S3_Investments::S3_Net Distribution 5 ) ; S3_Investments::S3_Net Distribution 5  ;

                                S4_BVD Sub Series Number = 6 and not IsEmpty (S3_Investments::S3_Net Distribution 6) ; S3_Investments::S3_Net Distribution 6  ;

                                S4_BVD Sub Series Number = 7 and not IsEmpty (S3_Investments::S3_Net Distribution 7) ; S3_Investments::S3_Net Distribution 7  );

                                result = If ( not isEmpty ( distribution) ; distribution * S4_Inv Total Percent FMV ; S4_Investor_Entities 1::S4_FMV )

                                ];

                                result

                                )

                                • 13. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                                  lbl159

                                  Yes that makes a ton of sense. Thank you very much man that formula basically did it! Been a huge help

                                  • 14. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
                                    taylorsharpe

                                    It seems to me there is a problem of continuing to performing If's and append them when you already have a result.  This makes me look at using the Case statement which stops after the first successful test.