Problem saving foundset in many-to-many table

Classic Servoy
1

I have 3 tables: TableA, TableB and TableC. I have done a lookup on TableA and have the foundset. I want to now create a new record in TableB and use TableC as a many-to-many relation table relating the new record in TableB with the foundset from TableA. Still with me?

I believe my problem is due to do which table the foundset is from and where the script is. Here I what I have done so far. This script is located under TableC:

for (var i = 1 ; i <= forms.TableA.foundset.getSize(); i++)
{
    controller.newRecord();

    id_1 = forms.TableB.TableB_id;
    var record = forms.TableA.foundset.getRecord(i);
    id_2= record.TableA_id;
	
    controller.saveData();
}

When I run this I get this error:

java.sql.SQLException: General error, message from server: “Column ‘id_2’ cannot be null” General error, message from server: “Column ‘id_2’ cannot be null”

Any ideas of what I am doing wrong?
Thanks,
Chad[/b]

2

i find this a very strange script

first you are going through all the records of A

but the newRecord you do in C (you are saying that that script ins under C)

And after that you are suddenly getting an id from B? What is in B?

if you want this n-m relation to work you have to do 2 newRecords
one on B and on in C if i am not mistaken.

what kind of sequences do you use? If you use DB sequences then you should do a saveData after each newRecord because if you don’t then the id isn’t there yet

for (var i = 1 ; i <= forms.TableA.foundset.getSize(); i++)
{
// make new record on B
forms.tableB.controller.newRecord();
// make new record on C
controller.newRecord();

// saveData if db indentites/sequences!!
saveData();

id_1 = forms.TableB.TableB_id;
var record = forms.TableA.foundset.getRecord(i);
id_2= record.TableA_id;

controller.saveData();
}

3

I think that you are almost there, Johan.

However, I think that Chad wants to actually create a single new record in table B and link that single record in table B with the foundset of table A via join records in table C.

Thus if the foundset of table A = 3, then there would be 1 record added to table B and 3 records added in table C

So if we change your code to :

// make new record on B 
forms.tableB.controller.newRecord(); 

controller.saveData();
 
// pick up the id from table B
var recID = forms.TableB.TableB_id; 

for (var i = 1 ; i <= forms.TableA.foundset.getSize(); i++) 
{ 

// make new record on C 
controller.newRecord(); 

// saveData if db indentites/sequences!! 
saveData(); 

id_1 = recID; 
var record = forms.TableA.foundset.getRecord(i); 
id_2= record.TableA_id; 

controller.saveData(); 
}

This may give Chad want he wants

Harry

4

Thanks for the suggestions but after looking a bit deeper I think the problem lies elsewhere.

TableC, the relation table is a table of two columns. Both are indexes and have the “not null” property. So it seems that when I say controller.newRecord() it barfs because it is trying create a new record with null values.

Am I correct? Does controller.newRecord() create a new record with null values? If so how do I create a new record in my TableC?

Thanks,
Chad

5

then do this:

makeA
// save
makeB
// save
makeC
setA
setB
//save

we don’t save when you do newRecord.

6

then do this:

make A
// save
make B
// save
make C
setA
setB
//save

we don’t save when you do newRecord.