Looking for some help from those smarter than myself.
I have multiple start and end date columns for 4 different activity types. I want to show these activities within a calendar but am having difficulty in doing so. From what i have read, i need to have the current 4 start and 4 end columns consolidated into one start and end?
I’m handy with Excel and Power Query so was trying to look for a pivot/unpivot columns functionality - unsure if this is the right approach within Glide?
Ideally i would have a solution that keeps everything in the same table, but happy to create a new/helper table if needed.
How are data inserted? From a form in Glide? If it does, you could do a onSubmit action that insert the the id in a new table. In that new table, you could add computed column to create the relation and lookup/single value for related data you need in the first table.
Then, you want to create a second table with the column you want to get from the first table:
Name, description, based on the related row id used to make the relation between the 2 tables and create the lookups you need:
Then, you want to create a onSubmit action on the form. You will set an Add row action to insert the row id of the first table after submission into the related row id column of the new table:
Then, to test it, submit something. It will insert a row in the empty table, then it will insert a row in the new table with one value in the related row id (the new inserted row from empty table).
So, the lookups will grabe the data from the first table based on the related row id:
appreciate the detailed response but im not sure this will work in my scenario.
for each project (ie row) in my main table there are 4 activities/sub tasks (1. Consult 2. Stage 3. Touch up 4. Pick Up). Each of these activities MAY have dates. In your solution, i believe i would need to add 4 new rows into the new table for each of these activities. Is that right? How would i do this?
These dates can be amended/updated. Would these updates flow through to the new table?
My advice would be to normalise your data structure. Get rid of all those date columns in your Projects table, and create a separate link table - let’s call that Project Milestones.
In that table, add the following columns:
ProjectID
Milestone (name or ID)
Start Date
End Date
With that structure it is easy, you can use that table as the source of your Calendar, and no transformation or other computed column gymnastics is needed.
