Forum Discussion

eacy's avatar
eacy
Helper II
9 years ago

Using a text string from one column as a column reference in a calculated column

Hi,

 

I have one table with a column named TBLSYSTEMCODE[TCODE] containing a text string "DCREATED" and an column named TBLSYSTEMCODE[ID] containing the id "1"

 

Then I have a second table named TBLPO[DCREATED] containing a date "01-01-2016"

 

And I have a third tabel named TBLMILESTONE[ISYSTEMCODEID] with a value "1"

 

I want to add a calculated column named TBLMILESTONE[INITIALTIME] which should extract the date "01-01-2016" from TBLPO[CREATED] where TBLSYSTEMCODE[ID] = TBLMILESTONE[ISYSTEMCODEID]

 

The ideas is that TBLMILESTONE[ISYSTEMCODEID] dynamically will change and refer to a row in TBLSYSTEMCODE[TCODE] which refer to another text string e.g. "DCLOSED" which means that the date to extract is TBLPO[DCLOSED] instead.

 

So the problem is how to utilize this "Configuration" text string as a colunm reference.

 

Thanks

Need Help

4 Replies

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    Hi eacy,

     

    Could you please share us some data sample, or a screenshot of your data model?

    If I understand your issue clearly, you would like to use a column value to filter columns in another table, and I don’t think this is available currently.

    What I could think out here is, to alter the table 2 “TBLPO”, transform this table and make it owns a column with the same value in “TBLSYSTEMCODE[TCODE]”, then use this two to filter the date.

    Regards

    • eacy's avatar
      eacy
      Helper II
      9 years ago

      Hi Michael,

       

      Thanks for your feedback.

      If you think about it, it is quite complicated to document and I belive you got it right.

      The main problem I face is to pick up a string from a field and reuse it as a column reference like:

      I have:

      TABLE[reference string] = "Column A"

      I want to use it like:

      IF(TABLE[Column A] = TRUE;bla bla bla)

       

      I would like avoid to add any infomation to the TBLPO table as you suggest because in the future I would like to easily exchange that tabel with a table from another backend system.

       

       

      • v-micsh-msft's avatar
        v-micsh-msft
        Microsoft Employee
        9 years ago

        Hi eacy,

        I did a deeper research on this topic, still can't find any further helpful infromation on this topic.

        Please take consider to submit an idea on this.

        Regards