Howto: Dynamic Value list with numbers?

[philou54]

Hi everyone,

I'm kind of stuck here.

I'm trying to create a dynamic value list with an autoincrement number field.

E.g:

Field number: 20

Value list: 1,2 ..... to 20

I can't manage to do that with a calculation field because I can't use a "loop" fonction because this is not a script.

Is it possible to do that?

Thanks a lot!

[Fitch]

What do you mean, "Field number: 20"? Twenty fields on your layout? Twenty records in your found set?

[philou54]

I mean, I set the number "20", and then it makes a calculation and display in a value list the numbers 1 to 20.

Then if I change the number field to "13", it does the calculation from 1 to 13, and display it in a value list.

I hope you understand

[Raybaudi]

Hi

try this

DynamicList.zip

[comment]

Since you have Advanced, you could do this using a custom function. But it is simpler to use a repeating calculation field =

Let (

i = Get ( CalculationRepetitionNumber )

;

Case ( i ≤ Extend ( yourNumberField ) ; i )

)

You will need a self-join relationship based on SerialID (or another unique field) to use this as a value list.

[philou54]

Thanks raybaudi!

it works and it's exactly what I needed.

[Raybaudi]

Remember to follow comment's suggestion about the self-join relationship !

So this file is better...

DynamicList.zip

[philou54]

I have an other problem.

I need to create a new record with this calculation "1" for the first, "2" for the second record etc...

When this number is reached, I need it to start over from 1.

For example I put "4" in field "number"

Records: 1->1, 2->2, 3->3, 4->4, 5->1, 6->2... etc

Do you know how to do that? Reset the "extend" fonction maybe?

[Raybaudi]

...but this is a totally new question ?

or the field "number" is the same of the previous question ?

[philou54]

the field number is the same! ;-)

[comment]

That is going to be difficult, because the number "4" needs to in a global field - and a calculation referencing a global field cannot be used to build a value list.

You could create a separate table as a bank of values, and define a ≥ relationship to it. Then use the related values for the value list.

[Raybaudi]

If the field "number" contains always the same value for all records of your DB, you can add a calculated field ( say "custom" ) with this calc:

Case(

Mod ( id ; number ) ; Mod ( id ; number ) ;

number

)

[philou54]

what is the "id"?

[philou54]

Sorry I understood thanks

[Raybaudi]

Id is a field that auto-enter the serial number on creation of the record, like the one of my second attachment

[philou54]

I tried with the "Mod" fonction but I can't see the aim of the use of this fonction

[Raybaudi]

try this

DynamicList.zip