Field name in a variable

[Ron Cooper]

I need a piece of code that will replace a Tables' subelement with a variable i.e. replace a field name with a variable.

I have two Tables: Table 1 (tbls) "Seniors", Table 2 (tblt) "Telephonebook". Table 1 has 150 records of Forename, Surname and Telephone. Table 2 has 1 record with 450 fields - F1,S1,T1 ........... F150,S150,T150.
I am trying to construct Table 2's Field names as a variable and then copy the contents of Table 1 to the Fields in Table 2.

Code in English:-
X = 0
Go to the first record in the file Seniors
Whilst not EOF Seniors
x = x + 1
construct 3 Fields F1, S1,T1
Table "TelephoneBook" Field "F1" = Table "Seniors" Field "Forename"
Table "TelephoneBook" Field "S1" = Table "Seniors" Field "Surname"
Table "TelephoneBook" Field "T1" = Table "Seniors" Field "Telephone"
Go to the next record in the Seniors file and construct 3 new fields using x
Continue until the EOF Seniors
The table Telephonebook will now contain 450 completed fields from F1,S1,T1......F150,S150,T150
At the moment I have done this with 150 IF statements - not a pretty sight, but it works!

[Ton Spies]

Re: Field name in a variable

Ron,

Is not what you ask in contrary to the principles of a database.
How can you keep all data up to date in only one record?
What can you do with that one-record-table?

For me, these questions block me to help you.

Sorry, Maybe I am totally wrong and do not understand you.
But if it is a only once job I would do it by hand with excel.

Ton

[Ron Cooper]

Re: Field name in a variable

Ton,

I agree with you it could be done in Excel but it is really simple with a script. The base table is a simple Forename, Surname, Telephone No. A simple script takes this information and transfers it to this one record table. The table is used in a form to print an A5 telephone book. This book is constructed from one sheet of A4 card, printed both sides, folded vertically and then guillotined in half, thus making the A5 booklet. I have programmed it and it is instantaneous but as I said I do not like 'long-winded' code.

Ron

[Ton Spies]

Re: Field name in a variable

Ron,

unknown to your application I would suggest that you print the list from senior table. Then you can sort on name -if you like- before printing.

I think you now have done a lot of work to make a report with all the different fieldnames and now to make a script transferring your records.
IMHO you always must update your script and report after entering new records or deleting records from the senior table. Or do you update the telephone Book file? If you can?

Can take a look at Alpha Sports application give you more knowledgde about what you can do with Alpha Five?

Anyway, Welcom at Alpha's Forum. This is the place for discussion about almost everything about Alpha Five.

Ton

[Peter.Wayne]

Re: Field name in a variable

Ron,
This should do it:

t1=table.open("Seniors",file_ro_shared)
t2=table.open("Telephonebook",file_rw_exclusive)
t2.zap(.t.)

t1.fetch_first()
i=1
while .not. t.fetch_eof()
t2.enter_begin()
  eval("t2.f"+i)=t1.forename
  eval("t2.s"+i)=t1.surname
  eval("t2.t"+i)=t1.telephone
t2.enter_end()
i=i+1
end while
t1.close()
t2.close()

The eval() function lets you reference just about anything as a variable.
- Peter

[Ron Cooper]

Re: Field name in a variable

Peter

That worked - thank you very much.
Am I allowed to ask another question - well here goes anyway. I have been using Superbase for 30 years and when I create a table with one of the fields indexed, it remains in that order and when I add a new record it automatically inserts the record in the correct order. When I add a record to an Alpha 5 table, it adds it to the end of the file. I can then sort the file/table but on closing it reverts to its previous order.
What am I doing wrong?

Regards

Ron

[Ron Cooper]

Re: Field name in a variable

Ton

The file is in order, but only in blocks in two columns.

Page 1 Page 2
Fields [1-28] [141-168] Club Logo
[57-84] [85-112] [113-140] [29-56]

I now have the solution eval() does the job very well.
Thanks for your help.

Regards

Ron

[Ron Cooper]

Re: Field name in a variable

Ton

Sorry about the previous posting, it stripped the blanks.
The pages are in two columns:-

Page 1
Top of page Column 1 Fields [1-28] Column 2 Empty
Bottom of page Column 1 [57-84] Column 2 [85-112]

Page 2
Top of page Column 1 Fields [141-168] Column 2 Club Logo
Bottom of page Column 1 [113-140] Column 2 [29-56]

I wish I could lay this posting out better. I have a 'layout' form that shows the diagram, but I am new and I do not know what files to attach to this posting.
I now have the solution eval() does the job very well.
Thanks for your help.

Regards

Ron

[Ton Spies]

Re: Field name in a variable

Hi Ron,

Since Alpha Five we call a file with data a table and all the tables together are called a database.

Normally a table is build on historical data entries.
In case the fields are e.g. firstname, lastname, date, telephone, city.
If you had sorted your table on lastname and you now would have a list on city and lastname you should resort the table.
Tomorrow you will have another sort and so on.
A table can have many records and sorting requires a lot of time then.
Take note that the table is not updatable while sorting.
Working with more users who frequently sort the table is a nightmare because every user wants his own sort.

So with indexes you can get the data in the order you've specified (in the indexbuilder).
If you want to have a list on city and lastname and date, define that as an index. You can have more than one index. Therefore there is no need to sort your data. Make use of an index for every wanted view. If you need the data on a form, a list or a browse just choose one of the indexen you've made.
Important to know is that indexes will be updated while updating the data.

Let Alpha Five manage the data, you take care of entries and updating so the data is usable any moment in any way you want.

I hope this helps to understand the basics.

Ton

[Ron Cooper]

Re: Field name in a variable

Ton

Thanks for that. Is there some way I can save the last index used, so that the table will always open in that order?

Regards

Ron

[goldberg2727]

Re: Field name in a variable

In this script the purpose was to allow the user to search for the values of any one selected field in the inventory table. It runs on an "on Push" event for a button called btnFind on a form. The user selects first selects a field name from the inventory table and stores the name in Character type in the variable vSearchBy, then the user enters the value they wish to find in that field. Then the users selects the parameter for the search (i.e. - Exact Match, Contains Words Like, Contains Exact Words). Then the clicks the button labeled "Find in Table" and this script runs.

*Just another way to do it.

'On push for a "Find In Table" button on a menu.
'THe purpose is to search any fields in the inventory (arvint) table.
'The three values determine the fields in the inventory (arvint) table to search, the text to search for, and the parameters of the search (i.e. exact match etc)

DIM vSearchBy as c '
DIM vFind as c
DIM vSearch as c

vSearchBy = MainMenu:vsearchitem.value 'List of Fields in the Inventory (arvint) table.  Value of variable is the name of the selected field
vSearchBy = alltrim(vSearchBy)

vFind = MainMenu:vsearchitemfor.value ' THe text to find, must be able to make ths value of this field have the data type of the field being searched.
vFind = alltrim(vFind)

dim tbl as p 
'get a pointer to the primary table of the form (the inventory form
tbl = table.open("arinvt")

'Dimension variables
DIM vFieldType as c
DIM vFieldNum as n
DIM vFieldList as c
DIM vField as p
dim filterExpression as c 
dim orderExpression as c 

'get the field being searched into a pointer variable (vField) and also figure out what date type that the field selected is (vFieldType)
vFieldList = tbl.field_name_get()
vFieldNum = word_number_get(vFieldList,vSearchBy,crlf(),.t.)
vField = tbl.field_get(vFieldNum)
vFieldType = vField.type_get()

'Show the field type...for debugging
'ui_msg_box("Field Type is...",alltrim(vFieldType))

'Set the filter and order expressions based on what the data type is of the field being searched
' Could do this also in a more simplified way using the convert_type() functions. So something like....

' ""+vSearchBy+" = convert_type('"+vFind+"','"+vFieldType+"')"     'Not Tested unsure of results  but would replace if then clauses

'could also us Select Case Logic for each different field type for the fields in the inventory table
if vFieldType = "N" then
	filterExpression = ""+vSearchBy+"=val('"+vFind+"')"
	orderExpression = "Itemno"
else if vFieldType = "D" then
	filterExpression = ""+vSearchBy+"=ctod('"+vFind+"')"
	orderExpression = "Itemno"
else if vFieldType = "C" then
	filterExpression = "contains("+vSearchBy+",alltrim('"+vFind+"'))"
	orderExpression = "Itemno"
else
	ui_msg_box("Error","The search function only supports character, date, and numeric fields, not"+ " " + alltrim(vFieldType)+".  " + "type fields.  Please ask your programmer to add this feature if it is necessary")
	end 
end if

dim i as p 
dim count as n 
'i = the remaining records in the inventory report after the filter and order

i = tbl.order(orderExpression,filterExpression)
count = i.records_get()

' If we found no records, then we put up a warning and show all records
if count = 0
	ui_msg_box("Warning", "No records found. Showing all records")

	filterExpression = vSearchBy + "<>" + quote("")
	orderExpression = "Itemno"
	i = tbl.order(orderExpression,filterExpression)
	count = i.records_get()
	if count = 0 then 
		topparent.allow_change(.f.)
	end if 

	mulstbtn1.text = "1"  '' I chose '1' because it is not in the set A-Z that we display 
end if

DIM varP as p
'show results from the inventory table in a browse view where the user can double click the record to view the inventory record details

varP = :browse.ViewQueried("brwInv_Ar",filterExpression,orderExpression)

[Ron Cooper]

Re: Field name in a variable

Lee

Thank you for that.
How did you do that scrolling 'frame' with the code?

Regards

Ron

[MikeC]

Re: Field name in a variable

Ron,
As Ton pointed out, an important concept is that a form or browse is just a "view" of the records in the table....and as such can be sorted/filtered to present different ways of looking at the data.

If you want a form or browse to always be sorted/filtered ALWAYS, then use the form properties to do this...it sets a base filter that cannot be dropped unless specifically coded to do so....Or use the index method and set the index upon viewing the records....Or set the filter in the OnInit or OnActivate events of the form/browse...

So many ways!! :)

----the use of the Code Tags in the advanced reply part of the post process will keep code formatting and add the scrolling you see. Just highlight the code and click on the Code Tag icon #