Creating related records with db managed PK

Classic Servoy
1

I just ran into a very big issue that took me hours to troubleshoot.

After developing for many hours, I had forgotten a change I had made that caused a big problem.

When a tab panel is showing a related form and the fields on that form come from a third table (e.g. Your current form is a list view for T1, tab panel shows list view from T2 and fields shown on form tied to T2 are from T3) you can’t rely on the allow creation of a relationship and simply set a field value and have a record created.

I have been educated in that I need to explicitly create the related record. This was fine and my code was working in the following the form.

//Explicitly create the related record in the subs database
mag_group_membership_to_mag_subs.newRecord();
mag_group_membership_to_mag_subs.subs_gid = pn_gid;

This works fine when the PK for the table on the left side is Servoy managed (servoy seq as opposed to db identity) and is in Sync.

However, this creates an error when it is set to db identity, which is required for web databases that also have php code running them. You receive a “Cannot convert null to an object.” error.

After thinking things through I thought that Servoy needed to retrieve the PK from the DB so I tried adding a controller.saveData().

controller.saveData()

application.output(controller.copyRecord());

//Explicitly create the related record in the subs database
mag_group_membership_to_mag_subs.newRecord();
mag_group_membership_to_mag_subs.subs_gid = pn_gid;

Using the application.output(controller.copyRecord()); shows the results that Servoy does see the PK. However, now you sometimes get an error of undefined value or the method will complete but the explicitly created record does not exist and was never created.

I also noticed that if you are allowing Servoy to manage the pk, then using the controller.saveData() prior to creating the related records will break the whole operation. Is this because Servoy committs and looses the PK it would have used to create the related record?

Is this a bug? Shouldn’t Servoy be able to create related records with db managed PK’s? I’m assuming the correct action, prior to creating a related record with a db managed key, is to save the data.

This is a pretty critical issue for systems where there is both a web front end and Servoy is also being used for admin purposes. Anyone hit this issue?

2

first.

it shouldn’t be nessesary for you to set the PK of the parent record as a FK in the child record. If you do new record through a relation this will be set for you..

But you are right about the db managed sequence.

this is possible with servoy managed:

controller.newRecord();
naam = ‘hallo’;
parent_to_child.newRecord();
parent_to_child.naam = ‘hallochild’
controller.saveData()

but with db managed you have to do one save data extra:

controller.newRecord();
naam = ‘hallo’;
controller.saveData();
parent_to_child.newRecord();
parent_to_child.naam = ‘hallochild’
controller.saveData()

but we will try to fix this. So that in javascript there is no difference between using db or servoy sequences.

do you have an example that those scripts (or examples simular) above aren’t working??

3

jcompagner:
do you have an example that those scripts (or examples simular) above aren’t working??

This is the method attached to the button named Join. The button is on the form shown in the Tab Panel. The goal is to add the user to a certain group and then create a related record in another table that contains the signup date and renewal date.

//Trap to see that the popup has been selected
if(globals.joinGroup == ""){
plugins.dialogs.showDialog( 'No Group Selected',  'You must select a group first',  'Ok');
return;
}

//Get names of the current subscriptions
var theNames = ''
var theMax = forms.subsMemberships.controller.getMaxRecordIndex();
var selectedGroup = globals.joinGroup;

for ( var i = 1 ; i < theMax + 1 ; i++ )
{
forms.subsMemberships.controller.recordIndex = i;
theNames = theNames + '\n' + forms.subsMemberships.mag_group_membership_to_mag_groups.pn_name;
	if (forms.subsMemberships.mag_group_membership_to_mag_groups.pn_name == selectedGroup)
	{
	forms.subsMemberships.msg_existing();
	return;
	}
}

forms.subsMemberships.add_group(); //Calls another method

Once it is determined that the group is not already assigned to the user then the .add_group() method is called.

controller.newRecord() // Create membership record
if (globals.joinGroup == 'Subscribers')
{
	pn_gid = 3;
}
if (globals.joinGroup == 'Comp')
{
	pn_gid = 8;
}
controller.saveData() //Save data to get db managed PK

//application.output(controller.copyRecord());

//Explicitly create the related record in the subs database
mag_group_membership_to_mag_subs.newRecord();
mag_group_membership_to_mag_subs.subs_gid = pn_gid;

var today = new Date();
var renew = new Date();

renew.setMonth(today.getMonth() + 3);

today.setHours(00);
today.setMinutes(00);
today.setSeconds(00);

renew.setHours(00);
renew.setMinutes(00);
renew.setSeconds(00);

mag_group_membership_to_mag_subs.subs_signdate = today;
mag_group_membership_to_mag_subs.subs_renewdate = renew;

controller.saveData()

Everything under the comment line //Explicitly create the related record in the subs database does not work properly.

4

i really need a working example.

but the relation:

mag_group_membership_to_mag_subs

is specfied as:

pn_gid = subs_gid

yes?

5

save data is now not needed anymore for parent to relation.newRecord()

The only thing that is still different if you use db-managed keys is that you can’t do something with the PK of the just created (in servoy not yet in db) record.. Because that one isn’t there.

but this is now working fine in 2.0RC9

controller.newRecord()
name=‘parent’
test_to_test2.newRecord()
test_to_test2.name=‘child1’
test_to_test3.newRecord()
test_to_test3.name='child2;
controller.saveData();

6

ive been trying to work out from this post what i need to do be able to create a new record through a tab panel relationship with a DB assigned PK..

from what ive read here it seems possible for the related data to automatically populated in the new record created through tab relationship.

some of the coding in this example looks a little complex (to me)… do we need to code this ourselves because we are using a DB PK?

or is it always the case you need to do it manually…

when i create a new tab panel i often try selecting the “Create related records” tick box. But when i ever i try creating a new record in the tabpanel in view i only succeed in creating a new record in the table the the form is associated it with.

weve realised now that are solutions are going to be very limited without being able to create new records through tabl panels.

be very helpful for any experience on this one. many thanks
eugene

7

when i create a new tab panel i often try selecting the “Create related records” tick box. But when i ever i try creating a new record in the tabpanel in view i only succeed in creating a new record in the table the the form is associated it with.

Can you explain a little more clearly what you are doing and what you are trying to achieve. On form A (table A) which is related to Form B (Table B), you run a method that creates a new record through the relationship in table B no? Does this not work?

8

On form A (table A) which is related to Form B (Table B), you run a method that creates a new record through the relationship in table B no?

thanks replying

yes that is what im trying to achieve… BUT ive not been running a method to try and create a new record. Ive been selecting “New Record” from the “Select” drop down in the client. Is this not sufficient?

i do not have a file maker background, i get the feeling i would have known more about how to do this if i was a filemaker / exfilemaker user as many servoy developers seem to be. much of the documentation it seems to assume some filemaker knowledge or background. hopefully we can still get to grips with things anyway.

Do I need to create the method to do create the related record from scratch.
Does the fact we are using DB assigned primary keys on all our tables make any difference? I noticed something in this post about servoy PK and DB PK

many thanks

9

In order to create a record in your table B which is being shown through a relation in a tab panel on form A, you need to create a method that does this. Whilst in the method editor you need to click on the relations, choose the relation you want a record on (table A to table B) and then double click newrecord. Save the method and then run it from Form A. Simple as that. If you use the menu option to create a new record, it will create a new record on the form/table you are currently on.

HTH

10

wow that’s easy!!

the only trouble is it does not seem to save the record

the new record seems fine while looking at it in servoy…
even populates the related columns which is brilliant but then when i check the database or click in and out of Designer in Developer, the new record is no longer there.

method looks like this:
//create a new record in foundset
brand_to_documents.newRecord(true);

does our DB assigned PKey make any difference or is it something else stopping the record creating properly? I do not get any type of error message

many thanks

11

that must work fine
what happens if you create 2 new record after each other.
Are you really sure that you don’t get new records in the database?
What happens if you also show the pk field (the db ident column) on the form? If you do newRecord then it should be empty and if you click then somewhere on the form the records get’s saved and then the id should be pushed from the db to the form.

12

thanks Edward, Johan… working now… :D

the reason for it not saving seems to have been the PK was not set to DB assigned, it was still showing as servoy assigned..
put that right and everything is ok

a whole new world has opened up us in finding out how to do this
many thanks for taking the time to answer..
:D

13

We now have the following set up
Form A (table A - members) which is related to Form B (Table B)
Form B (table B - event) which is related to Form C (Table C)
Form C (table C - event schedule) which is related to Form D (Table D)
Form D (table D - event attendance)

Form A has a Tab Panel containing Form B
Form B has a Tab Panel containing Form C
Form C has a Tab Panel containing Form D

There is a button in form C calling the NewRecord method using the relationship between Table C and D

When viewing Table A and looking through all 3 tab panels and running the NewRecord method on Table C the new record creates in Table D but it does not populate the memberid column as i hoped it might which exists in both Table A and Table D.

sorry this description is a bit long winded.. do you happen to know of a way to pass the memberid column in Table A into the new record being created through tab panels into Table D
many thanks

14

If you do a new on a relation between C and D then the relation keys of C are filled in in the record of D.
But what does A have to do with that action? You don’t do nothing with A.

Does D also have some thing that A has? Then that is a relation so you have to create a new record through the relation A->D (based on memberid)

But then C->D is not filled in ofcourse because A->D doesn’t know about C->D. If you want to set another column. Then you have to do it by hand.

15

From what I think you’re trying to do, ie create an event and tie it to a member and then add an attendee, you need to run the method from the mother record, ie Table A, so that the memberid is carried through the relation to Table D…