Liveware Publishing Support Forum

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?

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.

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!

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