"Let" Calc causes near infinite loop?

[Alan H]

So, I am trying to recover, and any ideas would be appreciated.

I have a database with about 7,000 records in one table. I created a calculation that was supposed to "catenate" two text fields. The calculation was (from memory) something like

Let ( [$value1 = field1;

$value1 = Trim ($value1);

$value1 = $value1 & If (Right ($value1, 1) ≠ "."; "."; "");

$value2 = field2;

$value2 = Trim ($value2);

$value2 = $value2 & If (Right ($value2, 1) ≠ "."; "."; "");

$value = $value1 & " " $value2

];

$value;

)

So I typed this in, hit return, and suddenly FMPro says that it is taking about 1 min/record to update the field--which for 7,000 records is about 4 days. I finally had to kill FMPro, and when I looked at my database after killing the program, it had grown from 1MB to 3GB.

Is this normal?

[Genx]

Why are you using variables here? You don't have to so just use the standard syntax:

Let ( [

x = Trim (field1);

x = x & If (Right (x; 1) ≠ "."; "." );

y = Trim (field2);

y = y & If (Right (y; 1) ≠ "."; "." )

];

x & " " & y

)

Try that. Also you might consider not storing the result of the calculation.

Edited February 18, 2007 by Guest

[Alan H]

That was it.

Why was I using dollar signs? Because I thought that they were required by FileMaker syntax.

Interestingly, all my other "Let" calculations in this database were using $'s, and were returning correct results. But in this case, there must have been some sort of recursion, because the database grew at least 300x in size when I introduced this calculation.

Thx

[comment]

It's usually better NOT to use $variables in a Let() function, unless you have a good reason to do so. However, it IS possible, so that's probably not the cause of your problem.

[Genx]

Yeah, I don't think it was using variables either but just thought i'd point out that.

I still don't think that using that many variables in a calc is a good idea, but it was probably something with the syntax -- maybe there's some way to achieve recursion using just variables?

[comment]

I don't think so. I believe the Let() function is evaluated in the order it was written (disregarding for the moment parenthesses and nesting). So when you re-define a variable, it takes on the new value, and the function moves forward - never back.

[Genx]

I wonder what was responsible then... Probably wasn't incorrect syntax, FM kind of throws fits when you try that : . There is a semi colon after $value where there shouldn't be one, but that's probably just clerical error. Hmmmm...

[comment]

The calculation was (from memory) something like

I'd rather not speculate until we see the actual formula.

[Genx]

Didn't see that bit.

[The Shadow]

It seems more likely to me that the original formula was only appending to the variables, and never setting/clearing them. That would explain why the database was growing larger, the second record had the values from the first plus its own, etc, etc.

[Genx]

Wouldn't that require the variables to be global though?

[Alan H]

Hi

It turns out that removing the $ fixes the problem. Perhaps there was some catentation of previous values as suggested. Perhaps what happens is that the $ syntax does something strange in this context, like dereferencing a string as a variable. I don't know if this is a feature or a bug, but I reported it to FileMaker. They can contact me for more info if requested

The actual calculation that was causing the error was

Let ( [ $result1 = t Conabio locality;

$result1 = Trim ( $result1 );

$result1 = $result & If (Length ($result1) > 0 and Right ($result1; 1) ≠ "."; "."; "");

$result2 = t Conabio Habitat;

$result2 = Trim ( $result2 );

$result2 = $result & If (Length ($result2) > 0 and Right ($result2; 1) ≠ "."; "."; "");

$result = $result1 & If ($result1 ≠ "" and $result2 ≠ ""; " "; "") & $result2

] ;

$result

)

(Please don't comment that there is an easier way to achieve what I was trying to do--I know that).

Note that there is a "bug" in that the variable $result is used before it is set, which may mean that it is picking up the previous value?

My variable naming conventions include starting variable names with a "t" when they are text fields.

Thanks for all the thoughts. As I said, I fixed the problem.

And, as I said, upon declaring a variable based on this caculation (or upon trying to import data into an empty database with this calculation), I almost immediately run into a very wait dialog, taking about 1-minute per record, and the database quickly balloons from 10^7 bytes to 3*10^9 bytes.

A

Please contact me at [email protected] if you really want to follow up on this.

Edited February 19, 2007 by Guest

[The Shadow]

Ah! I called it.

Since $result will stick around till the current script exits (which is until the file closes if not inside a script - really, try it), the $result stuck around and kept growing for each record processed.

[Genx]

How interesting, so if you set a $var in a field, it will remain set until the file closes? What happens if you change the value in a script?

Hmmmm, good work :(

Edited February 21, 2007 by Guest