Situation: I want to have externals being able to request a set of resources (request one to many resources). Requests and Resources would have to include multiple properties/columns in a single submission.
Question: Have any of you guys found a way through an external software/integration to achieve this type of result.
Note: Fibery forms only allows creating one entity per submission. (Having a relationship view like in the entity view would be awesome)
I know that this could be in the “Features” section of the forum but the team probably won’t care much, and I believe I already read a topic like this, so it would be a duplicate.
This just makes Fibery the most powerful tool right now, as this is a situation I’ve encountered so many times with so many clients.
How does it work?: In the Fibery Form you input your tabular content as text inside a rich text field. When I say “tabular content”, it is simply any text with a column and row separator.
The headers of your input text need to match exactly the headers in your Fibery database
It is up to you to help the user create a single string that they can paste into the description field (see my GSheets Formula as an example).
But please note and in this case I was pasting an actual cell from GSheets with content inside that’s why I use the .replace(/^([|\s-]+)|([|\s-]+)$/g, “”) in the code (read the code comments).
I wasn’t able to do it with the break line symbol “\n” as row separator, not sure how the markdown really parses this
You can easily modify this code within the section “SET YOUR VARIABLES”
To create child entities you need to have configured autorelations like this:
Where “IDL1” is just a regular text field to contain the ID of its parent (see blue lines in the previous picture)
Other ideas for future improvement of this script:
It could be great to simply upload a csv, it would require to know how to handle files and its content in Fibery scripting
I utilized auto relations because I used the function “createEntityBatch”, I guess this could be done using " addCollectionItemBatch" but I don’t understand this function very well (any ideas Scripting heroes? @ChrisG )
You could set Initial Fields in Fibery Forms but it doesn’t work in rich text. You could use a regular text field but there are limitations in the text length and it would require to adjust the code.
The Script (Create Child-Entities):
const fibery = context.getService('fibery');
// ----------- SET YOUR VARIABLES -----------
// Name of Source rich text field
const namerichtextfield = "Description"
// Row separator
const rowseparator = "|&|"
// Column separator
const columnseparator = ";"
// Name of Target child database (Use notation "Space/Database")
const namechilddatabase = "Testing/Level 2 Database"
// Name of Column in Child Database that will contain the parent id (text column)
const nameIDparent = "IDL1"
// ----------- START OF OPERATIONS -----------
// Get the current entity
const entity = args.currentEntities[0]
// Get the content of the Description field
const desc1 = entity[namerichtextfield]
const desc1textraw = await fibery.getDocumentContent(desc1.secret, 'md')
// Replace any "|" or " " or "-" symbols at the beginning (cleanup)
/* (These symbols appear in markdown if you paste from excel or Gsheet, as you are pasting
an actual cell inside a rich text field, it is still safe to paste normal text) */
const desc1text = desc1textraw.replace(/^([|\s-]+)|([|\s-]+)$/g, "")
// Get the ID of the parent item
const parentid = entity['Public Id']
// Get the lines and get the headers
const lines = desc1text.split(rowseparator).map(line => line.trim())
const headers = lines[0].split(columnseparator).map(header => header.trim())
// Initialize dataobject
const data = []
for (let i = 1; i < lines.length; i++) {
// Get current row
const values = lines[i].split(columnseparator)
// Initialize target row
const linedata = {}
// Input the current row values into the target row
for (let j = 0; j < headers.length; j++){
linedata[headers[j]] = values[j].trim()
}
// Append the ID of parent item to use auto-relationship
linedata[nameIDparent] = parentid
// Update the dataobject[]
data.push(linedata)
}
// Create the entities with the final object
await fibery.createEntityBatch(namechilddatabase, data)
// Give return message
return `${data.length} Entites have been created`;
Extra:
I created another script to preview the raw text as an HTML table in another rich text field. I share it here as well if it’s useful for you:
const fibery = context.getService('fibery');
// ----------- SET YOUR VARIABLES HERE -----------
// Name of Source rich text field
const namerichtextfield = "Description"
// Row separator
const rowseparator = "|&|"
// Column separator
const columnseparator = ";"
// Name of Target child database (Use notation "Space/Database")
const namechilddatabase = "Testing/Level 2 Database"
// Name of Target rich text field
const nametargetfield = "DescriptionTable"
// ----------- START OF OPERATIONS -----------
// Get the current entity
const entity = args.currentEntities[0]
// Get the content of the source field
const desc1 = entity[namerichtextfield]
const desc1textraw = await fibery.getDocumentContent(desc1.secret, 'md')
// Replace any "|" or " " or "-" symbols at the beginning (cleanup)
/* (These symbols appear in markdown if you paste from excel or Gsheet, as you are pasting
an actual cell inside a rich text field, it is still safe to paste normal text) */
const tabularData = desc1textraw.replace(/^([|\s-]+)|([|\s-]+)$/g, "")
// Split the tabular data into rows
let rows = tabularData.split(rowseparator);
// Initialize arrays to store column headers and data
let rowData = [];
// Extract data from rows
rows.forEach(row => {
let columns = row.split(columnseparator);
rowData.push(columns);
});
// Construct the HTML table
let htmlTable = "<table>\n<tbody>\n";
// Add data rows
rowData.forEach(row => {
htmlTable += "<tr>\n";
row.forEach(column => {
htmlTable += `<td>${column}</td>\n`;
});
htmlTable += "</tr>\n";
});
htmlTable += "</tbody>\n</table>";
// Push changes into Target rich text field
const desc2 = entity[nametargetfield]
await fibery.setDocumentContent(desc2.secret, htmlTable,"html")
Wow, this is amazing! For someone that says they haven’t “messed with Fibery’s API or its scripts”, you seem to have a pretty good handle on it! Bookmarking this, as I’m sure this approach could be useful in our org. Thanks for sharing
