affect some information from one application to another one

Applications Container
#1

Hello,

I’ll try to explain my problem:
I have a container with 2 forms.
First one contains one record of a table.
Ex: TABLE CLIENT
Fields: NAME ADRESS DATE ITEM
The user can create a new record with a name, adress ans date.

Second form contains all records of the table ITEMS :
ITEM NAME INFORMATION
I add a field on the form: TAG which is a checkbox (YES/NO)

I want to add in the table CLIENT for each ITEM with TAG = YES:
NAME ADRESS DATE ITEM

I don’t know in with event select all check records. I try to create a button in first and second form, without success.
I give you a image of the container, because my English is so poor…

Best Regards,
Olivier

Capture.JPG

#2

[QUOTE=ols03;29060]
I give you a image of the container, because my English is so poor…
Olivier[/QUOTE]

Yes I guess so, as I didn’t understand what you want to do :slight_smile:

Ok whatever I understood, if you want to add some items to each client, then you don’t need a new widget or a container… a widget from another form is not good idea.

make your item field in table 1 as double select, and point the options to auto lookup in your table 2 for items, finally display to end user only form of table 1, they can select as many items they want and save the record… you can layout your double select field and play with it to appear under your other fields…

if that is what you want!

#3

Hello Mike,

I don’t understand too…

The problem is if a have on the form 10 select records, i don’t know how to retrie them in a select… The checked box field is not a fiels of the table ITEMS, because the checked state must be just for select some records.

Best regards,
Olivier

#4

Oliver, if the checkbox field is not in the same table then how you will save its data! Make a new field as items (checkbox or double-select) and make its lookup take the values from your items table and thats it! you will have the double-select field just like any other field, display normally and save the selection normally!

#5

Hello,

I can now use checkbox!
But i want save records in another table by sc_exec_sql.
I have an error on the line:
sc_exec_sql(“INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE,ID) values ([clientcde],0,[resacde],[dlivcde],[cartcde],[total_cart][$x],‘a’,[total_lot][$x],’’,[qtecde],)”);

I supposed the last field ID that i let with a comma is not correct. Simply because ID is auto-increment, i don’t know how to indicate it.

Best regards,
Olivier

#6

I had some error, but now it’s correct. I can insert on record when i put ID.
I just have to solve the autoincrement problem.
Olivier

#7

Just last try:
I trie to get the last ID on my table.
The request Under Firebird:
select gen_id(GEN_AFF_CDE_ID, 0) from rdb$database;

give me the last ID value of the autoincrement genrator.

But when i try to use it in scriptcase:
$var=“select gen_id(GEN_AFF_CDE_ID, 0) from rdb$database”;
sc_lookup(rs, $var);
$var = {rs[0][0]};
echo $var;

I have the errors:
Erreur
Undefined variable: database

Erreur
ibase_query(): Dynamic SQL Error SQL error code = -204 Table unknown RDB At line 1, column 39

Erreur
Undefined offset: 0

Colud you help me please?
Best regards,
Olivier

#8

for the last id you can use MAX(id)
if you want to insert into a field wich is autoincrement, use Null

#9

Hello MikeDE,
Null is not accepted:
sc_exec_sql(“INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE,ID) values (’[clientcde]’,0,’[resacde]’,’[dlivcde]’,’[cartcde]’,’$carts’,‘a’,’$lots’,’’,[qtecde],NULL)”);

Errors:
Erreur
ibase_query(): validation error for column ID, value “*** null ***”

Erreur en acc?dant ? la base de donn?es:
validation error for column ID, value “*** null ***”
INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE,ID) values (‘sdsd’,0,‘C’,‘2014-11-11’,‘BG38504C’,‘BF1152’,‘a’,‘45869250’,’’,1,NULL)

I try uppercase, lowercase,…

Best regards,
Olivier

#10

hmmm oliver, is it the same order as the database EXACTLY? I remember once by jsb he said should be in exact order. make sure your id the (null) location must be right.

see this post for more information about (i think) is the same
http://www.scriptcase.net/forum/showthread.php?6738-clone-or-copy-records-from-previous-day-to-today-a-hint-please

#11

Hello!
The fields are strictly in this order in database.
Best regards,
Olivier

#12

Hi

did you try to leave it blank?

there is a code in the ready-made codes of sc, says: copy records to another table… have a look at it please, i used that with selected fields and worked, kept id field (auto increment in the target table) undefined in the code (didn’t mention it) and it works fine… it adds the record regardless to id field.

Mike

#13

Olivier,

If the database definition of the column is indeed auto-increment, then you need not pass anything. Leave the field ID out of the list, and leave the NULL out of the list.

Dave

#14

Hello Mike,
The order of the values is strictly the same that the fields in database.
I try to do that:
sc_lookup(rs,“SELECT max (id) from aff_cde2”);
$max={rs}[0][0];
$max = $max+1;
sc_exec_sql(“INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE,ID) values (’[clientcde]’,0,’[resacde]’,’[dlivcde]’,’[cartcde]’,’$carts’,‘a’,’$lots’,’’,[qtecde],’$max’)”);
}

It seem OK, but very long in execution.
Do you have any idea about that?
I’ll try to improve that.

Best regards,
Olivier

#15

I change my code:
sc_lookup(rs,“SELECT max (id) from aff_cde2”);
$max={rs}[0][0];

for($x=0;$x<$tot;$x++){
$lots = [total_lot][$x];
$carts = [total_cart][$x];
$today = date('jyhis');
$max = $max+1;
sc_exec_sql("INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE,ID) values ('[clientcde]',0,'[resacde]','[dlivcde]','[cartcde]','$carts','a','$lots','',[qtecde],'$max')");

}

All is OK, that you Mike for help.
Olivier

#16

ols03,

Your code as written cannot be safely used in a multi-user environment.

It has a race condition since you are reading max and then incrementing it. A second user may read the same value, and both try to write max + 1. The first one succeeds, the second one gets an error since the unique field already exists.

This is the whole purpose for using auto-incement (which happens at the database server).

If using Auto-Increment, you pass NOTHING for the field during an insert, the database decides what is the next unique number to insert for the index.

Dave

#17

Hello Dave,
I’m not in multi-user for this application, but your right, i must change that.
I try to passing nothing, but SC reject all my INSERT INTO. And in the database the field in auto-increment (see image).
Do you see an error in the used syntax above?

Best regards,
Olivier

Capture.JPG

#18

Olivier,

Do you get an error if you do this:

sc_exec_sql(“INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,L OT,STOCK,QTE) values (’[clientcde]’,0,’[resacde]’,’[dlivcde]’,’[cartcde]’,’$carts’,‘a’,’$lots’,’’,[qtecde])”);

If this generates an SQL error then the ID field is probably not set as auto-increment in the database. The picture you are showing is NOT THE DATABASE, it is what scriptcase thinks the database settings are. You need to look at the database itself with a database manager tool ( I use phpMyAdmin for mysql) in order to know if the database has the field flagged as auto-increment.

Dave

#19

Hello Dave;
I have this error:
Erreur
ibase_query(): Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 73 OT

Erreur
Erreur en acc?dant ? la base de donn?es:
Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 73 OT
INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,L OT,STOCK,QTE) values (‘TEST’,0,‘R’,‘2014-11-14’,‘BS38266C’,’’,‘a’,’’,’’,1)

I use IBexpert for manage my database.
I can associate generator on a field. I put an image.
I try to create a new generator, i have the same problem finally.

Best regards,
Olivier

Capture.JPG

#20

I’m stupid, i have an error on “L OT” either “LOT”.
But i always have:
Erreur
ibase_query(): validation error for column ID, value “*** null ***”

Erreur
Erreur en acc?dant ? la base de donn?es:
validation error for column ID, value “*** null ***”
INSERT INTO aff_cde2 (CLIENT,NCDE,RESA,DLIV,CART,CART_SOURCE,AFFINAGE,LOT,STOCK,QTE) values (‘TEST’,0,‘R’,‘2014-11-25’,‘BS38504S’,’’,‘a’,’’,’’,1)
I try to modify generator, add a trigger, but nothings happen more.

Olivier