I have a table called “Reports”, and in it I have a Text field called “Dates”. This text field contains a comma-separated list of dates (e.g. “01/02/2024,05/07/2024,03/03/2024”).
I’d like the user to be able to view report data based on date filtering (i.e. “show me all reports from January”). Right now this is impossible because the “Dates” field is a simple text field.
I thought of the following solution - create a new table with an actual date field. Then, for each record that is created in the Reports table, parse the “Dates” field (maybe via pipe/API?), and create a new record in the new table, for each date in the field (i.e. the above example would be split into 3 separate records, each with one of the comma-separated dates).
So this:
“report_a”,“data_a”,“01/02/2024,05/07/2024,03/03/2024” (text field)
Would become (in new table):
“report_a”,“data_a”,01/02/2024 (date field)
“report_a”,“data_a”,05/07/2024 (date field)
“report_a”,“data_a”,03/03/2024 (date field)
So I guess what I’m asking - is this the best solution? If so, how would I go about actually doing it (which pipes would help me parse the field and create new records based on the parsed data)? Is there a better/simpler solution?
Any suggestion would be appreciated.
Thanks!