Calculate field value list

[Gabe]

Hi

I am still pretty shaky in the area of calculations and the limits of what they can do. I'm wondering how to go about calculating a list of related values from another table field. I'm not sure if there is a way to loop through the values, or if I'll have to create a recursive function somehow...

Seems like if there is a way to write a loop, I could just run a loop with lookup(), but I dont think there is a way to loop in a calculation field.

Any suggestion? I really don't quite know where to start with it.

Also, I know somebody will probably suggest that I just create a value list on the related field, but my problem is that I need to be able to generate lists from different tables, not just one. So, I am pretty sure I have to calculate it, because the source field is variable.

[Gabe]

Hi

I am still pretty shaky in the area of calculations and the limits of what they can do. I'm wondering how to go about calculating a list of related values from another table field. I'm not sure if there is a way to loop through the values, or if I'll have to create a recursive function somehow...

Seems like if there is a way to write a loop, I could just run a loop with lookup(), but I dont think there is a way to loop in a calculation field.

Any suggestion? I really don't quite know where to start with it.

Also, I know somebody will probably suggest that I just create a value list on the related field, but my problem is that I need to be able to generate lists from different tables, not just one. So, I am pretty sure I have to calculate it, because the source field is variable.

[Gabe]

Hi

I am still pretty shaky in the area of calculations and the limits of what they can do. I'm wondering how to go about calculating a list of related values from another table field. I'm not sure if there is a way to loop through the values, or if I'll have to create a recursive function somehow...

Seems like if there is a way to write a loop, I could just run a loop with lookup(), but I dont think there is a way to loop in a calculation field.

Any suggestion? I really don't quite know where to start with it.

Also, I know somebody will probably suggest that I just create a value list on the related field, but my problem is that I need to be able to generate lists from different tables, not just one. So, I am pretty sure I have to calculate it, because the source field is variable.

[electrobase]

My question is sufficiently similar it seemed appropriate to post it here - if not I apologize.

I want to enter a start date and a number of days and generate a return-delimited list I can operate on, thusly:

Date: January 1, 2005 ; Days: 5 =

1/1/05

1/2/05

1/3/05

1/4/05

1/5/05

this seems like it ought to be trivial AND common, and yet I have searched all over without success.

any help would be MUCH appreciated. (we have Developer, so could create custom function if necessary)

[electrobase]

My question is sufficiently similar it seemed appropriate to post it here - if not I apologize.

I want to enter a start date and a number of days and generate a return-delimited list I can operate on, thusly:

Date: January 1, 2005 ; Days: 5 =

1/1/05

1/2/05

1/3/05

1/4/05

1/5/05

this seems like it ought to be trivial AND common, and yet I have searched all over without success.

any help would be MUCH appreciated. (we have Developer, so could create custom function if necessary)

[electrobase]

My question is sufficiently similar it seemed appropriate to post it here - if not I apologize.

I want to enter a start date and a number of days and generate a return-delimited list I can operate on, thusly:

Date: January 1, 2005 ; Days: 5 =

1/1/05

1/2/05

1/3/05

1/4/05

1/5/05

this seems like it ought to be trivial AND common, and yet I have searched all over without success.

any help would be MUCH appreciated. (we have Developer, so could create custom function if necessary)

[comment]

If the number of days is not a constant (or cannot be limited to a predefined maximum), you need a custom function. See

here .

[comment]

If the number of days is not a constant (or cannot be limited to a predefined maximum), you need a custom function. See

here .

[comment]

If the number of days is not a constant (or cannot be limited to a predefined maximum), you need a custom function. See

here .

[comment]

A calculation, recursive or not, cannot loop through related records, simply because there is no way to reference "related record #2". A reference to a related record in a calculation is always a reference to the FIRST related record, or, in an aggregate function, to ALL related records.

I believe your best choice is to create a value list based on the related field. If the related field is a variable, create a value list for each, then use a calculation to select the correct one.

The other option is a script, looping through the the records in the related table, comparing the current value to a temp global. If the value <> temp global, add the new value to the result, and update the temp global.

[comment]

A calculation, recursive or not, cannot loop through related records, simply because there is no way to reference "related record #2". A reference to a related record in a calculation is always a reference to the FIRST related record, or, in an aggregate function, to ALL related records.

I believe your best choice is to create a value list based on the related field. If the related field is a variable, create a value list for each, then use a calculation to select the correct one.

The other option is a script, looping through the the records in the related table, comparing the current value to a temp global. If the value <> temp global, add the new value to the result, and update the temp global.

[comment]

A calculation, recursive or not, cannot loop through related records, simply because there is no way to reference "related record #2". A reference to a related record in a calculation is always a reference to the FIRST related record, or, in an aggregate function, to ALL related records.

I believe your best choice is to create a value list based on the related field. If the related field is a variable, create a value list for each, then use a calculation to select the correct one.

The other option is a script, looping through the the records in the related table, comparing the current value to a temp global. If the value <> temp global, add the new value to the result, and update the temp global.

[electrobase]

comment, i am forever in your debt

thank you!

[electrobase]

comment, i am forever in your debt

thank you!

[electrobase]

comment, i am forever in your debt

thank you!

[Gabe]

Good idea, about the value lists. Question, though.. how do I use a calculation to select the proper value list? That would solve my problem.

[Gabe]

Good idea, about the value lists. Question, though.. how do I use a calculation to select the proper value list? That would solve my problem.

[Gabe]

Good idea, about the value lists. Question, though.. how do I use a calculation to select the proper value list? That would solve my problem.

[comment]

Example:

Let ( [

list1 = ValueListItems ( Get(FileName) ; "yourFirstListName" ) ;

list2 = ValueListItems ( Get(FileName) ; "yourSecondListName" ) ;

list3 = ValueListItems ( "ExternalFileName" ; "ExternalListName" ) ;

theList =

Case (

selectorField = "List One" ; list1 ;

selectorField = "List Two" ; list2 ;

selectorField = "List Three" ; list3 ;

"no list has been specified"

)

] ;

theList

)

[comment]

Example:

Let ( [

list1 = ValueListItems ( Get(FileName) ; "yourFirstListName" ) ;

list2 = ValueListItems ( Get(FileName) ; "yourSecondListName" ) ;

list3 = ValueListItems ( "ExternalFileName" ; "ExternalListName" ) ;

theList =

Case (

selectorField = "List One" ; list1 ;

selectorField = "List Two" ; list2 ;

selectorField = "List Three" ; list3 ;

"no list has been specified"

)

] ;

theList

)

[comment]

Example:

Let ( [

list1 = ValueListItems ( Get(FileName) ; "yourFirstListName" ) ;

list2 = ValueListItems ( Get(FileName) ; "yourSecondListName" ) ;

list3 = ValueListItems ( "ExternalFileName" ; "ExternalListName" ) ;

theList =

Case (

selectorField = "List One" ; list1 ;

selectorField = "List Two" ; list2 ;

selectorField = "List Three" ; list3 ;

"no list has been specified"

)

] ;

theList

)

[BobWeaver]

Actually, a custom function is not required to create the date list. I posted an FM5/6 example some time back. So, if you don't have access to Developer, or are not using FM7, you can try this one:

http://www.fmforums.com/threads/showflat.php?Number=73337

[BobWeaver]

Actually, a custom function is not required to create the date list. I posted an FM5/6 example some time back. So, if you don't have access to Developer, or are not using FM7, you can try this one:

http://www.fmforums.com/threads/showflat.php?Number=73337

[BobWeaver]

Actually, a custom function is not required to create the date list. I posted an FM5/6 example some time back. So, if you don't have access to Developer, or are not using FM7, you can try this one:

http://www.fmforums.com/threads/showflat.php?Number=73337

[comment]

I said "cannot be limited to a predefined maximum".

Otherwise, you can use a repeating field, or LeftValues ( compute a range of 2000 ; n), or any such.

Of course, I have since learned that a custom function has its own maximum of 10,000 iterations, so perhaps the difference is merely quantitative. And with up to 32,767 repetitions, repeating field looks like the winner on this one.

[comment]

I said "cannot be limited to a predefined maximum".

Otherwise, you can use a repeating field, or LeftValues ( compute a range of 2000 ; n), or any such.

Of course, I have since learned that a custom function has its own maximum of 10,000 iterations, so perhaps the difference is merely quantitative. And with up to 32,767 repetitions, repeating field looks like the winner on this one.

[comment]

I said "cannot be limited to a predefined maximum".

Otherwise, you can use a repeating field, or LeftValues ( compute a range of 2000 ; n), or any such.

Of course, I have since learned that a custom function has its own maximum of 10,000 iterations, so perhaps the difference is merely quantitative. And with up to 32,767 repetitions, repeating field looks like the winner on this one.

[BobWeaver]

Yep, lots of ways to skin a cat.

Oops, sorry Fluffy.

A lot of my workarounds were rendered obsolete with the Get(CalculationRepetitionNumber) function in FM7. I haven't gotten around to using it for anything yet, so I still tend to think in terms of FM6 limitations.

[BobWeaver]

Yep, lots of ways to skin a cat.

Oops, sorry Fluffy.

A lot of my workarounds were rendered obsolete with the Get(CalculationRepetitionNumber) function in FM7. I haven't gotten around to using it for anything yet, so I still tend to think in terms of FM6 limitations.

[BobWeaver]

Yep, lots of ways to skin a cat.

Oops, sorry Fluffy.

A lot of my workarounds were rendered obsolete with the Get(CalculationRepetitionNumber) function in FM7. I haven't gotten around to using it for anything yet, so I still tend to think in terms of FM6 limitations.

[Gabe]

Ok, comment. That little snippet worked great for fetching the list I want.

Now, I have a new problem related to that. I want to be able to show that list in a pop-up list in a field on my layout. I tried creating a fourth value list that will show the values of my field with the calculation in it. Unfortunately value lists can only be created from indexed fields, and you cannot index a calculation field that references other fields.

It's a connundrum. Is there another way to show the values in a popup besides what I was trying to do?

I'm thinking I might not be able to solve this problem at all.

[Gabe]

Ok, comment. That little snippet worked great for fetching the list I want.

Now, I have a new problem related to that. I want to be able to show that list in a pop-up list in a field on my layout. I tried creating a fourth value list that will show the values of my field with the calculation in it. Unfortunately value lists can only be created from indexed fields, and you cannot index a calculation field that references other fields.

It's a connundrum. Is there another way to show the values in a popup besides what I was trying to do?

I'm thinking I might not be able to solve this problem at all.

[Gabe]

Ok, comment. That little snippet worked great for fetching the list I want.

Now, I have a new problem related to that. I want to be able to show that list in a pop-up list in a field on my layout. I tried creating a fourth value list that will show the values of my field with the calculation in it. Unfortunately value lists can only be created from indexed fields, and you cannot index a calculation field that references other fields.

It's a connundrum. Is there another way to show the values in a popup besides what I was trying to do?

I'm thinking I might not be able to solve this problem at all.

[comment]

Perhaps you could change the calculated value list into a text field with auto-enter calculation (replace).

This might work for you or not, depending on what you're trying to achieve. After all, you are trying to break the rules here. There is a reason why FMP won't let you create the fourth VL. You might be able to fool it, but it also might backfire.

[comment]

Perhaps you could change the calculated value list into a text field with auto-enter calculation (replace).

This might work for you or not, depending on what you're trying to achieve. After all, you are trying to break the rules here. There is a reason why FMP won't let you create the fourth VL. You might be able to fool it, but it also might backfire.