Solution for data entry form made up from three applications

[simon]

Hi

  I have a couple of instances in an application I'm trying to develop where I would like to combine three applications into a single data form. I don't mind too much how I do it and I've seen from the link below this might be a problem, so all I'm after is a solution that is reasonably elegant.

http://www.scriptcase.net/forum/showthread.php?728-Master-detail-form-one-update-button-to-update-both-master-and-detail&highlight=applications+form

The application/table components I have are : ‘company details’,‘office address’ and ‘power stations owned’ - There is also a linking table between company and power station as there is a many to many potential. All tables are linked with the relevant keys.

The idea is that when a new company is entered in the application an office address and then their power stations can be added for them without moving back and forth through menus.

Simon

[nina_sorensen]

is it necessary for you to use master/detail? i mean: does the “company” has more than one address? more than - lets say - 5 stations (when it is a new entry)?

because: if not, you have the following possibility_

1. make a form from company detail table
2. insert news fields. adress and 5 station fields (or whatever is needed for the two other tables)
3. ajax -> on before insert: save excact company name or any other unique field into a global variable [newcompany]
4. ajax -> onafterinsert : macro select sql field ID (ai from database) from your company-table where companyname is [newcompany]
5. -> new variable: $companyID = ID from select
6. insert new into table companystation id=$companyID, station1= {your field}, station2={yourfield2} and so on
7. company adress table: also insert into sql this way.
8. unset [companyname]

result: all tables are done with one buttonclick.
whats not nice: you have to decide from scratch how many adresses and how many stations are mostly possible for the user when inserting new company. if you are nice, you make an additional “select”-field right in the form and let the user choose how many fields are shown or hidden.
if i do something like this, i allwys use (ajax on update field–>Field1ForTable2) and than if ({Field1ForTable2}!="") { --> show {Field2ForTable2} } else { hide {Field2ForTable2} }
and so on

[simon]

Hi

Alas yes. there are a number of companies who will have more than one address. However, on initial insert of the company record it would be very reasonable to only insert one address. Other addresses as required come later.

That may mean for the combined insert then your suggestion will work. A form and grid with update would allow the addition of extra addresses in the future.

My challenge is though to interpret your suggestion into a solution so any further help would be very gratefully received. I think my big question is where do I create the sql that’s necessary to interpret the added fields for inserting (steps 6 and 7). I’m also assuming that I would set the variable names in the new fields before the insert in step 6 & 7. I’m guessing step 8 would be onAfterInsert.

Thank you
Simon

[nina_sorensen]

@simon;19127

on initial insert of the company record it would be very reasonable to only insert one address.

of course your can set up more adressfields right into insert in the “new” form. its just more data to handle.

@simon;19127

My challenge is though to interpret your suggestion into a solution

thats what my boyfriend allways tells me

@simon;19127

where do I create the sql that’s necessary to interpret the added fields for inserting (steps 6 and 7).

on after insert.

@simon;19127

I’m also assuming that I would set the variable names in the new fields before the insert in step 6 & 7.

i dont get this question very much.

the idea is: you set up the form based upon a table, “company-base”. than you add some new fields, lets say {data1} is a field for sql table “stations”, {data2} is a field for table “adresses” .
before you insert the new data into the database (in this moment, before inserting, you dont know the ID of this data, which is given by sql i guess when inserting, ID must be a AI field, isnt it?) - so: before you insert that new data, you “save” something to remember this dataset. this must be a unique field, for example {companyname}.
after inserting ( -> on after insert ) you get the ID from the sql which is just executed. to do this, you use your unique field help variable, companyname.

must be something like select ID from company_detail where companyname is companyname.

than - this is 6 and 7 - you go on with the code right there, inside “on after insert” - you do a second sql statement: insert into stations
the fields {data1} and {data2} are still there. so you say

insert into stations ((id=> $id) , (sql-data => {data1} ) );

the fields are still there and there is no need to use more variables.

step 7: same procedere.
insert into companyadresses ((id=> $id) , (sql-data => {data1} ) );

remember to unset, because if you dont you could struggle when you insert a second new company.