Hi all.
I am having quite a big problem with relations in Servoy.
Here is my case.
I have got 2 tables (person, address) and one linking table (person_has_address).
I want to create an address record linked to a person. So what I do is… my method is in form based on the linking table (person_has_address) and I create the new record like this:
controller.newRecord(); //this creates the new record in the linking table. Given the right person record is selected because the linking table form is a related 'subform' of the person form
forms.address_popup_form.foundset.newRecord(); //this creates new address record
idaddress = forms.address_popup_form.idaddress; //and this links the linking record with the address record
So after that new address record and the linking record are created.
Now I want to use relation to access both sides (address and person). So I go like this forms.person_form.person_to_person_has_address.person_has_address_to_address
and I can see the newly created record… all fine.
BUT, when I try to access it the other way round: forms.address_popup_form.address_to_person_has_address.person_has_address_to_person
I’ll get NULL, because for forms.address_popup_form.address_to_person_has_address I am getting empty foundset
If I check forms.address_popup_form.foundset.getSelectedRecord() I can see the new created record which is correct.
Am I missing anything? The forms have all “Default” named foundset. All the relations are defined correctly.
Thanks
Did you save the newly created records ? My understanding is that Servoy initially fills (related) foundsets with SQL query on the database. If not saved the query for the back relation will be empty and the Servoy caching mechanism is not that smart to find any unsaved records in cache that fits the bill.
Try saving/committing the data back to the database (Database Manager > SaveData()) this has worked for me. You can additionally filter the save to one record which saves overhead.
From the perspective of the “person” form which has the “person_has_address” subform on it
person_to_person_has_address.newRecord() //Creates the link record with the proper foreign key columns already filled, AND Servoy knows it's a related record.
person_to_person_has_address.person_has_address_to_address.newRecord() //Again, fills in necessary FK values and Servoy is again immediately aware it's related.
From the perspective of the “person” form which has the “person_has_address” subform on it
person_to_person_has_address.newRecord() //Creates the link record with the proper foreign key columns already filled, AND Servoy knows it's a related record.
person_to_person_has_address.person_has_address_to_address.newRecord() //Again, fills in necessary FK values and Servoy is again immediately aware it’s related.
Thanks jgarfield.
However this won’t work.
calling to person_to_person_has_address.person_has_address_to_address.newRecord() throws TypeError: Cannot call method “newRecord” of null because the relation person_has_address_to_address is null at this point.
Thank you all guys.
Saving the records to the database would work.
However I would rather not use this approach… because I want to be able to rollback the records. In case user presses cancel.
I don’t know why servoy doesn’t see the related record when accessing it from the child record. All the linking ids are there and correct…
thonda:
Thanks jgarfield.
However this won’t work.
calling to person_to_person_has_address.person_has_address_to_address.newRecord() throws TypeError: Cannot call method “newRecord” of null because the relation person_has_address_to_address is null at this point.
that doesn’t seem like it should be the case, since prior to running person_to_person_has_address.person_has_address_to_address.newRecord() you are creating a new record in the person_has_address table via person_to_person_has_address.newRecord(), and should therefore be able to
If it outputs 1, which it should, then you should be able to then use person_to_person_has_address.person_has_address_to_address.newRecord(), since everything should be in place at that point…unless I’m forgetting something?
this is because person_to_person_has_address is pk → fk relation and the pk of the source is copied to the fk of the target
But this one:
person_has_address_to_address.newRecord()
is a fk to pk relation, so where do you new record on? you dont want to create a new address record anyway i guess (you want to hook an existing one)
a pk → fk relation the foundset is always there with a size of 0 if there are no records because we can do “select * from orderitems where orderid = X” (where X is the value of the pk orderid from orders table compared to the fk in the orderitems table)
a fk → pk relation if the fk is not filled in the foundset is null because we cant do “select * from orders where orderid = X” (because what is then X ? now the source is the orderitems table and there the FK is the orderid but that is null, so we cant fill in anything for that query)