0

I have a FileMaker database where I want to calculate the median value of 3 different fields. I used the code that can be found here.

In my setup, it looks like this:

Let ([
beak.trait = List (beak_depthA; beak_depthB; beak_depthC);
valuelist = SortValues ( beak.trait; 1);
count = WordCount ( valueList );
odd =  MiddleValues ( valueList ; Truncate (count/2;0) +1 ; 1 ); 
even = MiddleValues ( valueList ; Truncate (count/2;0)    ; 1 )];
Case( Mod ( count ; 2 ) ; odd ; (even+ odd) / 2))

When there is an even number of data in the cells it generates this:

However, when including 3 measurements, I get this: enter image description here

But if I look into the data, it's there, with an extra line return... enter image description here

Would there be a way to get the value, when the number of fields is odd without the extra return?

How the code would be affected?

1

Extra return might comes from SortValues function. Use Trim4 custom function to remove it.

Trim4 ( SortValues ( beak.trait; 1) )

Use it for MiddleValues functions too.

| improve this answer | |
  • This seems to require FileMaker pro advanced. I only have the "pro" version. Is there a way I can put this in a custom function? – M. Beausoleil Dec 10 '19 at 19:19
  • What is your FileMaker version? If it is 17+, you can enable Advanced Tools in preferences. If it is 16-, you can add the entire custom function calculation to your calculation. – Siva Dec 12 '19 at 1:23
  • yeah, I have FMP 16... ok good to know – M. Beausoleil Dec 12 '19 at 20:15
  • I've tried adding it to the function, but it doesn't work. How Trim4 would it be included into the function ? – M. Beausoleil Dec 16 '19 at 18:57
  • Here is what I tried Let([ beak.trait = List (beak_lengthA; beak_lengthB; beak_lengthC); valuelist = SortValues ( beak.trait; 1); sT = Char(9) & Char(13) & Char(32) & Char(160); C1 = PatternCount(sT; Left(valuelist; 1)); Cn = PatternCount(sT; Right(valuelist; 1)); Ca = C1 + Cn]; count = WordCount ( If(Ca; Middle(valuelist; 1 + C1; Length(valuelist) - Ca); valuelist) ); odd = MiddleValues ( valueList ; Truncate (count/2;0) +1 ; 1 ); even = MiddleValues ( valueList ; Truncate (count/2;0) ; 1 )]; Case( Mod ( count ; 2 ) ; (odd) ; (even+ odd) / 2)) – M. Beausoleil Dec 16 '19 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.