Changing Variables in Let Function

[Jake Sterling]

Note: Please nobody spend a lot of time trying out the long calculation at the bottom. It works fine and I don't need a solution to it. I am just trying to learn if there is a way to change a variable within that is nested inside a previously determined variable --Jake

Is there any way to change a Let Function variable once it is set? Here is an example that doesn't work, but may illustrate what I am trying to do:

Let ( A = "text" ;

Let ( text = "Go " ; A) &

Let (text = "figure." ; A)

)

The answer I want to come up with is "Go figure." Of course the answer I really get is "text."

I have experimented with using the Evaluate function but without any success.

Ultimately, what I want is a variable (EndText) that contains a counter variable ("counter") inside the MiddleValues function -- MiddleValues ( text ; startingValue ; numberOfValues ) -- and have the counter change at each iteration of EndText.

Let ( EndText = "MiddleValues ( FieldName ; counter ; 1)" ;

"* " & Let ( counter = 1 ; EndText ) & "¶" &

"* " & Let ( counter = 2 ; EndText ) & "¶" &

"* " & Let ( counter = 3 ; EndText )& "¶"

)

If I started out with a value list:

Horses

Zebras

Donkeys

What I want to end up with is

* Horse

* Zebras

* Donkeys

(Yes, I know there are easier ways to do this specific thing, but I am trying to keep my example simple.)

So, my question is, is there any way to change the variable "counter" within the previously defined variable "EndText"?

In case anyone is interested, what I am actually trying to do is take two value lists and combine them so that all possible combinations are represented.

List 1 (cities)

X Roxbury

0 Dorchester

X Cambridge

List 2 (Groups)

X Students

X Parents

0 Teachers

0 Adminstrators

If three cities and two groups are selected (those are the one's with the X's) I want to end up with a list that looks like this:

Roxbury-Students

Roxbury-Parents

Cambridge-Students

Cambridge-Parents

I apologize about my use of the words "variable" and "parameter", I am a little fuzzy about how these terms are used in the Filemaker context.

I have worked out a perfectly good solution for this (see example), but it would be more elegant if I were able to change paremeters in the mid-stream, so to speak.

HERE IS THE CALCULATION I AM USING NOW

--The two fields referenced are gi_Sector and gi_Affiliation.

Let ( [

Sect1 = If ( MiddleValues ( gi_Sector ; 1 ; 1) > 0 ; Substitute ( MiddleValues ( gi_Sector ; 1 ; 1) ; "¶" ; "" ) ; "" ) ;

Sect2 = If ( MiddleValues ( gi_Sector ; 2 ; 1) > 0 ; Substitute ( MiddleValues ( gi_Sector ; 2 ; 1) ; "¶" ; "" ) ; "" ) ;

Sect3 = If ( MiddleValues ( gi_Sector ; 3 ; 1) > 0 ; Substitute ( MiddleValues ( gi_Sector ; 3 ; 1) ; "¶" ; "" ) ; "" )

] ;

If ( IsEmpty (MiddleValues ( gi_Affiliation ; 1 ; 1 ) ) ; If (Sect1 > 0 ; Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Sect2 ; "" ) & If ( Sect3 > 0 ; "¶" & Sect2 ; "" ) ;

Let ( var1 = 1 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 2 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 3 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 4 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 5 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 6 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 7 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

) &

Let ( var1 = 8 ;

Let ( Affil1 = Substitute (MiddleValues ( gi_Affiliation ; var1 ; 1 ) ; "¶" ; "" ) ;

If (

Affil1 > 0 ;

"¶" & Affil1 & If ( Sect1 > 0 ; "-" & Sect1 ; "" ) & If ( Sect2 > 0 ; "¶" & Affil1 & "-" & Sect2 ) & If ( Sect3 > 0 ; "¶" & Affil1 & "-" & Sect3 ) ;

""

)

)

)

)

)

Edited April 10, 2006 by Guest

[Ender]

I didn't read all that, but in your first example, there are a couple problems:

Let ( A = "text" ;

Let ( text = "Go " ; A) &

Let ( text = "figure." ; A)

)

First, the variable A is defined as a string of text "text", and you never try to evaluate it. So you'd get the result "texttext". But even if it's written like

Let ( A = "evaluate("text")" ;

Let ( text = "Go " ; evaluate("A")) &

Let ( text = "figure." ; evaluate("A"))

)

it will fail because the variable A cannot know the values of the variables defined after it. When A is evaluated at the top, the variable text is undefined, so A returns that undefined value when it's evaluated later.

I'll try to decipher your other calc if I get a minute.

[Ender]

In case anyone is interested, what I am actually trying to do is take two value lists and combine them so that all possible combinations are represented.

This is simple solve if you have access to FM7 Developer or FM8 Advanced. Here's a Custom Function that does just this:

http://www.briandunning.com/cf/393

Otherwise, you have to hardcode the calc to handle a specified maximum number of values, and you end up with a long calc like what you have.

[Jake Sterling]

Ender,

Yeah, I know it will fail. I just put that calculation in to illustrate what I was trying to do. And I already tried the Evaluate thing, too. At this point, I just wanted to know if there was any way to do get a variable inside a variable to change. I have a feeling that the answer to my question is, "No, you can't do that with FileMaker."

By the way, don't spend time on that long calculation. It works just fine. I just offered it simply as an example of how this issue arose.

Cheers, --Jake

[Ender]

In FM8, a variable can be redefined, but that wouldn't solve the problem here. You need a recursive function since the number of values is indeterminate. So "Yes, you can do this with FileMaker," but you need the Developer or Advanced edition to install it. :P

[comment]

Another option is to use a repeating calculation field, as shown here.

[Jake Sterling]

Thanks to everyone who responded. I do have the version 7 Developer, but I haven't used it a lot yet and I hadn't realized that the Custom Functions could do recursive calculations. Looking over the function that Ender recommended (http://www.briandunning.com/cf/393) really helped. It does exactly what I was trying to do in a much simpler way -- Plus, I just expanded my understanding of what Filemaker could do in a huge way. This is why it is so great to have colleagues!

Cheers, Jake