536116_en-US

liliconcom

Conditional formulas and free text in the same column

I'm creating a data table template to be used by others and submitted to me. In some cases, the data in ColB is a function of ColA, but there are conditions where this isn't true. I'd like to make life easier for the submitter when possible by populating ColB automatically (using a formula) once ColA is entered but still allow them to enter a custom string in ColB if their ColA entry doesn't fit the pre-defined relationship. Is there a way to achieve this? I naively hoped that leaving the "else" condition blank in my if statement might enable this, but I was wrong.

 

Bonus: It'd be even better if I could then use List Checks on top of all this functionality (as there are still only a set list of acceptable values for ColB), but JMP has already thrown errors telling me I can't add a formula on top of a List Check (which I guess makes sense given that a formula and free text can't co-exist).

 

I have a hunch that I'm trying to do too much with a simple table and will probably need a fancier input form to pull all this off. But just wanted to check before going down that path. Thanks in advance.

Re: Conditional formulas and free text in the same column

Ahh, I see! Put the 'color' formula in the "Material 2" column but apply it to the "Material (if needed)" column that has the list check. Much better! I was trying to put the formula in the "Material (if needed)" column. Great stuff!

Re: Conditional formulas and free text in the same column

Yes, you can color the cell of the other column. The formula is a little more complicated and you have to both add the color when needed and remove it (in case it was added earlier) when not.

 

Match( :Furniture Type,
 "Bed",
  If( Is Missing( :"Material (if needed)"n ),
   :"Material (if needed)"n << color cells( "Red", {Row()} );
   "PLEASE ADD VALUE IN MATERIAL COLUMN";
  ,
   :"Material (if needed)"n << color cells( "Black", {Row()} );
   :"Material (if needed)"n;
  ),
 "Chair", "Wood",
 "Sofa",
  If( Is Missing( :"Material (if needed)"n ),
   :"Material (if needed)"n << color cells( "Red", {Row()} );
   "PLEASE ADD VALUE IN MATERIAL COLUMN";
  ,
   :"Material (if needed)"n << color cells( "Black", {Row()} );
   :"Material (if needed)"n;
  ),
 "Table", "Metal"
)

Re: Conditional formulas and free text in the same column

Thanks, @Peter_Hersh! Sorry, I struggled to explain it very well. I think Jeff's proposal should work though. I really appreciate your input!

Re: Conditional formulas and free text in the same column

Thanks, @Jeff_Perkinson! I think this is a decent implementation as the instructions will auto-populate as the user fills out content left-to-right for a given row. I should have thought about building instructions into a formula.

 

If it's possible to automatically color the cells in the "Material (if needed)" column based on the values in the "Material 2" column I'd be all ears. I tried to implement this with a formula but then I couldn't keep the list check in the "Material (if needed)" column. This way I could just use a color indicator for a cell that needs to be filled out and hide the "Material 2" column from the inputter entirely (as they don't need to see the "Material 2" output).

Re: Conditional formulas and free text in the same column

Thanks, @DonMcCormack! Yes, Row States has been on my list of things to learn. Thanks for pointing me to them. I'll look into it.

Re: Conditional formulas and free text in the same column

@Matt_Converse, I think the way I would handle this is with two columns for the user to fill, one for (using your example above) Furniture Type and another for Material and then your formula column would use your look up for those Furniture Types you know, and the Material column for those that you don't.

 

In your formula you can check whether the Material (if needed) column is filled or not and alert the user that they need to fill it. You can also use a List Check property on the Material column.

 

 

Re: Conditional formulas and free text in the same column

Hi Matt,

 

It might make sense to have a quick call. I might not be fully understanding what you are attempting to accomplish.  Feel free to reach out to me at peter.hersh@jmp.com 

 

You can easily create a single script that will create all of these column-to-column relationships at once.  Unless there is some reason that this has to be done.

Re: Conditional formulas and free text in the same column

@Matt_Converse you might take a look at using Row State Handler in conjunction with a function that check and updates the column of interest. It would be a bit tricky since Row State Handler is executed anytime there is a change in a row state, passing the row number(s) of the selected and unselected rows. You'd probably have to add conditions to make sure the function was only run under certain circumstances (say, if the value in the target column is changed). The benefit is that you'd avoid needing to use a function or value checking the target column. Look in the Scripting Index and documentation for examples.

Re: Conditional formulas and free text in the same column

Thanks, @Peter_Hersh! I hadn't thought about using recode. I envision a few challenges though as my dataset (and 'customer' workflow) is more complex than I've alluded to:

1. An entire row of data is entered before a second row is added. Therefore, the script (albeit a simple play button) would need to be run for every row and I'll need to add some lines to make it not overwrite previously entered non-question-mark text (which I imagine is doable).

2. There are multiple column-to-column relationships where I'd like to leverage this feature (i.e., between ColA and ColB as well as between ColC and ColF, and so on), so the user will need to run the script multiple times for a given row as well as known when to play the script, etc which seems a bit clunky. Alternatively, I could rearrange the columns (ColA, ColC, ColB, etc) or ask the user to jump from ColA to ColC, etc so the script can be run just once per row, but these options don't sound very user friendly either.

 

If you think 'recode' is my best workaround I'll probably give it a try. Thanks for your input!

Re: Conditional formulas and free text in the same column

@Matt_Converse Have you tried using Recode?  In your example, a chair would be wood and the bed would be blank.  You could run a quick script to run this recode then add a list check property.  The list check will allow the user to right-click and pick one of the values in the list.  If you use the log in JMP 16 it will capture all these changes, so you don't have to script.   Try this below. 

//Make Data Table

New Table( "Example 2",
 Add Rows( 7 ),
 New Column( "Furniture",
  Character,
  "Nominal",
  Set Values( {"Chair", "Bed", "Chair", "Bed", "Stool", "Stool", "Bed"} )
 )
);

Wait (2);

// Recode column: Material
dt = Data Table( "Example 2" );
dt << Begin Data Update;
col1 = dt << New Column( dt:Furniture );
col1 << Set Name( "Material" );
dt << Move Selected Columns( {col1}, after( dt:Furniture ) );
dt << Recode Column(
 dt:Furniture,
 {Map Value(
  _rcOrig,
  {"Bed", "?", "Chair", "Wood", "Stool", "Metal"},
  Unmatched( _rcNow )
 )},
 Update Properties( 1 ),
 Target Column( col1 )
);
dt << End Data Update;


// Clear row selection
Data Table( "Example 2" ) << Clear Select;


// Change column list check: Material
Data Table( "Example 2" ):Material << List Check( {"?", "Metal", "Wood"} );

Re: Conditional formulas and free text in the same column

Thanks for the prompt reply, Peter. I learned a bit more about Value Labels looking at it. However, unless I'm missing something, ColB is still ultimately a function of ColA. I'll need the option for ColB to be independent of ColA in cases where ColB has a one-to-many relationship with ColA.

 

So, for example, say ColA is "Furniture Type" and ColB is "What material is the furniture made of?". If the user enters "Chair" in ColA then ColB automatically populates with "Wood" because I know a priori that the only chairs they would have would be made of wood. But if they entered "Bed" in ColB then ColB is left blank for them to fill out with either "Wood" or "Metal" because they might have either type. Alternatively, ColB could become grayed out if they put "Chair" in ColA (since I already know what ColB should be and I'm just trying to save them time and avoid typos), but I couldn't get that to work either since I also performed that operation with a formula.

 

Hope this is a little more clear. Thanks!

Re: Conditional formulas and free text in the same column

@Matt_Converse,  Good question.  Here is an example of something similar to what you asked.  I used Value Labels where you can include a range.  If this value falls in a specific range it will be displayed as a label.  This might accomplish what you are hoping for or at least in part.  You could run the list check on Col A since it is independent and if it falls outside of the desired range that could work also.  Anyway check it out let me know if you have any questions.