I am seeking guidance on how to automatically consolidate records in a table when the records only differ by the values in a single column.
Here is an example of the current state; note how there are multiple rows for a single company, with only the values under 'OrderID' differing:
Here is how I ideally WANT the records to be listed, with the values for OrderID all listed in a single cell, separated by comma:
I appreciate any tips!
Hey @Smitty, a couple questions for you - Is this a one-time change you'd like to make or recurring as new data comes in? And if the latter, where is this data coming from? E.g. an external system via API or webhooks, or user input from a field on a screen, or..?
Thanks for your response, @aj. This will be a recurring need (three times daily). The data is coming into a Honeycode table via Amazon Appflow, from a csv file in an S3 bucket.
Hey @Smitty, I've been trying a variety of table automations without much success. One question I do have though is related to how this current table design will scale. Specifically, might each customer have a growing list of orders in OrderID, and if so, will those just continue to be inserted into just one cell (per compnay)?
One alternative I have been thinking about is a separate table ("Orders") where each row would be an order ID (first column) and then the company ID (second column) would be a rowlink linked back to the Companies table. Something like this...
Thoughts?
Yes, @aj , for some companies the Order IDs will grow in number, and each time I would want the Order ID to be inserted into that one cell.
I'm not opposed at all to a solution that involves some combination of additional tables with various functions leading to the result of one table that contains the data as I've specified.
Your proposed approach may be promising, but I'm not completely clear yet on how it accomplished what I need to do.
Hi @Smitty, been chatting with the PM team on this. Today we do not support the ability to automatically consolidate records in a single cell, or one-to-many as we refer to it. This type of need has been requested before and therefore is something our team is aware of. I'll log this as another case for the feature request.
For now the best option would be to keep the companies and respective orders in separate tables. I know it's not exactly what you need, but it is an alternative way to correlate the two data sets.
