Newbie Question on Variable Field References

[biredale]

Hi all,

 

Just a quick question on using variable field names;

 

If I have a table with a field called Month (populated as Jan, Feb, etc..) and a different table with an Item (populated as Widget1, Widget2, etc; also with a field for each month; Jan, Feb etc..)

 

Is it possible to use a calculated field in the Month table using the Month field as a variable field name to reference the column in the Item table?

 

I bet that was clear as mud...

 

Month Table

 

Month - Count (calculated)

jan - 

feb - 

 

Item Table

 

Widget - Jan - Feb

 

Widget1 - 2 - 3

Widget2 - 0 - 2

 

Resulting in 

 

Month - Count

jan - 2

feb - 5

 

Thanks for the opportunity to as stupid questions..

 

Cheers!

 

 

[Wim Decorte]

Automatic message

This topic has been moved from "Articles, Tips, Techniques & Solutions" to "Calculation Engine (Define Fields)".

 

I moved the topic to the proper forum and deleted your duplicate posts

[Wim Decorte]

Sure, a field can be referenced dynamically.  Check out the GetField() function

 

I'm not sure that trying to do this with calcs is a good approach.  Historic sales data is something that should be calculated only once and then stored, as it is not going to change anymore.

Using live calculations may be very very slow depending on the amount of data you will have.

[comment]

a different table with an Item (populated as Widget1, Widget2, etc; also with a field for each month; Jan, Feb etc.

 

This is where your real issue lies: instead of 12 fields, one for each month, you should have 12 records (one for each item/month), so that your Sales table would look something like this:

ItemID    Year    Month    Qty
001       2013      1       2       
001       2013      2       3
002       2013      2       2
...

This can be summarized iany way you want - without requiring "variable field references" or a Months table that doesn't hold any information.