How to construct a variable name in an execute statement?


#1

I want to use the formulafill function within an execute statement to access an array, part of whose name is derived from the first character of a value in a database. For a record whose value in the field A3 begins with “4”, I want to access the array B4. In a simple example:

field A3 formulafill B4

I want to do this for multiple fields but I am having difficulty coding an execute statement which inserts the correct value of Bn into a statement for multiple fields. This doesn’t work:

execute {formulafill B} + info("fieldname")[1,1]

and nor does this:

execute {formulafill B} + info("fieldname") + {[1,1]}

How else might I construct the array name?


#2

When I’m having trouble with an execute statement, I change execute to message to see what is going on. In your first example, the result will be

formulafill BB

so it’s pretty obvious why it doesn’t work. If you change it to:

execute {formulafill B} + info("fieldname")[2,2]

the result will be

formulafill B3

which is close, but not quite what you are looking for. I think what you need is:

execute {formulafill B} + (val(info("fieldname")[2,2])+1)

I believe you could forget about execute and use the fieldvalue( function instead.

formulafill fieldvalue("B"+(val(info("fieldname")[2,2])+1))

Instead of a text funnel you could use the striptonum( function.

formulafill fieldvalue("B"+(val(striptonum(info("fieldname")))+1))

#3

If I understand your setup correctly, you do not want to build the array name based on the field name, but of the contents of the field. So I think you have to use something like

field A3
formulafill fieldvalue(“B”+A3[1,1])


#4

So do I. But that didn’t help in this case.

None of your solutions work - I keep getting the error message, “data field B4 doesn’t exist.”


#5

That’s precisely what I want to do. But this doesn’t work either - I get the same error message as from Jim’s solutions.

I have to run out at the moment but I’ll look at this again when I return. I woke up at 2 am thinking about using chevrons or the chevronquoted( function so I’ll check that out too.


#6

I would try:

execute {formulafill } + datavalue("B"+(val(striptonum(info("fieldname")))+1))

#7

That one causes a syntax error failure.

And I may have inadvertently mislead people by my initial example. I don’t necessarily want to fill A3 with B4, I want to fill it with the array whose name is B followed by the first character of each record in A3. So I don’t want to add 1 to anything.

Just to clarify - if a value in A3 begins with 8, I want to replace that value with the contents of the variable B8. If it begins with 1, I want to replace that value with the contents of the variable B1.

The actual task I’m applying this to is far more complex than this but the problem remains the same - I want to generate a variable name from the first character of every value in the field A3.


#8

And, if you use the fieldvalue( function, you have to use fieldvalue(FieldName1+"") to get the value rather than the name.


#9

This works, after a fashion:

execute {formulafill Bn} + fieldvalue(FieldName1+"")[1,1]

but it fills every record with the Bn value appropriate to the currently active record. It doesn’t calculate a new value of n for each record.

How can we tweak it to respond to each record?


#10

This does the job:

execute {formulafill datavalue("B" + datavalue(} + info("fieldname") + {)[1,1])}

I didn’t really solve this myself - I wouldn’t have got there without your collective help. Thanks to all.


#11

You’ve resolved it but one trouble shooting technique that I’ve found useful with Execute is to put the formula result on the Clipboard so that I can look at it and see clearly what is being executed - or attempted. It has saved me a lot of pain and suffering.

Clipboard = {formulafill datavalue(“B” + datavalue(} + info(“fieldname”) + {)[1,1])}


#12

I don’t see where execute is giving you anything here. I think you could just write this as

formulafill datavalue("B"+«»[1,1])

The reason Jim and Kurt’s suggestions gave you errors is that they were thinking B3 was a field instead of a variable. Fieldvalue( won’t return the value of a variable.


#13

I’m sure you’re right Dave but the reason that I wanted to fit it into an execute statement is that the final code is a tad more complex:

execute {formulafill ?(array(datavalue("Bn" + datavalue(} + info("fieldname") + {)[1,1]),val(}
     + info("fieldname") + {[1,3])-datavalue("Diff" + datavalue(} + info("fieldname") + {)[1,1]),cr())
     contains } + info("fieldname") + {,"",} + info("fieldname") + {)}

I suspect that this has to be an execute statement but I’m prepared to stand corrected - I can see that it might be possible but I’m not brave enough to try at this stage.


#14

With A3 as the current field, I replaced execute with clipboard= and I got this.

formulafill ?(array(datavalue("Bn" + datavalue(A3)[1,1]),val(A3[1,3])-datavalue("Diff" + datavalue(A3)[1,1]),cr())
     contains A3,"",A3)

Since A3 was the current field every instance of A3 or datavalue(A3) can be replaced by «», resulting in

formulafill ?(array(datavalue("Bn" + «»[1,1]),val(«»[1,3])-datavalue("Diff" + «»[1,1]),cr())
     contains «»,"",«»)

The execute formula has to be evaluated first, so that the command will know what code to execute. The names of the variables change from one record to the next, so a single calculation at the start isn’t going to be any help there. The execute formula does calculate the name of the current field, which remains constant, but that can be replaced by «».


#15

Good one Dave. It’s no faster than the execute approach but it’s certainly a lot easier to comprehend and to adapt to a different situation.