Hello everyone,
I want to create a linked table that shows columns in one table as rows in another table.
I looked at Pivot functionality, but it seems to be designed for Interfaces. (I didn't try it yet, so I could be wrong here)
What I'm looking to do should be fairly simple: check the screenshots.
Dates in Table 4 should be automatically transposed to how they look in Table 5.
In the screenshot I did it manually for demonstration purposes.
Thanks!
Jan
Transpose rows to columns (but not pivot)
+2
+22Write a formula that creates the list of records you need to create as a comma-separated list, like:
{= Date records}
{Date 1}, {Date 2}, {Date 3}
Now follow the instructions in our guide to automated junction tables to create those records with an automation.
+25I can share my script, written for that purpose
//galex,2023
//Read settings
let settings = input.config({title: 'Transpose table',
items: [input.config.table('tTable', {label: 'Select table' }),
input.config.view('tView',{label: 'Select view',parentTable:'tTable'}),
input.config.text('tName',{label:'Name of transposed table'}),
input.config.text('tWild',{label:'Wildcard for fields to transpose',description:
`Example: if you want to transpose fields "Agent 1", "Agent 3", "Agent 4", input "agent".
Case insensitive. You can put several wildcards, separated by comma.`
})]
})
const {tTable,tView,tName,tWild}=settings
//Find fields to transpose
const primary=tTable.fields[0].name
const tWilds=tWild.split(',').map(w=>w.toUpperCase())
const wildCheck=fname=>tWilds.some(w=>fname.name.toUpperCase().includes(w))
let tFields=tTable.fields.filter(wildCheck).filter(f=>f.name!=primary)
const fNames=arr=>arr.map(a=>a.name).filter(n=>n!=primary)
let chosen=fNames(tFields)
console.log(`These fields will be transposed: ${chosen.join(', ')}`)
//Ask for left side
let ask=''; let leftSide=[primary]
let restFields=fNames(tTable.fields).filter(t=>!chosen.includes(t))
restFields.unshift('All OK');
while (ask!='All OK') {
output.text('Current left side: '+leftSide.join(', '));
ask=restFields.length==1? 'All OK':await input.buttonsAsync('Choose left side fields: ', restFields)
leftSide.push(restFields.splice(restFields.indexOf(ask),1)[0]);
output.clear; output.text('==================')}
leftSide.pop() // put out All OK from fieldlist
//Read and transform data
const query=await tView.selectRecordsAsync({fields:[...leftSide,...chosen]})
output.text(`Total records: ${query.records.length} and ${chosen.length} fields to transpose`)
const leftpart=r=>leftSide.map(f=>[f,r.getCellValueAsString(f)])
const trans=r=>chosen.map(f=>({fields:Object.fromEntries([[newCol,f],
[newVal,r.getCellValueAsString(f)],...leftpart(r),[newLink,[{id:r.id}]]])}))
//Create new table
const newLink='Link_'+tTable.name;
const newCol=tWilds[0]; const newVal=newCol+'_value'
const crtField=n=>({'name':n,'type':'singleLineText'})
const newflds=[...leftSide,newCol,newVal].map(crtField)
newflds.push({name:newLink,type:'multipleRecordLinks',options:{linkedTableId:tTable.id}})
// -ignore
const newID=await base.createTableAsync(tName,newflds)
const newTab=base.getTable(newID)
//fill new table with transposed data
const crt=query.records.flatMap(trans)
const total=query.records.length*chosen.length
output.text(`Table ${newTab.name} will be fiilled with ${total} records`)
console.log(crt)
const go=await input.buttonsAsync('Press GO to start',['GO','Quit'])
if(go=='GO') while (crt.length) await newTab.createRecordsAsync(crt.splice(0,50))
output.text(go=='Quit'? 'upload skipped...':'Done')
Note it must be present in such way, forum editor engine eaten this piece:
Scripting extension linter does not like dynamic field creation for createTableAsync, so in real-life scenarios it's almost impossible to avoid its warning. Or I just miss something simple.
+2Write a formula that creates the list of records you need to create as a comma-separated list, like:
{= Date records}
{Date 1}, {Date 2}, {Date 3}
Now follow the instructions in our guide to automated junction tables to create those records with an automation.
Thanks Russell! I opened your base and tried to understand what's going on and how I can implement it in my use case.
But since I'm fairly new to Airtable, this is going a bit over my head. Would you mind elaborate a little?
Do I need to create a formula in a newly created field in the first table, and then add the formula's like in your example base?
+2I can share my script, written for that purpose
//galex,2023
//Read settings
let settings = input.config({title: 'Transpose table',
items: [input.config.table('tTable', {label: 'Select table' }),
input.config.view('tView',{label: 'Select view',parentTable:'tTable'}),
input.config.text('tName',{label:'Name of transposed table'}),
input.config.text('tWild',{label:'Wildcard for fields to transpose',description:
`Example: if you want to transpose fields "Agent 1", "Agent 3", "Agent 4", input "agent".
Case insensitive. You can put several wildcards, separated by comma.`
})]
})
const {tTable,tView,tName,tWild}=settings
//Find fields to transpose
const primary=tTable.fields[0].name
const tWilds=tWild.split(',').map(w=>w.toUpperCase())
const wildCheck=fname=>tWilds.some(w=>fname.name.toUpperCase().includes(w))
let tFields=tTable.fields.filter(wildCheck).filter(f=>f.name!=primary)
const fNames=arr=>arr.map(a=>a.name).filter(n=>n!=primary)
let chosen=fNames(tFields)
console.log(`These fields will be transposed: ${chosen.join(', ')}`)
//Ask for left side
let ask=''; let leftSide=[primary]
let restFields=fNames(tTable.fields).filter(t=>!chosen.includes(t))
restFields.unshift('All OK');
while (ask!='All OK') {
output.text('Current left side: '+leftSide.join(', '));
ask=restFields.length==1? 'All OK':await input.buttonsAsync('Choose left side fields: ', restFields)
leftSide.push(restFields.splice(restFields.indexOf(ask),1)[0]);
output.clear; output.text('==================')}
leftSide.pop() // put out All OK from fieldlist
//Read and transform data
const query=await tView.selectRecordsAsync({fields:[...leftSide,...chosen]})
output.text(`Total records: ${query.records.length} and ${chosen.length} fields to transpose`)
const leftpart=r=>leftSide.map(f=>[f,r.getCellValueAsString(f)])
const trans=r=>chosen.map(f=>({fields:Object.fromEntries([[newCol,f],
[newVal,r.getCellValueAsString(f)],...leftpart(r),[newLink,[{id:r.id}]]])}))
//Create new table
const newLink='Link_'+tTable.name;
const newCol=tWilds[0]; const newVal=newCol+'_value'
const crtField=n=>({'name':n,'type':'singleLineText'})
const newflds=[...leftSide,newCol,newVal].map(crtField)
newflds.push({name:newLink,type:'multipleRecordLinks',options:{linkedTableId:tTable.id}})
// -ignore
const newID=await base.createTableAsync(tName,newflds)
const newTab=base.getTable(newID)
//fill new table with transposed data
const crt=query.records.flatMap(trans)
const total=query.records.length*chosen.length
output.text(`Table ${newTab.name} will be fiilled with ${total} records`)
console.log(crt)
const go=await input.buttonsAsync('Press GO to start',['GO','Quit'])
if(go=='GO') while (crt.length) await newTab.createRecordsAsync(crt.splice(0,50))
output.text(go=='Quit'? 'upload skipped...':'Done')
Note it must be present in such way, forum editor engine eaten this piece:
Scripting extension linter does not like dynamic field creation for createTableAsync, so in real-life scenarios it's almost impossible to avoid its warning. Or I just miss something simple.
Thanks for sharing your work, but I don't even know how to start editing your code for my use case.
I assumed my problem had an easy built in fix in Airtable, without the need of custom code.
+25Thanks for sharing your work, but I don't even know how to start editing your code for my use case.
I assumed my problem had an easy built in fix in Airtable, without the need of custom code.
It's ready to use, nothing to edit. (except line with @ t s - i g n o r e )
When choosing left side fields, just click 'All OK' if you don't need other fields besides primary (according to your example)
+25Thanks for sharing your work, but I don't even know how to start editing your code for my use case.
I assumed my problem had an easy built in fix in Airtable, without the need of custom code.
I just remembered - Actually Yes, Airtable has the script in example scripts capable of doing the similar thing. It's input settings were not convenient for me, but the source code was extremely useful when I learned JS in Airtable and what is object/array and how to work with them to read and write data in tables.
You can try it as well, maybe my feeling about settings biased in the favor of my code 🙂
+22@JanV is this a one-time exercise, or do you need to continually repeat this with new data that lands in your initial table?
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.