Referencing by field name

Meeting place for R&R customers and clients to share tips and ideas. Post your questions, messages or problems here.
Post Reply
ifahsozasuaw
Posts: 0
Joined: Tue Mar 06, 2018 10:32 pm

Referencing by field name

Post by ifahsozasuaw » Wed Mar 04, 2009 3:37 pm

I have a table that has week numbers in the field name (columns): wk01, wk02, ... ,wk52 and another column that houses the current processed week number with rows referenced by a key. I need to be able to take the number from the current week column and reference the associated week column name. For example, if the current week number is 8, I need to be able to look at the column named wk08 for the row associated to the key. I have tried concating "Database.alias.wk"+replicate("0",2-len(str(CurrentWk)))+str(CurrentWk)), but I don^t get a reference to the Database.alias.wk08 field, instead just having a text string with the value of "Database.alias.wk08". Any suggestions?

Chris_Strasser_(Guest)
Posts: 47
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Referencing by field name

Post by Chris_Strasser_(Guest) » Mon Mar 09, 2009 4:14 pm

You^re trying to implement indirect referencing, which we don^t explicitly support, currently.____However, you can get the desired results with the appropriate xLookUp() function. Say you have a file called "prearn" and it has two columns called "ytdamount" and "ytdhours" that you want to retrieve data from, but only the one that the user requests.____You^d create a parameter and / or calculation that let you pick the appropriate value and then supply that character string to another calculated field with the (in this case) NLOOKUP() function:____NLookUp(LinkCalc, "YTD" + Field2Pick, "PREARN.DBF", "PREARN.CDX", "EMPNO")____Here, LinkCalc consists of the fields that would normally be used to provide an exact lookup of the record that you are on (the primary key), the second term shows how the fieldname you want to return can be built up via a calculation, the third term identifies the file, and the fourth / fifth terms the index and index tag that you need to make the link. If you don^t have an appropriate index, you^ll have to create one, somehow.

ifahsozasuaw
Posts: 0
Joined: Tue Mar 06, 2018 10:32 pm

==> RE: Referencing by field name

Post by ifahsozasuaw » Thu Mar 19, 2009 3:59 pm

I am using SQL tables, not database files. Another option that I also cannot figure out is joining a constant to the value of a key field in the table to be joined without actually joining the master table. For example, I would like to only join a table with the name "Column1" with the constant "HEADER". Any suggestions on this? Thanks for the last response!

kfleming
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Referencing by field name

Post by kfleming » Fri Mar 20, 2009 10:22 am

If you create a calculated field with the constant, you can select that field as a join field.____Kathleen__R&R Support

Post Reply