Formula to Condense a Series of Numbers with hyphens

Paul_Warren · June 23, 2020, 6:23pm

I am looking to take a long string of comma separated numbers (it is a string from a look up). Here is an example of numbers to summarize:

1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.33-15, 1.33-16, 1.33-17

If you look closely, there are missing numbers. I am hoping to find a formula that will output:
1.17-01 --> 1.17-14, 1.17-17, 1.17-18, 1.33-01, --> 1.33-05, 1.33-07, --> 1.33-17

I am flexible on the “–>”, but as you can see a simple dash won’t work, because dashes exist in the numbers. Has anyone seen something like this before? Any help would be greatly appreciated!!

ScottWorld · June 23, 2020, 6:52pm

I’m still learning Javascript myself, but the only way that you could do this in Airtable would be to write a custom JavaScript script, and then you would need to manually run the JavaScript whenever you wanted to update your result. There are a few JavaScript experts in this community, so hopefully one of them will chime in!

Bill.French · June 23, 2020, 10:45pm

I see the missing numbers but I don’t quite follow the pattern you seek to transform this data from or to. What are the rules?

Paul_Warren · June 23, 2020, 11:00pm

The data set is in the form of LOT.Location to create a unique ID. The data set is a string of unique IDs. I would like to condense consecutive IDs to be First --> Last, Next, Next, First --> Last, etc…

A simpler version would be condensing 1, 2 ,3 ,4 ,5, 8, 9, 10, 11, 15, 16, 17, 20 to 1 --> 5, 8 --> 11, 15 --> 17, 20.

Let me know if that is still unclear. Thank you!!

Bill.French · June 26, 2020, 3:52pm

Yep - I think I see the rules and I’ll be the first to go out on a limb and suggest that without Split() in the formulas toolbox, this problem cannot be solved in a formula. And if someone does by chance nominate a solution, I’ll bet a fine turkey sandwich that it won’t be a practical solution.

The rules seem simple -

Sort the unique IDs to create sequential [ascending] order.
Batch the contiguous items

Given the sample data, is this the outcome you’re looking at?

If so, here’s a script block that does this - code included below.

/*

   ***********************************************************
   Airdrop - Unique Code Batching
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

output.markdown('# Contiguous Batching');

// create the test list
let thisList = "1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-15, 1.33-16, 1.33-17";

// create the array (sans spaces)
let aList    = thisList.toString().replace(/ /ig, "").split(",");
aList.sort();
output.inspect(aList);

// create a 2d array
let aData = [];
for (var i in aList)
{
    aData.push([aList[i], aList[i].toString().replace(/\./ig, "").replace(/\-/ig, "")]);
}
output.inspect(aData);

// batch the sets
let thisBatch = aData[0][0] + " => ";
for (var i = 0; i < aData.length; i++)
{
    thisNumber = parseInt(aData[i][1]);
    try {
        if (thisNumber + 1 == parseInt(aData[i + 1][1]))
        {
            // we have continuity
        } else {
            // continuity broken
            thisBatch += aData[i][0];
            output.markdown(thisBatch);

            thisBatch = aData[i + 1][0] + " => ";
            // i += 1;
        }
    } catch (e) {
        // do nothing
        thisBatch += aData[i][0];
        output.markdown(thisBatch);
        break;
    }
}

Paul_Warren · June 24, 2020, 3:07pm

Bill, this is awesome! I wish I had given more details earlier. Now I need to pull from a field called “Input Components Rollup” and deposit in a new field called “Input Components Condensed”. Would you be willing to help with that? I am not a java script wizard, but I will do my best. Thank you!

Bill.French · June 24, 2020, 3:21pm

Good to see we got the requirements nailed down. I’ll try to add that table/field selectivity to the process; slammed today so a little later perhaos.

Paul_Warren · June 25, 2020, 3:05pm

Thank you for you willingness to help Bill! I regret not putting all of the details for my application and sincerely appreciate your insight!

Bill.French · June 26, 2020, 3:52pm

Table…

Block…

Outcome…

Code…

/*

   ***********************************************************
   Airdrop - Unique Code Batching
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

output.markdown('# Contiguous Batching');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Source Field");
let sourceField = await input.fieldAsync("Pick the name of the field to be batched:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the target field name
output.markdown("### Select the Target Field");
let targetField = await input.fieldAsync("Pick the name field to be UPDATED with the batched values:", sourceTable.id);
let targetFieldName = targetField.name;

// get the records
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// get the record count
let recordCount = sourceRecords.length;

// iterate across all the records
for (var r = 0; r < sourceRecords.length; r++)
{
  
    // get the lst for this record
    var thisList = sourceRecords[r].getCellValue(sourceFieldName);

    if (thisList != null)
    {
    
        let allBatches = "";

        // create the array (sans spaces)
        let aList = thisList.toString().replace(/ /ig, "").split(",");
        aList.sort();
        output.inspect(aList);

        // create a 2d array
        let aData = [];
        for (var i in aList)
        {
            aData.push([aList[i], aList[i].toString().replace(/\./ig, "").replace(/\-/ig, "")]);
        }
        output.inspect(aData);

        // batch the sets
        let thisBatch = aData[0][0] + " => ";
        for (var i = 0; i < aData.length; i++)
        {
            thisNumber = parseInt(aData[i][1]);
            try {
                if (thisNumber + 1 == parseInt(aData[i + 1][1]))
                {
                    // we have continuity
                } else {
                    // continuity broken
                    thisBatch  += aData[i][0];
                    allBatches += thisBatch + "\n";
                    output.markdown(thisBatch);

                    thisBatch = aData[i + 1][0] + " => ";
                    // i += 1;
                }
            } catch (e) {
                // do nothing
                thisBatch  += aData[i][0];
                allBatches += thisBatch + "\n";
                output.markdown(thisBatch);
                break;
            }

        }

            await sourceTable.updateRecordAsync(sourceRecords[r].id, {
                [targetFieldName] : allBatches
            });

    }
}

Paul_Warren · June 26, 2020, 3:56pm

@Bill.French, this is fantastic work! It accomplishes 99% of what I was looking for. The only major issue is that if there is only one item in a series in the Input Components Rollup field, then it shows 1.17-01 => 1.17-01, which leads to duplicate and confusing information. For now, this still saves me considerable time and effort! I can just go back and edit the text output.

Thank you!

I think this script is an awesome candidate for the nee “Button Field” contest! A few comments I would make to potentially improve this script:

Allow a view to be selected instead of a full table.
It is amazing the flexibility you built in to select any table and any two fields! I love it!!! However, it is a pretty slow script. We have about a thousand records in one table and it takes almost a minute to run the script. Being able to select just a single view would reduce the necessary resources considerably.
Separate the groups by commas instead of new lines. The primary reason for this script is to condense the information into a smaller space and improve readability. I mostly want it to use in a nice Page designer and the new lines expand the data to take even more room than before with long discontinuous lists.
If using the button field, it could either clean up a field in the selected record or a field in all linked records in another table. I would prefer the second option. This would be even better than a view selection!!

I sincerely thank you for your volunteer time on this! I mean no disrespect in my recommendations. The script as is meets all of my most pressing needs. I just can’t help but share the ideas you helped me have! Gracias!!

Paul_Warren · June 26, 2020, 5:10pm

Actually, I found a way to do this with a simple formula:
LEFT(
SUBSTITUTE(
{Input Components Condensed},
‘\n’,
', ’
),
LEN(SUBSTITUTE(
{Input Components Condensed},
‘\n’,
', ’
)
)-2
)

Thanks!

Bill.French · June 26, 2020, 6:28pm

You’re welcome and non taken. This is the world of fluid requirements because often we don’t know what’s possible.

But it also underscores the need for detailed requirements; we all agree they are beneficial, but the time it takes to think through all of the nuances is typically where developers and domain experts lunge forward without a clear pathway and outcome in mind.

system · June 29, 2020, 6:28pm

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.