System Overhead - Repeated Open and Close of Table

[Thomas Jones]

Situation: I have a form that is tied to a 1:M set. The set consists of parent (main inventory table) and child (inventory warehouses table). The purpose of the form is to allow user to enter the results of physical inventories.
A layout session variable appears on the form to capture on hand inventory counts by item. The user then presses a "Save" button on the form and a script is launched that (1) updates the on hand quantity in the parent table, (2) calculates differences in quantity on hand and cost as a result of the physical, and (3) opens three transaction tables to record the results of the physical and then closes these transactions tables.

Concern: Given the nature of this application, the transaction tables could theoretically be opened and closed hundreds of times during a user session. This bothers me as it would seem to create too much system overhead.

Is there anyway to open the transaction tables when the form opens (session begins)and then close them when the form closes (session ends)? I have tried opening them in the OnInit event of the form (using both shared and global variables), but receive an error message "not an open table" when the "Save" script is launched.

No doubt many of you have encountered this type of situation before. Any suggestions would be appreciated.

[Scott Emerick]

RE: System Overhead - Repeated Open and Close of T

Thomas,

Opening and closing a table several hundred times is really no big deal, however, to answer your question, yes there is a way, maybe several, but here is my thought.

Create a set that includes all the tables you need opened and attach your form to the set instead of the table. You might even find working with the set helps data entry.

Now depending on how your set is created .. you can just reference the table.

Example:
Table1 - your main table which is the parent.
table2 - child table
table2 - child table
ect.

Now you can set pointers to these tables like:

Dim tbl1 as p
tbl1 = table.current()

Dim tbl2 as p
tbl2 = table.current(2)

Dim tbl3 as p
tbl3 = table.current(3)

and so on .....


Scott


Table2 - linked 1

[Thomas Jones]

RE: System Overhead - Repeated Open and Close of T

Scott, thank you for replying to my post. I'll probably end up doing what you suggest, though I was hoping for some other way. Let me explain.

In a prior post in February, I asked a question concerning sets, that is, whether it was better to create a large set with many relations or to create several smaller sets. Most replies, as I recall, were on the side of several smaller sets. For output large sets were okay, but for input no sets or smaller sets were preferable.

And so, I guess I was trying to keep it small. As things now stand, my form is based on a set with one child table. If I follow your suggested approach, I'll ultimately have to add two more child tables and one grandchild. Should I keep my original (smaller) set on which other forms are based and create another set for this form with the additional child/gr child relations?

I suppose I'm still having problems understanding the best approach to take with sets in designing my application. When I first started exploring Alpha 5 I had some strange things take place and most seem related to sets. So I have tried to rely on them as little as possible, keeping away from referential integrity and trying to hold down the number of indexes created by multiple relations in the set.

Any additional insights in this area would be appreciated, and thanks again for your suggestion.

[Thomas Jones]

RE: System Overhead - Repeated Open and Close of T

Scott, do you have any experience working with "TBL".PERSIST()? Would it have any relevance in solving my problem?

[Peter.Wayne]

RE: System Overhead - Repeated Open and Close of T

Your question is exactly what "tbl".persist() is supposed to address. You can open the 3 tables in the form's OnInit event and close them in the OnClose event. Strictly speaking, it would probably not be necessary to close them in the OnClose event, since ending the form's session will close the pointers (you can't keep the tables persistently open from one form to another).

[Thomas Jones]

RE: System Overhead - Repeated Open and Close of T

Thanks, Dr Wayne. This is what I've done and it works just as you say. This has been a very useful discovery.

PS, it's nice to know the doctor is out there keeping an eye on things.