Data Entry using intermediate/buffer tables

[fmstudent]

Hey,

I have a database with two tables - Main and Sub. There is a one-to-many relationship between the two i.e. One record in Main table has a unique key mapping to multiple records in the Sub table. I'm creating a data entry layout where I don't want users to access the two actual tables. Also, the data entry layout has a portal which means that user will be entering data into both tables at the same time.

To protect my data, I intend to create two temporary tables(Main-temp & Sub-temp) with same schema as Main and Sub tables. All user data entry will go into these temp tables. And the user can either create new records or edit existing records (For edit records, I'll make copy of the existing records from the Main/Sub tables to the temp tables. Then I'll let the user edit them) After the user is done, he clicks on a "Save" button and I want to run a script to update (either edit existing records or create new records) the original tables with the new data that the user has entered.

Which would be the best script steps to use to implement this? Can you please help me out? Please let me know if you need more information on my problem

Thanks!

[bcooney]

I'm really not following how your design might solve whatever data integrity problems you're trying to avoid.

What are Main and Sub? Typically, tables are named for the entities that they represent. I feel that you're abstraction isn't serving a purpose here.

Are you familiar with RLA (Record Level Access) and FM Security in general? You could limit edits to specific groups (privilege sets). Also, you can have audit logs for any user edits. See Ray Cologon's UltraLog.

[David Jondreau]

Using globals instead of regular fields is the way to go.

I'd say use an Import[] in your script too.

[fmstudent]

Sorry about the confusion. This database was designed 5 years ago and I'm working to do some modifications to it. The current data entry layout is an exposed view of the database. Hence, it is easily possible to modify data accidentally without confirmation. If I set it to prompt for saving, the pop-up comes up very often which is annoying. Hence, the schema I was thinking about was to safeguard against accidental modifications and to allow users to check their entries before saving. In simple terms, the user is often a novice with not much idea as to the data he is entering into the database.

Btw, Main table is a table containing TRANSACTION level data. Sub table contains data that breaks up each transaction into ITEM level (each transaction can have multiple items purchased) information.

I hope this post along with the previous one would make it clear.

One more thing is - I cannot use globals because my data entry layout would be something like a portal. I do not know how many items would be entered. Hence, I do not know how many global fields I should create. I'm planning to give an "Add Portal Row" script to portal so that user can add as many items in the portal rows.

Thanks for your interest in replying to my problem!

[comment]

If I set it to prompt for saving, the pop-up comes up very often which is annoying.

It comes up only when user is trying to commit the record/s. To prevent the dialog coming up when user clicks in empty space, you can use an empty web viewer as the layout background.

[fmstudent]

What a simple solution! Thanks :)

[bcooney]

" In simple terms, the user is often a novice with not much idea as to the data he is entering into the database. "

And so, why is he allowed to edit?