dynamic variable name - is it possible

[rivet]

is there a trick to create a dynamic variable name?

[comment]

Evaluate()?

[rivet]

I was trying variations of:

Let (

[

win = Get ( WindowName ) ;

key = account::zp_account ;

Evaluate ( "$$" & win & key ) = win&key ] ;

1

)

[comment]

Try:

Let ( [

vName = ... ;

vValue = ... ;

formula = "Let ( $$" & vName & "= " & vValue & "; "")"

] ;

Evaluate ( formula )

)

[comment]

Or even:

Let ( [

vName = ... ;

vRep = ... ;

vValue = ... ;

formula = "Let ( $$" & vName & "[" & vRep & "] = " & vValue & "; "")"

] ;

Evaluate ( formula )

)

[rivet]

as always... thanks comment.

[doughemi]

comment's calc in post #5 will get me exactly where I want to go, but I don't understand how to get that calc into a script.

[eos]

Use Set Variable [] to declare a dummy variable; use the calculation engine to create your dynamic variable(s); pass an empty result for the dummy variable since it's … well, a dummy   and you're merely interested in the side effects.

[doughemi]

 it's … well, a dummy 

 

And so am I, because I still don't get it.  Could you give me a concrete (like my head) example?  I want to end up with 

 

Set Variable[$RegionCount[$reg] ; Get(FoundCount)]

 

where $reg contains a previously defined number.  I get the calculation part:

 

Let ( [

vName = "RegionCount" ;

vRep = $reg ;

vValue = Get(FoundCount) ;

formula = "Let ( $" & vName & "[" & vRep & "] = " & vValue & "; "")"

] ;

Evaluate ( formula )

)

 

what's eluding me is getting it into the script .

[eos]

I want to end up with 

 

Set Variable[$RegionCount[$reg] ; Get(FoundCount)]

 

No; what you want to end up with is a variable called $RegionCount which in some repetition holds the found count.

 

Just use

 

Set Variable [

 

  $dummy ;

 

  Let ( [

    vName = "RegionCount" ;

    vRep = $reg ;

    vValue = Get ( FoundCount ) ;

    formula = "Let ( $" & vName & "[" & vRep & "] = " & vValue & "; "")"

    ] ;

    Evaluate ( formula )

  )

]

 

Now you have defined two variables: the one you wanted from the calculation, plus one you can ignore from the script step.

 

You could basically use any script step that gives you access to the calculation engine, but using Set Variable[] strikes me as the most straightforward.

[doughemi]

!

 

Thanks for your patience.

[eos]

Thanks for your patience.

 

De nada! 

[doughemi]

Still no joy.  I entered the expression

 

 Let ( [

    vName = "RegionCount" ;

    vRep = $reg ;

    vValue = Get ( FoundCount ) ;

    formula = "Let ( $" & vName & "[" & vRep & "] = " & vValue & "; "")"

    ] ;

    Evaluate ( formula )

)

 

into the Data Viewer and it returned a "?" with the error message "This text constant does not end with a quotation mark." and the red portion of the expression highlighted. I can't see where it thinks a quote mark is missing. 

[eos]

There's a problem with the quote(s) you pass as dummy result.

 

Try

formula = "Let ( $" & vName & "[" & vRep & "] = " & vValue & "; "")"

which – using placeholder values for your vars – yields the syntactically correct expression of

 

Let ( $RegionCount[1] = 22; "")

[Kris M]

I had to wrap the vValue in quotes for it to work for me.

formula = "Let ( $" & vName & "[" & vRep & "] = " & Quote ( vValue ) & "; "")"

[flummi]

Hi all.

I know this is an old post but it fits exactly what I need to do. So I am hoping that posting a question here will nevertheless be read.

My problem is this:

I have a table that holds a record per day for a period of time with date, time and a value. I want to create a report that shows the date, time and value for each record in the table in three columns for a block of three weeks across the page, newest week first. So this is what I want to see:

week 18  01.05.2022  165        week 17  24.04.2022  147      week 16   16.04.2022    168

                02.05.2022.                              25.04.2022   134                       17.04.2022    195

                03.05.2022  139                       26.04.2022.                               18.04.2022    117

                .......

               06.05.2022   100                       30.04.2022   129                       23.04.2022

week 15  22.04.2022               week 14  .....                            week 13    .....

etc.

In order to achieve this I want to create an array of 7 x 3 entries for each data field of the first three weeks (and subsequently for each block of three weeks):

datum1[1 ... 3], time1[1 ... 3] and value1[1 ... 3] for Sunday of the week in question

datum2[1 ... 3], time2[1 ... 3] and value2[1 ... 3] for Monday

etc.

I tried to use the technique suggested in this post: evaluate (let ...) 

Here is what the FM code looks like for every record in a loop:

Set Variable [ $Dummy ; Value:

                           Let (        

                                    [    vName = "Datum" & $WT ;          

                                          vRep = $Spalte ;          

                                          formula = "let ( $" & vName & "[" & vRep & "] = " & Parameters::Datum &  "; \"\")";          

                                         $Debug = formula        

                                    ] ;         

                                         Evaluate (formula)      

                                 )

                       ]

$WT is the day of the week (1 to 7)

$Spalte is the report column (1 to 3)

Parameters::Datum is a field in the table

Here is what the "formula" looks like in the debugger:

$Debug:  let ( $Datum1[1] = 01.05.2022; "")

This looks ok to me and yet the variable is not created and in the debugger I get an error <Field Missing> when I use the above code in the debugger watch feature.

Can anyone hint me to the problem?

Any help will be greatly appreciated.

Thank you.

Hans

 

 

[comment]

I don't see how the technique discussed in this thread is supposed to help with your task.

Do you want to print your report or display it on-screen?

--
P.S. Please update your profile to reflect your version and OS so that we know what you can use.

 

Edited May 2, 2022 by comment

[flummi]

I am using FM 18 on Mac OS Monterey 12.3.1.

My question is why the technique discussed in this thread does not deliver what I wanted to achieve, which is to create a variable, no matter what I do with it. I just mentioned the report in order to give some insight into the context.

The let statement looks syntactically correct ( as the $Debug variable shows) and references two local variables defined prior to the it and a field defined in the table that is being processed. Why would it not create the variable but rather display a <Field Missing> error?

[comment]

It is difficult to reproduce the problem based on your description. AFAICT, you are getting the error at the final step, when evaluating the formula  variable.

8 hours ago, flummi said:

Here is what the "formula" looks like in the debugger:

$Debug:  let ( $Datum1[1] = 01.05.2022; "")

This looks ok to me

This does not look OK to me. The process you are using expects the value to be a number. You are feeding it a date or text that looks like a date. If you want the variable to be of type text, then the value must be enclosed in quotes:

Let ( $Datum1[1] = "01.05.2022" ; "")

If - more likely - you want it be of type date, then the best way would be to make the formula look like this:

Let ( $Datum1[1] = Parameters::Datum ; "")

which is easy to do: just replace the & Parameters::Datum & part in the formula definition with & GetFieldName ( Parameters::Datum ) & . This is assuming that Parameters::Datum is a Date field.

 

1 hour ago, flummi said:

I just mentioned the report in order to give some insight into the context.

Well, I still don't see why you would need this method for the described problem and I suspect you are overcomplicating it.

 

[flummi]

I very much appreciate your thoughts. Thank you so much. I was indeed misled by the appearance of the let statement in the debug variable. I implemented your correction and it now creates the variables. Great!

Why did I choose this method? Well, the task does not seem to be achievable by sorting. I could of course print a report that shows date, time and value per record in a single column line by line but that would leave a lot of white space on the page and a comparison of figures would be made more complicated. That's why I wanted to print as many weeks as possible across the page. In the example where I use three columns the entry for the date in column three on line one of the report is from record fifteen in the table. 

Hence my idea was to put all values for three weeks in variables and then print the array line by line.

I would be very happy if there were a simpler solution.

[comment]

4 hours ago, flummi said:

I would be very happy if there were a simpler solution.

Well, I cannot say that I fully understand your solution (how do you "print the array line by line"?), but...

Even if going along with your idea of creating a dedicated variable for each individual value, there is no need to use a calculated name. You could simply set the repetition number to say something like: 

10 * $dayOfWeek + $columnNumber

or more generally:

10 * $rowNumber + $columnNumber

to accommodate any number of rows, not just 7 days of a specific week.

But I believe it would be much simpler to set a single variable to a return-separated list of all dates in the found set, in this order: by week, descending + by date, ascending, and use a reporting table where a calculation field with 3 repetitions would retrieve the values from this variable using a formula like:

Let ( [
n = Get(RecordNumber) - 1; 
block = Div ( n ; 7 ) ;
row = Mod ( n ; 7 ) + 1
] ;
GetValue ( $$dates ; 21 * block + row + 7 * ( Get(CalculationRepetitionNumber) - 1 ) )
) 

Finally, I am not entirely convinced that it's impossible to sort the records so they would print in the requested order using a layout with 3 columns. However, that would work only if you are printing the report (or showing it in Preview mode) and there are no partial weeks in the found set (with the variable method it is easy to add the missing dates when constructing the variable).
 

[flummi]

Thank you very much for taking the time to think into my problem. I am trying to learn from every suggestion. I will have to digest your solution but I will try it out.

To illustrate what I do, here are some screenshots.

Pic 1: the base table

Pic 2: the table I create by using the evaluate (let ....) method. This is the input for the report layout

Pic 3: the resulting report

[comment]

Looking at your screenshots, I notice that you do not have data for all dates in the reported range. Which means that my idea of producing the list of dates from the data table cannot work.

You could generate such list quite easily by looping - but have a look at an alternative method in the attached file. Just select the latest date for which you have data and all the dates will be calculated automatically. You just need to make sure you have the right amount of records in the found set to cover the range you want to report on.

But you still need to figure out how to bring in the corresponding data from the data table. Perhaps instead of the repeating field use 3 separate fields and 3 relationships?

 

 

dates.fmp12

[LaRetta]

Oh that is ONE SWEET repetition calculation, Michael!!  Such fun!   💥  😃  💥

[flummi]

3 hours ago, comment said:

You could generate such list quite easily by looping

That is what I am doing now. I loop through the (sorted) table and repeatedly collect the data for 21 fields in variables which I then write into a 'report' table which is the base table for the report layout.

3 hours ago, comment said:

Just select the latest date for which you have data and all the dates will be calculated automatically.

Smashing. You are a genius. 🙂 I am sure I find an opportunity to use this.

3 hours ago, comment said:

But you still need to figure out how to bring in the corresponding data from the data table.

That is the problem. The looping business seems so easy and straight forward that I think building relationships etc. would require a lot more thinking. Plus, for anyone looking into the code later the loop is much easier to follow than complicated formulae and relationships.

But from a challenge point of view this is fascinating of course. 🙂

Thank you once again for your time and thoughts. It is very much appreciated!

[comment]

41 minutes ago, flummi said:

I loop through the (sorted) table and repeatedly collect the data

I don't see how you can do that, when the data table does not have records for some dates - for example 28.03.2022 and 29.03.2022. What I meant by "generate such list by looping" is to loop over the calendar, not the data table.

 

50 minutes ago, flummi said:

I think building relationships etc. would require a lot more thinking.

Not really. It's a rather straightforward arrangement of each date in the report table being connected to the corresponding date in the data table. Alternatively, you could collect all the (existing) data in the data table into 3 lists (or even a single JSON variable) and lookup the corresponding value for each date from there. But I am not sure that's worth the effort.

 

4 hours ago, LaRetta said:

Such fun! 

Indeed! It's like building a clock where some wheels go forward and others backward!

 

[comment]

9 hours ago, comment said:

It's a rather straightforward arrangement of each date in the report table being connected to the corresponding date in the data table. 

Here is a demo of how this could work. I have deliberately left large gaps in the data table to show how each date in the report table picks the corresponding date's data from the data table. This is all done with no scripts, no variables and no loops (the only script serves to adjust the number of records shown in the report). You don't even have to sort the records. Just pick the last date and the number of blocks to show, and it all falls in place on its own.  Filemaker's real strength lies in declarative/functional programming.

 

Report3Columns.fmp12

Edited May 4, 2022 by comment

[flummi]

Hey, this is fantastic, ingenious or whatever adjective I can think of. I couldn't fully understand how this could possibly work until I saw it in action. 

This opens up a whole world of new ideas. I come from a conventional way of programming background and loops and that kind of stuff has always been the first choice. Now that I see what is possible with Filemaker I will never think of loops again until I don't see any other way. 

Thank you with all my heart for enlightening me. I really appreciate you thoughts and the effort you have put into creating the demo database.

You are a genius.

Hans