Temporary calculation filed and results < 1

[enquirerfm]

I have a calculation which is generally working. It processes  a lot of numbers. But I've run into some problems with some older data processing after updating some figures. Part of the script to calculate the value I need includes this line which is where I think it is going wrong:

 

If[File::cWeightedAverage  < 1]

File = the FM file and cWA is the field which is calculated using a formula. However, what is the field value doesn't return a value less than '1' because it is blank? How do I put that into the script?

 

e.g. main script section which includes this line is:

 

 

Go to Field [File::cWeightedAverage]
copy [select]
If[File::cWeightedAverage  < 1]
Go to Field [File::month_may2002]
Copy [select]
EndIf
 

 

etc.

[mr_vodka]

I am not sure what the rest of the script does that you havent posted but if you are trying to set a field with value, instead of using copy / paste, you can just use Set Field [ ]

 

 

For example you can do it in one step if I follow correctly:

 

Set Field [ YourTargetField; Case ( cWeightedAverage  < 1; month_may2002; cWeightedAverage ) ]

[Fitch]

You could either change the cWA calculation to always return a result (try unchecking the "do not evaluate if all referenced fields are empty" checkbox); or, change the script to:

If[ IsEmpty( cWA or cWA < 1 )]

I'd also like to point out that if you are using the "copy" command in order to move data within FileMaker, there is a better way, which is to use the Set Variable command instead of Copy, and the Set Field command instead of Paste.

One advantage is that the source and target fields don't have to be on the layout, and another is that you don't mess with the user's clipboard. (Go to Field is also not necessary unless you have some other reason for it.)

[doughemi]

Fitch:

 

Shouldn't your script step be

If[ IsEmpty( cWA) or cWA < 1 ]

[Fitch]

Yes. Thanks for catching that. I'm slipping in my old age.

[enquirerfm]

Thanks everyone. The problem is still there... it is picking up loads of data from a related file instead of the figures it is supposed to and it works fine on other months. Anyway, this what I now have which seems much shorter than my Copy, Paste, If... EndIf :

 

 

 

Set Field[File::month_May2005;Case (IsEmpty(File::cWeightedAverage or File::cWeightedAverage < 1);File::month_WA_apr2005;File::cWeightedAverage)]
 

However, this isn't working because it doesn't replace the target field (File::month_May2005) with the previous month's value (File::month_WA_apr2005) based on the  IsEmpty query. Any suggestions?

 

The idea is it will pick up a value from the previous month. So, so long as there is a value in the first month of the whole series then this will work because it will pull the value from the previous etc.

Edited April 11, 2013 by enquirerfm

[enquirerfm]

OK think I got it working - had brackets wrong place

 

better: Set Field[File::month_May2005;Case (IsEmpty (File::cWeightedAverage) or File::cWeightedAverage < 1);File::month_WA_apr2005;File::cWeightedAverage)]

[Fitch]

That looks fine. I might format it something like:

Set Field[File::month_May2005;

Let( avg = File::cWeightedAverage ;
  Case( IsEmpty (avg) or avg < 1) ; File::month_WA_apr2005 ; avg )
   )

]

 

It's worth considering any time a field occurs more than once in a calculation.

[bruceR]

Another way to handle the empty value is to coerce the result to number by adding zero to it:

 

Case( ( 0 +  avg ) < 1) ; File::month_WA_apr2005 ; avg )

 

or more rigorously:

 

Case( getAsNumber( 0 +  avg ) < 1) ; File::month_WA_apr2005 ; avg )

[enquirerfm]

This is great and it has so simplified the scripting. I don't get any funny results any more. Thanks to everyone.

[enquirerfm]

I just want to be clear on this...

 

I set the date for the month's calculations as :

 

Set Field [gDate; Date (12;1;2012)] for example this is for December - I notice FM doesn't accept Date (12;*;2012).

 

If records are uploaded on certain dates ito my file, say 4th, 12th, 21st and 30th how would I find the records cWeightedAverage for only the records on a specific date?