Running an append using an array

[glenschild]

Hi

I am struggling to get this to work!

I have three tables.

Table A (Owners) contains details of individuals entered into a competition. The [ownerkey] field is the unique id field.

Table B (fixtures) contains details of games taking place. The [gamekey] field is the unique id field.

Table C (predict) is the recipient table for the new records. The structure is the same as fixtures except for three extra fields (Ownerkey, fname, lname) all of which are present in the owners table.

For each owner in the owners table I need to append 1 copy of each record in the fixtures table so say there are 20 owners and 30 fixtures there would be 600 records in the predict table. If another 2 owners are added then an additional 60 records would need to be appended.

So I figure that I should create an array for the owners table records (which works in the interactive window) and then work through each entry using a for each loop.

The script below attempts to do this but falls over when it reaches the first field needed from the array. (see attached image)

DIM Append as P
DIM UserFname as C
DIM UserLname as C
DIM UserKey as C
dim AppUserkey as C
dim tbl as p
dim arr_count as N

tbl = table.open("owners")
arr_count = tbl.records_get()
tbl.close()

dim raw[arr_count] as P
dim ownerkey as P
raw.initialize_from_table("owners")
dim i as N
for i = 1 to arr_count
userkey = raw[i].ownerkey
AppUserkey = ""
AppUserkey = "str(GM_KEY_F,3,0)-"
AppUSerkey = AppUserkey+userkey
UserFname = raw[i].fname
UserLname = raw[i].lname

a_tbl = table.open("predict")
append.t_db = "fixtures"
ON ERROR GOTO ERROR24052010161937986

append.m_key = "str(GAMEKEY,3,0)-OWNERKEY"
append.t_key = AppUserkey
append.m_filter = ""
append.t_filter = "Cmp_Rd_F=1"
append.type = "Unique only"
append.m_count = 7
append.m_field1 = "Gamekey"
append.m_exp1 = "@Fixtures->Gm_Key_F"
append.m_field2 = "Comprnd"
append.m_exp2 = "@Fixtures->Cmp_Rd_F"
append.m_field3 = "Hmtmkey"
append.m_exp3 = "@Fixtures->Hm_Tm_F"
append.m_field4 = "Awtmkey"
append.m_exp4 = "@Fixtures->Aw_Tm_F"
append.m_field5 = "OWNERKEY"
append.m_exp5 = UserKey
append.m_field6 = "FNAME"
append.m_exp6 = UserLname
append.m_field7 = "LNAME"
append.m_exp7 = UserLname
append.t_count = 0

a_tbl.append()
NEXT


GOTO CONTINUE24052010161937986
ERROR24052010161937986:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE24052010161937986:
a_tbl.close()
'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if

Any thoughts gratefully received.

Kind regards

[Stan Mathews]

Re: Running an append using an array

A key expression is a field or combination of fields that Alpha Five uses to determine which table records are considered unique.

I don't see how you can make an array value fit that requirement.

You should be able to do this with an xbasic loop instead of the append operation.

[Tom Cone Jr]

Re: Running an append using an array

I agree. A pair of nested loops. Loop through the owners, and for each owner loop through the fixtures.

[glenschild]

Re: Running an append using an array

Tom & Stan

Thanks, guess I was trying to make things too difficult for myself again!

Regards

Glen