Can't GetNthRecord using a variable

[PerryL7]

Hi all,

I am working on a script that merges two duplicate records. Currently, I have it setup to go to the merge layout, cycle through each field, and copying the information from the other record to the current record. The script looks like such.

Go to Layout["Merge Layout']

Go to the first field

Start loop

Set $CurrentFieldName to ActiveFieldName

Set Field[GetNthRecord ( $CurrentFieldName ; Get ( RecordNumber ) + 1 ) )]

Go to Next Field; Exit loop if last field

End loop

The problem is that the information being returned from the GetNthRecord function is just the value in the variable $CurrentFieldName and not the information stored in that field. I can't figure out how to use a variable with the GetNthRecord function. Does anyone have any ideas?

Thanks,

PerryL7

[Fitch]

Maybe you need to use Evaluate on the variable. (Not tested.)

[The Shadow]

Curious, I just tried the calc:

  Let ( $Fieldname = "txt"; List( GetField($Fieldname)) )

where "txt" is the name of a repeating field, it correctly collected all three reps, so I think GetField() or Evaluate() will do the trick for you.

[Genx]

Well I would think you would need something like evaluate a string isn't a field unless its evaluated.

[Søren Dyhr]

Let ( $Fieldname = "txt"; List( GetField($Fieldname)) )

It's just a shame, you can't set a multi repetions $ or $$ variable this way ...why havn't Extend found it's way in to this path yet?? Or am I ignoring something here??

--sd

[mr_vodka]

I second that. : Until they do, using repeating variables are kind of useless.

[Søren Dyhr]

Well not entirely, I tend to use a dummy $Assign like I do it here:

http://www.fmforums.com/forum/attachment.php?attid/10846/

...but they still have a long way to come!

--sd

[mr_vodka]

We can use repeating variables for our own convenience, but practicality-wise its "almost" useless IMHO.

Sure we can set up a repeating variables such as $color and then set it as:

Set Variable [$color; "red"]

Set Variable [$color[2]; "blue"]

Set Variable [$color[3]; "green"]

However, it is not that much more better than using:

Set Variable [$red; "red"]

Set Variable [$blue; "blue"]

Set Variable [$green; "green"]

As I stated earlier, it is "almost" useless. It can help slightly in efficiency with cases such as:

Set Variable [$k; 1]

Set Variable [$allcolors; "red green blue"]

Loop

Set Variable [$color[$k]; MiddleWords ( $allcolors ; $k ; 1 )]

Set Variable [$k; $k +1]

Exit Loop If [$k > WordCount ( $allcolors )]

End Loop

But its use is limited.

[Søren Dyhr]

I wondered if there was a way to make a string via substitution, that let you assign a pilcrow delimited into each rep via an evaluate, in that case would it be a highranking CF I would include in every solution.

Something in the vicinity of this:

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

--sd

[comment]

Something like this?

DefineRepeatingVariableCF.fp7.zip

[Søren Dyhr]

Very good indeed! It seems to work ...who need Extend then??

--sd

[mr_vodka]

Very nice Michael. :

[Søren Dyhr]

The good question is for how long time he have used it secretly??

What was the other issue, they not were up to yet?? Aggregation over the array ...this could be CF'ed as well couldn't it??

I would say, Michael rush it to Dunnings site pronto!

--sd

[comment]

how long time he have used it secretly??

Take the time of my post and subtract about 10 or 15 minutes...

I must say, I don't quite see the advantage of this. Why not simply drop the list into a single variable, then get the one you need by GetValue ($list ; $i )?

With the repeating variables, you can't even tell how many of them are there. That would be a major obstacle to any custom function trying to aggregate them, I think.

[comment]

BTW, that was really only a sketch - you'll notice it will break if your variableName contains "x".

Here's a slightly improved version:

Let ( [

n = ValueCount  ( listOfValues ) ;

item = GetValue ( listOfValues ; n ) ;

template = "Let ( §variableName§[§n§] = §item§ ; 0 )"

] ; 

Case ( 

n > 1 ; RepeatingVariables ( variableName ; LeftValues ( listOfValues ; n - 1 ) ) & " &¶"

)

&

Case ( 

n ; Substitute ( template ; [ "§variableName§" ; variableName ] ; [ "§n§" ; n ] ; [ "§item§" ; Quote ( item ) ] )

)



)

Wow, color-coded code!

Edited June 8, 2007 by Guest

[Genx]

Sorry... so the point of this is to take:

Test¶Test2¶Test3¶Test4

And put it into repetitions of a named variable?

$Test=Test

$Test[2]=Test2

$Test[3]=Test3

$Test[4]=Test4

... Why?

Lol, but yay for the code, its a heck of a lot clearer. See, FM needs something simply like that.

[The Shadow]

One possibility I see for this is that the variable could represent for example, a 2-dimension matrix of numbers, and you could put the matrix dimensions into var[-1] and the actual values into var[0], var[1], var[2], var[3] for a 2x2 matrix.

With this, then you could make custom functions that take two matrixes and multiply them together, etc.

Another thing, is that each repetiton of the variable can hold a list of values, which is something a flat list of values can't do, since you would need to add extra indicators of where the sublists started and ended.

Edited June 9, 2007 by Guest
One more thing...

[The Shadow]

Here's another idea, since there is no cost to having missing repetitions, you could have a table of zip codes that you load up in your startup script and use it rather than a relational lookup:

$$CityName[ zipCode ]

since the relational lookup (besides probably being slower), would cause the calculation to be unstored, where this technique would not.

I've attached a simple example showing the use of this.

zipCodes.fp7.zip

Edited June 9, 2007 by Guest
Attaching example file

[Genx]

Yeah I was playing with this a few moments ago.. but here's a general discovery that may or may not be apparent to some of you -- you can name your repetitions i.e.

Instead of providing a number you can specifiy a custom key:

$record["name"]

Just enclose the repetition name in quotes when specifying it in set variable.

[The Shadow]

That makes a variable usable an associative dictionary, which is one of the power tools perl has at its disposal (as well as other languages). Its a direct mapping between two arbitrary strings.

So, if you wanted to count all distinct words in a piece of text, you could loop over the words an increment wordCount[word] each time you saw one.

As opposed to my zipCode example, any fixed table could be entirely loaded into variables for fast non-relational lookup purposes.

[comment]

I think there would be a problem with codes starting with zero/s. It's too bad you cannot use text as repetition index, although 0 and negative could be potentially useful - I didn't know you could do that.

[The Shadow]

That's why I did a GetAsNumber() on the zipcode in my example, to strip off the leading zeros and any other punctation or whitespace that might cause confusion.

Edited June 9, 2007 by Guest
Nope, I was wrong, that doesn't work.

[Genx]

Yeh, I've been using PHP lately and arrays are infinintly useful (especially with key=>value pairing) so thought i'd try.

I initially did it through a let statement but then it seems that the rep option happily accepts it directly in the rep field for the set variable script step.

[comment]

the rep option happily accepts it directly in the rep field for the set variable script step.

If by "it" you mean text, then yes, it accepts it - but when you call it, it's evaluated to a number.

[The Shadow]

Okay, so then we just need a custom function that encodes a (relatively) short string as a number.

Since the FileMaker numbers support a large number of digits, it should be possible to encode any small string value uniquely as a number, *then* it could be used as a variable repetition to get the associative array effect.

[Genx]

... no, no its not:

Let( $name["test"] = "abcdefghijk" ;

$name["test"]

)

Try setting the variable using the set variable script step -- works fine

[Genx]

Your passing it a string as a key, and requesting a value at a position in the same fashion.

The FileMaker calculation engine, as far as i can tell deals with anything enclosed with quotes as a string variable, not a number.

[comment]

The FileMaker calculation engine, as far as i can tell deals with anything enclosed with quotes as a string variable, not a number.

That's true up to a point. That point is where the parameter must be a number - and that's where it gets converted to a number.

Why don't you perform a simple test:

Set $variable[0] to "zero" ;

Set $variable["text"] to 'MyText"

Now check the value of $variable[0].

[Genx]

I did -- and that will work, but it will also assign the text value.

Why don't you perform a simple test:

Set $variable["text"] to 'MyText"

Set $variable["textTwo"] to 'MyTextTwo"

Then try: $variable["textTwo"], it will not return "MyText".

Likewise, $variable["text"] will not return "MyTextTwo" which would be the last thing set at $variable[0]

Edited June 9, 2007 by Guest

[The Shadow]

Here's an example that uses a custom function to encode a short string as a number - I'm not sure I caught everything here, but this should mostly work for english that doesn't have a lot of weird punctation.

zipCodes.fp7.zip

[comment]

I'm afraid I am getting different results than you are.

Untitled.fp7.zip

[The Shadow]

I'm using 8.5 Advanced, and the setting with strings isn't working for me either (they just set rep zero).

Genx, are you really using 8.0 Advanced as your profile says?

[comment]

To be more precise, they set repetition GetAsNumber ( string ), I think.

[Genx]

How undelitefully odd:

Untitled.zip

[Genx]

No -- 8.5 sorry.