- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 23, 2020 11:23 AM
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!!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 25, 2020 06:04 PM
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
});
}
}
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 23, 2020 11:52 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 23, 2020 03:45 PM
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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 23, 2020 03:59 PM
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!!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 23, 2020 05:13 PM
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;
}
}
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 08:07 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 08:21 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 25, 2020 08:05 AM
Thank you for you willingness to help Bill! I regret not putting all of the details for my application and sincerely appreciate your insight!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 25, 2020 06:04 PM
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
});
}
}
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 26, 2020 08:56 AM
@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!!