Method to create a N-M table record & a parent at once!

Classic Servoy
1

I need a sample of a method that allows you create a N-M table record and one parent record at once. I need to create a record in the many-to-many’s child table. There is an already available parent, but first I need to create the other parent record. In other words, I need to create the record that holds one of the foreign key and place that foreign key in the child table along with the other parent FK, but as if all of that were happening at the same time.

The challenge is there (in part) because the FK cannot be NULL (they are the child’s composite PK).

- There are three tables: Table-A, Table-B and Table-AB (the one in the middle, the N-M Table).
- A user in working on an UI form which based table is Table-A. (Based Form)
- He needs to add a record in Table-AB. (Tab-panel in Based Form/Table-A)
- The challenge is that he needs to create the related record in Table-B while creating the record in Table-AB (while creating the record in the Tab-panel form).
- I need to run a method that creates first the record in Table-B, and then uses that record’s primary key to insert the record in Table-AB alongside the primary key of the record in Table-A (which is the one in the Based Form).

I also need to figure out a way to delete the many-to-many record (record in Table-AB) and the record in Table-B.

I always avoid this kind of complexity, but this solution really requires this. Have anybody written this method before? Can you share an example?

Thanks in advance!

Carlos

2

Carlos,

Something like this?

	var a = foundsetA.getRecord(foundsetA.newRecord())
	var b = foundsetB.getRecord(foundsetB.newRecord())

	var fs = databaseManager.getFoundSet(datasource_ab)
	var ab = fs.getRecord(fs.newRecord())
	ab.a_id = a.a_id
	ab.b_id = b.b_id

	var fs = databaseManager.getFoundSet(datasource_ab)
	if (fs.find())
	{
		a_id = a.a_id
		b_id = b.b_id
		if (fs.search() > 0)
		{
			fs.deleteAllRecords()
		}
	}

Rob

3

Excellent. Thank you very much.

For use of the forum. See the attached example:
[attachment=0]nmrelation.servoy[/attachment]
Database Server: nmrelation

nmrelation.servoy (5.75 KB)