Request for help with application design

[thirdcircle]

I am beginning a new and critical phase in the development of my Caspio application. I have a lot of time and money invested in this project so, before I proceed, I’d like your advice and input on possible strategies and solutions.

Below, I first describe the existing elements of my tables and DataPages. After that is an explanation of my objective. Specifically, I am requesting strategies and techniques for accomplishing the OBJECTIVE using built-in Caspio tools and techniques but I am also open to incorporating Javascript, if necessary.

Thanks in advance for your help!

CASPIO ELEMENTS____________________________________

Table_1: Defined fields include:

"Profile_ID" (Autonumber)

"Field_2" (Text 255)

"Field_3" (Text 255)

"Field_4" (Text 255)

Table_2: Defined field is

"ID" (Number)

Table_3: Defined field is

"ID" (Number)

Table_4: Defined field is

"ID" (Number)

Lookup_Table_1: Defined field is:

Choice (Text 255)

This lookup table has been manually populated with 3 records:

Record 1: Choice = “Aâ€

Record 2: Choice = “Bâ€

Record 3: Choice = “Câ€

DataPage_1: Single Record Update form. Displays the following field elements from Table_1:

Field_2: form element = Dropdown, Required

Field_3: form element = Dropdown, Required

Field_4 form element = Dropdown, Required

Field_2, Field_3, and Field_4 all have their Source: (in Field Options) set to the same lookup table = Lookup_Table_1.

Thus, in DataPage_1, the user will:

> from a dropdown, select “A†or “B†or “C†for Field_2

> from a dropdown, select “A†or “B†or “C†for Field_3

> from a dropdown, select “A†or “B†or “C†for Field_4

OBJECTIVE______________________________________

Upon or after submission of DataPage_1, the application will process the selections made in Field_2, Field_3, and Field_4 and write a new record to either Table_2, Table_3, or Table_4 according to the following logic.

The logic below applies equally to the processing of Field_2, Field_3, and Field_4:

IF “A†was selected from the the dropdown box, a new record will be written to Table_2. For this new record:

The value of the field “ID†will be set to the value of the field “Profile_ID†(of the record that was updated in DataPage_1)

IF “B†was selected from the the dropdown box, a new record will be written to Table_3. For this new record:

The value of the field “ID†will be set to the value of the field “Profile_ID†(of the record that was updated in DataPage_1)

IF “C†was selected from the the dropdown box, a new record will be written to Table_4. For this new record:

The value of the field “ID†will be set to the value of the field “Profile_ID†(of the record that was updated in DataPage_1)

Thanks for your help!

[bahar_vm]

I think one solution is to have Caspio write a database trigger to check the record upon update and based on the specified criteria insert a new record to the corresponding table.

Best,

Bahar M.