
1. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
electon Aug 24, 2015 11:59 AM (in response to lbl159)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 Aug 24, 2015 12:11 PM (in response to electon)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 Aug 24, 2015 12:23 PM (in response to lbl159)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 Aug 24, 2015 12:32 PM (in response to electon)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 Aug 24, 2015 1:16 PM (in response to lbl159)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 Aug 24, 2015 2:42 PM (in response to lbl159)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 Aug 24, 2015 2:54 PM (in response to DavidJondreau)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 Aug 24, 2015 3:00 PM (in response to lbl159)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 Aug 24, 2015 3:24 PM (in response to electon)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.
This is the layout that is having difficulties. There are different Series rounds of investments. S4_BVD Sub Series Number 17 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.

10. Re: Calculation field returning repeating value ie: 76007600760076007600 instead of just 7600
lbl159 Aug 24, 2015 3:32 PM (in response to 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 Aug 24, 2015 3:40 PM (in response to lbl159)Because in this calculation, you don't have a default result for your If(). In the other you do.
If test is True (any nonzero 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 Aug 24, 2015 3:45 PM (in response to 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 Aug 24, 2015 3:58 PM (in response to DavidJondreau)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 Aug 24, 2015 8:30 PM (in response to lbl159)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.