Database setup for repeating groups

[m6xmed5]

Hi,
I'm building an android app to update a stock keeping system so that it can be used on the shop floor.
The UI for web is built with fabrik and so is the database.
The app works like this;
Using 2 fabrik tables in the database 'parts' and 'jobrecords'
The user scans a QR containing a unique identifier from the parts table and the app returns some fields 'part name', 'description', 'quantity in stock'.
Then the app gives the user the option to add a part from the parts list to the job record.
The app needs a php file to run mysqli queries to manipulate or lookup data in the database.
When the user adds a part to a job the part quantity is subtracted from 'quantity in stock' on the parts list like this;

$sql = "UPDATE table_parts SET instock -'$qty' WHERE partno='$partno'";

That part is straight forward enough.

But I'm not sure how to add the part to the job record as parts used on job records are a repeating group.
I see in the database fabrik adds each item of a repeating group into a new table like

jobrecords_repeat_101
jobrecords_repeat_102
etc.

I don't know how to do that with a php mysql query, can anyone help me understand how fabrik is adding these repeats to the database or what query I would need to run to add these repeat rows to the database?

Marc

 

[cheesegrits]

Repeat groups are simple one-to-many table relationships. A parent table, and a child table that has an FK (foreign key) pointing to the PK (primary key) of the parent. In the tables we auto-create, the FK is called parent_id.

So you'd add a row to jobrecords_101_repeat (I'm pretty sure it'll be that, not jobrecords_repeat_101), with parent_id set to the rowid of the jobrecords row the part belongs to.

-- hugh

 

[m6xmed5]

Thanks Hugh,
That was my bad, I forgot that I had multiple repeating groups in that form. That's where I was getting confused. I had it in my mind for some strange reason that it was creating a new table for each repeating row I added.
Too many hours of coding that day!

Marc