Combine multiple records into a single paragraph text field (and delete the original records)?

API & Customization
1

My app is a service support portal/app for techical support on equipment - Each interaction creates a comment record documenting the “transaction”. These comments are accumulating very fast and my total record count is well out of line with my alloted storage space. I would like to replace the related comment records for a case with a single comment that includes all content when a case is closed (or at a scheduled time) - a makeshift “archive” of sorts…and then delete the original comments. This will reduce my record count AND my technicians can copy the new “field” into our SAP and quickly/properly document the case details into our “offical” system. Any suggestions?

Thank you.

2

Hi @Trey, I’ve encountered the same need before in one of my client’s apps.
What I did was export all the comments to Excel, transform the data with Power Query to merge all comments with the same connected record, with a date and user stamp for each comment, and reimported the comments back into Knack, deleting all the old comment records.

This works well as a one off, however other suggestions is either:

  • Users manually prepending comments to a single paragraph text field, making sure they date/user stamp it
  • Some JavaScript that on submit of a form takes some text that a user inputs in a field and prepends it to another read-only paragraph text field, which builds up all the comments.
  • Some JavaScript that on archiving of a connected record fetches all comments, merges their comments into a new record, and deletes all the other records.

Hope one of those suggestions are useful!

image

3

We do something similar in Make where we merge 10 to 20 records into 1 rich text field. It’s a bit of a pain to set up but once you have you can Archive a lot of records fairly quickly.

4

Thanks Stephen,

There is no pressing need for this consolidation at the close of every case. I can still select all comments and copy into SAP and it seems to work nicely. Then I can use your suggested method quarterly or annually to “clean up” the data and reduce record count. Perhaps I will get assistance from a Java expert in the future, but your suggestion is brilliant.

Thank you!

1 Like
5

Interesting – I have not dipped my toes into the Make world yet, but I think it is fast approaching. Thanks for the feedback.

6

@CSWinnall yes good call! My mind always focuses on the inbuilt (JS) approach that I forgot to mention Make, Zapier, etc!

7

@StephenChapman I prefer JS too but sometimes the visual aid that comes with Make (Especially if you don’t want to code) makes it fairly easy.

I still think that Knack should be able to handle most internal data editing that Make should not be needed but it can’t.

P.s. I read all your posts, they are great I’ve learnt a lot from them.

Craig

2 Likes