Extract Data to Populate Table Columns

[Sheila]

I have raw data fields that contain many values that need to be extracted to their own columns on a table.  In Excel, I was using a nested If(isnumber(search function to extract the various values to their respective columns.

How do I do this in Caspio?  The attached file shows a couple of examples of before and after.

TIA!

S.

Help on Data Extraction.xls

[Sheila]

Can anyone answer or provide any input?  Thanks!

[FlashD]

On 10/9/2017 at 9:33 AM, Sheila said:

Can anyone answer or provide any input?  Thanks!

Hi,

Can you have a look at this post from the forum and try to implement your requirements with it's  help?

 

[nightowl]

Hi Sheila,

This can be done in multiple ways:

1. JavaScript: You might take a look at my other forum post below, although you might need to tweak the JavaScript a bit.  See the REFERENCES section below for the link.
2. Triggers: This is a new feature in the 9.9 release. See below for the link.

REFERENCES:

1. Multiple Values Separated by Commas Input: https://forums.caspio.com/topic/6770-multiple-values-separated-by-commas-input/?do=findComment&comment=21222
2. Triggered Actions (aka Triggers Feature): https://howto.caspio.com/tables-and-views/triggered-actions/

Hope this helps.

-nightowl

[Sheila]

Thanks to both of you.  My file is not comma delimited, so can't separate that way.  Also, the data has many variations.  I want 3 columns to be populated, but the values for each column are many.  For instance, column 1 only has 3 variations that could be populated, but column 2 has as many as 15 and column 3 has over 20 variations.

Here is an example of the function I use in Excel to populate column 1:

ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""Women"",RC[-7])),""Womens"",IF(ISNUMBER(SEARCH(""Men"",RC[-7])),""Mens"",IF(ISNUMBER(SEARCH(""Kids"",RC[-7])),""Kids"",IF(ISNUMBER(SEARCH(""unisex-child"",RC[-7])),""Kids"",IF(ISNUMBER(SEARCH(""Female"",RC[-7])),""Womens"",IF(ISNUMBER(SEARCH(""Male"",RC[-7])),""Mens"",""Other""))))))"

The result is column 1 only contains the values of Mens, Womens or Kids, even though the raw data may contain other values.

The other two columns work the same way, but with more and different variables.  Hope I'm explaining this right.