Grooper 21.00.0082 is available as of 12-12-2023! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.00.0042 is available as of 03-22-2024! Check the Downloads Discussion for the release notes and to get the latest version.
Grooper 23.1.0018 is available as of 04-15-2024! Check the  Downloads Discussion  for the release notes and to get the latest version.
Options

Question on an Expression to populate a column/field based on the value of another

tmartintmartin Posts: 82 ✭✭
in The Astronauts (Q&A)
I have run into a could of scenarios where I need to populate a column in a table or a field in a data model based off of the value of a another field. I feel like this should be a pretty straight forward expression, but once again, I don't know the syntax. Any help is appreciated. 

Best Answer

  • Options
    tmartintmartin Posts: 82 ✭✭
    Answer ✓
    I worked with Matt and we came up with the following expression:
    IIf(Len(Last_Date.ToString)>0,"Inactive","Active")

    This is converting the Last_Date field from a DateTime type to a string. Once this occurs, the if statement makes a comparison using the length of the "Last_Date" value. 

    There is probably a better, more elegant expression, but this is working for the moment.


Answers

  • Options
    RandoCalrisianRandoCalrisian Posts: 195 admin
    edited January 2020
    What you need to use here is a Calculate Expression, and as you guessed, it's very straight forward.

    To test the most simple example, create a Content Model with a Data Model and give it a couple of test Data Fields.  Set the first one to use a Default Value Expression of 0.

    On the other Data Field, open the Calculate Expression editor, and simply type in the name of the other Data Field.  Intellisense should detect as you type. Use a Calculate Mode of Set if Empty or Always Set.

    Click on the Data Model and Test Extraction. You don't need a specific document or anything, you should just get 0s returned on both, since the default for one was set, and the other is calculated to be the other.

    Randall Kinard
    rkinard@bisok.com

  • Options
    tmartintmartin Posts: 82 ✭✭
    That is cool, but what I need is this: Building on your scenario:

    Field02's value is based on what is extracted for Field01. For example, if Field01 extracts a date, Field02 would be populated by "Inactive", otherwise, Field02 would be populated by "Active."

    Another scenario would be if column X returns a value of "W," the value of column Y should be overridden with a value of "0," regardless of what is extracted.
  • Options
    RandoCalrisianRandoCalrisian Posts: 195 admin
    edited January 2020
    So, to expand on my example, I used the following syntax for the Calculate Expression of Field02:
    IIf(Field01=0,"Inactive","Active")<br>
    In this case, if Field01=0, then Field02 will list the string Inactive, otherwise it will list the string Active.
    Does this get you any closer?
    Randall Kinard
    rkinard@bisok.com

  • Options
    tmartintmartin Posts: 82 ✭✭
    Ah. This works to an extent. My specific scenario is involves a field that extracting as a DateTime type. I need to convert this to a string before running this calculate expression, otherwise I get an error. Do you happen to know how to put that conversion in front of this expression?
  • Options
    RandoCalrisianRandoCalrisian Posts: 195 admin
    You need the field with the expression to return a specific value if and only if the referenced field has a date, otherwise it returns some other value.
    You don't care what the date in that field is?
    Randall Kinard
    rkinard@bisok.com

Sign In or Register to comment.