How to reference the field name of the table in xbasic

[psuwan]

Dear All,

My question is,

I have 2 tables (table A, and table B.) with comprises of fields as follows
TABLE A.
1. Staffid Character
2. January Numeric
3. February Numeric
......
......
13.December Numeric

TABLE B.
1. Staffid Character
2. TransactionDate Date
3. Amount Numeric

I read the data from table B. and convert "Transactiondate" to character by using cmonth(,)when I get the the month name I would like to update the table A. according to the month name (Jan feb mar.....)

Example
Staffid TransactionDate Amount
00001 22/06/2010 50,000.00

So Cmonth(TransactionDate) will return the value "June" then I will update table A field name "june" with amount from table B. I can do by using case command but I think it should have the other way to shorten the code than I think.

If someone can help please or if there are previous message like this please help because I try to search but I can't.

Thank
Panya.

[MikeC]

Re: How to reference the field name of the table in xbasic

Panya,
I am curious as to why you would want to store this as it would basically be redundant data....a set created with Table A as Parent and Table B as child, using a One-to-One relationship (if I understood correctly) and StaffID as the link.

Maybe there is another purpose I am unaware of, but if this is for display or report purposes then I think a set is more appropriate (and normalized).



EDIT LATER: If there are going to be more than one transaction (probable), then maybe a compound linkage using the StaffID and the months' names would work....

EDIT EVEN LATER: If this is something that is deemed necessary, then an update operation could be used utilizing your months as conditions and StaffID s as links.

[MikeC]

Re: How to reference the field name of the table in xbasic

I made some edits I realized that perhaps were done as you were reading the initial reply of mine....wanted to point that out as well as to answer your original question that is in the thread title.

Using table methods, you have to first open or "get" the table.....if the table is already open, then table.get("your_table_name") will suffice most times. Otherwise use table.open("your_table_name"). a bit of psudocode below that may get you there in your original thinking.

dim tbl_1 as p
tbl_1 = table.open("Table_A")
dim tbl_2 as p
tbl_2  = table.open("Table_B")

tbl_1.enter_begin()
select
  case Cmonth(tbl_2.TransactionDate)="January"
    tbl_1.January = tbl_2.Amount
  case
    .and so on as you indicated for each month
    .
    .
end select

tbl_1.enter_end(.T.)

tbl_1.close()
tbl_2.close()

You would be looping through the table as well....using

tbl_1.fetch_first()
while .not. tbl_1.fetch_eof()

'select/end select code in prior post put here

end while


But as I said....I'd try an update (or posting) operation instead of the loop and let the genie do the heavy lifting.

[Tim Kiebert]

Re: How to reference the field name of the table in xbasic

Mike, I think Panya is trying to not use the select case construct

Panya, check out the eval() function

Dim tbl_A as P
Dim tbl_B as P
......
What ever code you have to get the correct records...
......

tbl_A.change_begin()
eval("tbl_a."+Cmonth(tbl_B.TransactionDate) = tbl_B.Amount
tbl_A.change_end()

[psuwan]

Re: How to reference the field name of the table in xbasic

Hi

Thank a lot (both Mike & Tim), your answers have valued for me, especially eval() function, it's the thing that I want but I don't know before until you suggest me how to use.

Thank again for your kind.

Panya