Control form to insert sequence numbers in multiple records

Applications Control
#1

Hi there,

is their a way to use a control form to insert a start number (e.g. 2001) and then when i push ok. this number increments by +1 for each record in a table that meets a criteria (like: Where ev_id = [ev_id])

any suggestions?

#2

i have this code and played with it. but i can’t get it right

//begin met nr ingegeven

$start = {START_NUMMER};

/**

  • Update a record on another table
    */

// SQL statement parameters
$update_table = ‘ILP_rooster_TEMP’; // Table name
$update_where = “ev_id = ‘82’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“wk_max_d = ‘$start, $start=$start++’”,

);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

#3

I think you must set your counter before your update record:

$next_sq = $start;
$start = $start++
// Update record
…
…
$update_fields = array( // Field list, add as many as needed
“wk_max_d = $next_sq” );

#4

well i tried that but he fires it only once for the sequence if i put this in onload

//begin met nr ingegeven

$start = {START_PERIODE};
$next_sq = $start;
$start = $start++;
// Update record

/**

  • Update a record on another table
    */

// SQL statement parameters
$update_table = ‘ILP_rooster_TEMP’; // Table name
$update_where = “ev_id = ‘82’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“wk_max_d = ‘$next_sq’”,

);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

result all wk_max_d get a 1 if {START_PERIODE} = 1

their has to be a do while or something

#5

You Must set a New Field for your sequence: {new_sequence}
in OnLoad
if ( {new_sequence} > 0) {
{new_sequence} = {new_sequence} +1;
}else {
{new_sequence} = {START_PERIODE};
}
…
$update_fields = array( // Field list, add as many as needed
“wk_max_d = {new_sequence}"

#6

sorry i ment onvalidate. but this kind of code only works once when you use the control form

when you use a form, (OnLoad) this will work. but when i use a control form it fires only once

static $a = 2001;
echo $a;
{wk_max_d} = $a;
$a++;

#7

I think that Code can works fine in Onvalidate for your Control App.

#8

But how does script case know what table tot update and which records? Because een i use update all the fields have the same value

Much appricated btw

#9

You must have a table for consecutive numbers, each time a number is used, the consecutive table must be updated to the next one.

#10

@rotrax you should either

  • create a php loop where you loop through all records returned by a SQL select with the WHERE cause you need to select only the records to modifiy and on each loop iteration:
    – store and increment the counter: $wk_max_d_value = $start++;
    – get the record primary key value: $key_val = … (the syntax to get the field value depends on which SC macro you use)
    – execute a SQL update for the single record: UPDATE … SET wk_max_d = $wk_max_d_value WHERE primary_key_field_name = $key_val;

  • find out if your DB engine has some way to set and increment counters in the SQL statements (no idea if this is possible with any DB engine, unless you use stored procedures or something similar)