Multi-key tables and looping data saves

[fester]

Hi,

I have nutted out most of the basics and its all really good and logical, and now I have come to the crux of the application I am building.

I have a Client(ClientID PK), Course(CourseID PK) and a Unit(UnitID PK, CourseID FK).
I have to get all these into a table:
- ID
- ClientID
- CourseID
- UnitID
- Other Info fields

The issue is that I need to multi-select Clients based on grouping, select a Course and multi-select the sub-Units, then save these all back to the table structure.
This will mean that for 3 clients, doing 2 courses, there are 6 table writes.

I understand that this is impossible with out-of-box functionality, and that I will have to do the writes manually, and that's fine, I just need some guidance on how to create a Browse Subform/Search Subform that I can multi-select (like the in-box Delete Column) and feed those selections out into PHP.

Any help would be appreciated.

[fester]

Apologies, made a duplicate post (missed the 'must be approved' notice).

Possibly easier information from that post re-added below:

So far, quite impressed with the product, and I have managed to build the basic data input/ouptut forms successfully.
Now for the tricky one.

I need to be able to create a subform that allows me to multi-select items from a Browse grid (similar to how the 'delete' visually works) and feed those values back to the parent form.
Is there an object I am missing or can I get some guidance?

[admin]

fester,

Are you saying

There are a number of Units in a Course.
and a Client has 1 Course?

And you want a way to add Units to Courses and Clients to Courses?

Steven

[fester]

Kind of.

At the end of the day I have a data structure (inherited from prev access db) that links a Client, Course and Unit together.
This is simple, but I need the UI to allow users to populate 1 Course, 5 units and 12 Clients and then save them all down to the table.
I know the save process will be manual PHP, but its the multi-select that's where I'm stuck.

[admin]

fester,

I would start with an Edit Form from which you select a course.

Then I would have 2 subforms

1 containing temp_client

1 called temp_unit

Code: Select all

CREATE TABLE temp_client (temp_client_id VARCHAR(15) NOT NULL, tc_course_id VARCHAR(15) NOT NULL, client_id VARCHAR(15) NOT NULL);
CREATE TABLE temp_unit (temp_unit_id VARCHAR(15) NOT NULL, tu_course_id VARCHAR(15) NOT NULL, unit_id VARCHAR(15) NOT NULL);

THEN on After Save of the Form properties

Code: Select all

$t = nuRunQuery("SELECT * FROM temp_unit, temp_client");
while($r=db_fetch_object($t)){

   $id = uniqid('1');
  $s = "INSERT INTO tblname (ID,ClientID,CourseID,UnitID) VALUES ('$id','$r->client_id','$r->tc_course_id','$r->unit_id')";
  nuRunQuery($s);

}

That should get you started.

Steven

[fester]

Perfect, that makes sense in a round-about kind of way

[admin]

.