Writing to table that is already open

[Sooty57]

Hi all,

I have a script that creates a new record from dialog boxes, as follows:

dim tbl as P
tbl = table.open("activities")
tbl.enter_begin()

tbl.Custid = var->currClient
tbl.Funder_id =var->currFunder

dim filtContact as c
filtContact = "Contactid = " + quote(var->currContact)
tbl.Contact = lookup("Funder_Contacts",filtContact,"Contact")
tbl.Position = lookup("Funder_Contacts",filtContact,"Position")
tbl.Telephone = lookup("Funder_Contacts",filtContact,"Telephone")
tbl.Address1 = lookup("Funder_Contacts",filtContact,"Address1")
tbl.Address2 = lookup("Funder_Contacts",filtContact,"Address2")
tbl.Address3 = lookup("Funder_Contacts",filtContact,"Address3")
tbl.Address4 = lookup("Funder_Contacts",filtContact,"Address4")
tbl.Town_city = lookup("Funder_Contacts",filtContact,"Town_City")
tbl.County = lookup("Funder_Contacts",filtContact,"County")
tbl.Postcode = lookup("Funder_Contacts",filtContact,"Postcode")

dim filtFunder as c
filtFunder = "Funder_Id = " + quote(var->currFunder)
tbl.Organisation = lookup("funders_new",filtFunder,"Funder_Name")
tbl.Charity_no = lookup("funders_new",filtFunder,"Charity_No")

tbl.Created_by = user_name()
tbl.Created_date = date()

tbl.enter_end(.t.)
tbl.close()

This is fine if the "Activities" table is not in use by another form, but the table.open() line causes an error if the table is open.

How do I get around this, please?

[Stan Mathews]

Re: Writing to table that is already open

TABLE.GET()

[Tom Henkel]

Re: Writing to table that is already open

Just a quick comment.

It would be much quicker if you opened up the table you are doing lookups to and just use tbl.<filedname> instead of lookups. There is significant overhead with a lookup.

Open the table at the beginning of the script, do a fetch_find(), populate your new table, close the table.

dim tbl as P
dim tbl1 as P

tbl = table.open("activities")
tbl1 = table.open("Funder_Contacts")

tbl.enter_begin()

tbl.Custid = var->currClient
tbl.Funder_id =var->currFunder

dim filtContact as c
filtContact = "Contactid = " + quote(var->currContact)
tbl1.index_primary_put("Contactid")  '<-whatever your Key is
tbl1.fetch_find(filtContact)
tbl.Contact = tbl1.Contact
tbl.Position = tbl1.Position
tbl.Telephone = tbl1.Telephone
tbl.Address1 = tbl1.Address1
tbl.Address2 = tbl1.Address2
tbl.Address3 = tbl1.Address3
tbl.Address4 = tbl1.Address4
tbl.Town_city = tbl1.Town_City
tbl.County = tbl1.County
tbl.Postcode = tbl1.Postcode
tbl1.close()

[Sooty57]

Re: Writing to table that is already open

Thanks for your help guys

[johnkoh]

Re: Writing to table that is already open

Good Morning, Tom

I still confuse to use (or apply) the Index and Query Fetch method.

In this case,
Do I need any loop scripts (Fetch_first, fetch_eof , While ...end)?

[Mike Wilson]

Re: Writing to table that is already open

John,
If your query filter will produce more than one record and your process will be addressing more than one record, yes you will need to loop.

[johnkoh]

Re: Writing to table that is already open

Hi Mike,

The above script(Tom's) found ONE record and save it on another table without loop method.

[jaryder]

Re: Writing to table that is already open

Paul,

To add to Tom's changes I would move the code to do the fetch to outside of the enter_begin() / enter_end() block of code. The less code you have in the enter_begin or change_begin block the less time you have a file lock established.

dim tbl as P
dim tbl1 as P

tbl = table.open("activities")
tbl1 = table.open("Funder_Contacts")

[COLOR="red"]dim filtContact as c
filtContact = "Contactid = " + quote(var->currContact)
tbl1.index_primary_put("Contactid")  '<-whatever your Key is
tbl1.fetch_find(filtContact)
[/COLOR]
tbl.enter_begin()

tbl.Custid = var->currClient
tbl.Funder_id =var->currFunder

tbl.Contact = tbl1.Contact
tbl.Position = tbl1.Position
tbl.Telephone = tbl1.Telephone
tbl.Address1 = tbl1.Address1
tbl.Address2 = tbl1.Address2
tbl.Address3 = tbl1.Address3
tbl.Address4 = tbl1.Address4
tbl.Town_city = tbl1.Town_City
tbl.County = tbl1.County
tbl.Postcode = tbl1.Postcode
[COLOR="Red"]tbl.enter_end(.T.)  ' got lost in Tom's Changes[/COLOR]
tbl1.close()

[Mike Wilson]

Re: Writing to table that is already open

Hi Mike,

The above script(Tom's) found ONE record and save it on another table without loop method.

Good point Jeff. John, so no in this instance no fetch_first-while-fetch_next-end while loop is needed.

[johnkoh]

Re: Writing to table that is already open

How about without the Index_primary_put()?

filtContact = "Contactid = " + quote(var->currContact)
tbl1.index_primary_put("Contactid")  '[COLOR="DarkRed"]<--- Without this[/COLOR]
tbl1.fetch_find(filtContact)

and in this script, the variable("currContact") was called in the form layout?
or
Should / Can I address it(" currContact=tbl.contactd") on somewhere here?

[Mike Wilson]

Re: Writing to table that is already open

The theread started with this:

I have a script that creates a new record from dialog boxes

I believe you can assume the variables are generated from the values entered in the dialog boxes.

Can I address it(" currContact=tbl.contactd") on somewhere here

No. The table pointer tbl is pointing to the table where you are entering a new record (receiving table). The field contactid which the variable currContact is related to is in the source table pointed to by the pointer variable tbl1. I don't see that the receiving table pointed to with tbl has a field contactid. It has custid but I don't see contactid.

[Sooty57]

Re: Writing to table that is already open

The theread started with this:

I believe you can assume the variables are generated from the values entered in the dialog boxes.

Correct

[Sooty57]

Re: Writing to table that is already open

Thanks to everyone for their help. I had to modify script as follows (changes from Jayrider's post in green), and all is working great now.

dim tbl as P
dim tbl1 as P

tbl = table.open("activities")
tbl1 = table.open("Funder_Contacts")

[COLOR="SeaGreen"]tbl1.index_primary_put("Contactid")  '<-whatever your Key is
tbl1.fetch_find(var->currContact)[/COLOR]

tbl.enter_begin()

tbl.Custid = var->currClient
tbl.Funder_id =var->currFunder

tbl.Contact = tbl1.Contact
tbl.Position = tbl1.Position
tbl.Telephone = tbl1.Telephone
tbl.Address1 = tbl1.Address1
tbl.Address2 = tbl1.Address2
tbl.Address3 = tbl1.Address3
tbl.Address4 = tbl1.Address4
tbl.Town_city = tbl1.Town_City
tbl.County = tbl1.County
tbl.Postcode = tbl1.Postcode
[COLOR="Red"]tbl.enter_end(.T.)  ' got lost in Tom's Changes[/COLOR]
tbl1.close()

[CALocklin]

Re: Writing to table that is already open

I minor FYI that many people don't understand:

The ".T." in
tbl.enter_end(.T.)
does not to be specified since it is the default. So, 99% of my commands are simply
tbl.enter_end()

The argument simply determines whether or not the "enter" action is actually completed. However, I can't think of a situation in xbasic where I couldn't determine whether or not the new record should be created even before starting the tbl.enter_begin(). And, if it shouldn't be created then there's no reason to start it.

The only time I use this argument is during development when I want to run through the script but not save the new record (or save changes when using change_begin/end) because I just want to test other things in the script and run the same thing over and over until everything else works.

In the case of that situation during development testing I do something like this:

IF api_getmachinename() = <MyComputerName>
    commit_flag = .F.   'Change to .T. when ready to test actual results.
ELSE
    commit_flag = .T.
END IF
....
tbl.enter_begin()
...
tbl.enter_end( commit_flag )

There were too many times where I forgot to change the commit flag before sending it to the customer. Therefore I added the IF statement so the commit_flag would always be true on any computer except my development computer. (For those of you who have read my many rants about using a keyboard macro program, this IF statement is another of my macros - I type jjcommit and the macro program replaces it with the complete IF/END IF statement.)