Inserting multiple data into one column

i have a collection of activities that a user can select.(activities table).
i want to get each selection timestamp.
i am doing it now by adding a new record to a different table (activities timestamp table).
but due to row limitations in glide. i want to have a different approach
i want to store each timestamp into the same table 1 :activities table.
in the same row as the activity itself
so each row will have column 1: activity name | column 2 : timestamps.
and each time i record this activity a new timestamp will be added to that specific row.
how to achieve this?
this will significantly decrease my row usage in glide. i don’t want to add a new row for each selection.
i want to use these timestamps later to display the activities in descending orders. and to limit the number of activities a user can enter daily.

Do you want the last modified timestamp only or do you want a comma separated list of all timestamps for that row?

1 Like

i want a list of all timestamps for that specific row.
kind of like this:

this is a list of timestamps (each record is a timestamp for this activity)
but i don’t want to take up 1 row for each timestamp
i want a way to save them in the same row this activity is :


and in a way that i can later do sortings based on the new column

If you want to store that in a single row, then the best approach is to use a JSON structure.

[
  {
    "name": "xxxx",
    "category": "social",
    "timestamp": "July 6....."
  },
  {
    "name": "xxxx",
    "category": "social",
    "timestamp": "July 6....."
  },
  {
    "name": "xxxx",
    "category": "social",
    "timestamp": "July 6....."
  }
]

It’s a bit tricky to setup, and requires the use of JavaScript to add/remove items, but it is quite an effective technique. Whenever you need to display the data from a single row as a list/collection, you just need a Helper Table with enough rows to cover the maximum number of expected items.

3 Likes

I’ve always wondered if the Trebuchet method ( 💫 The Trebuchet Method - @Robert_Petitto) would be applicable to ‘slinging’ dates into a timestamp date-list column (e.g. timestamps of access).

Inquirying minds…

1 Like

Trebuchet can work here for sure. The JSON method that @Darren_Murphy is mentioning is an advanced version of the Trebuchet, and is useful when needing to store multiple points of data in the same cell.

3 Likes

oh, I misunderstood the requirement. I assumed he needs to store all 3 values. If it’s just the date, then yeah Trebuchet is the correct choice.

1 Like

i got it, i cannot belive it works,
i watched the video multiple times and i did some iterations to work :
this is my setup:


1- a text field (empty) called timestamps
2- a date -time field to house the current date-time
3- make array column to join columns 1 & 2 together
i don’t know why it shows the first two values as 1 value here, but it got fixed in the next column where we separate the values
4- a joined list separated by ; from the make array column.
and in the actions i have 2 actions:
1- to set the current time stamp (column 2) to the current time stamp
2- the next action is to override timestamps( column 1 with column 4 the joined list.).
ONE ISSUE tho, how can i use the timestamps column (column 1) to display the data in a calendar view??? don’t tell me i can’t.
i tried it but it only shows the first one:

any workarounds?
thanks all for your help, amazing community

Are you sure it got fixed? I’m skeptical about that.
The Make Array column as you have it will take the entire contents of your Timestamps column and treat that as a single array element. And then add the current timestamp as a second element. So no matter how large your list of Timestamps is, you will always get a two element array.

What you need to do to fix that is first use a Split Text column to coerce your Joined List of Timestamps into an array, and then use that with the Make Array column.

A word of caution: this solution is likely to break for some of your users, depending on their device/browser regional settings. This is because the way a timestamp is formatted will not be the same for all users. To make it more robust you would need to first coerce the timestamps into either a string or integer, save them in that format, and then coerce them back to timestamps when you need to use them.

You can use the “Miracle Method” to do that.

Oh, one more comment. I notice that you have a User Specific Column for your Timestamps. Is it really your intention that no user will be able see to any other users data?

1 Like

thanks @Darren_Murphy for your quick reply
i fixed it :slight_smile:


i added an extra column (splitted timestamps) as you can see
but i had to change the column type of timestamps to text) because i cannot split dates as i experimented.
i noticed a type of Bug tho :
if timestamps is a date type: it shows only one date although it has 3 timestamps now :
image
i tried copy and paste and it pasted this only
BUT if i click on the record itself it shows all three dates:
image
very weird. how can a cell hold 2 different inputs at the same time?
regarding this:

A word of caution: this solution is likely to break for some of your users, depending on their device/browser regional settings. This is because the way a timestamp is formatted will not be the same for all users. To make it more robust you would need to first coerce the timestamps into either a string or integer, save them in that format, and then coerce them back to timestamps when you need to use them.

i will try to update this, but idk i use timestamps in my whole app. but i will search how to do that.
and will see this miracle method if it can fix my calendar sorting and will update here

I notice that you have a User Specific Column for your Timestamps. Is it really your intention that no user will be able see to any other users data?

yes its intentional, i am building a customer facing freemium app that will post about it in the showcase section for you all to try it. i have been building it for quite some time now. and these are the final touches.

Yes, I think that’s expected. If you make it a datetime column, then Glide will do its best to convert whatever you enter into a single datetime value. Have a browse through the below thread to learn more about that:

For your use case, it needs to be a text column.

What I would do is convert it to an integer using a math column, and store that in the array. Then use the Text to Date plugin to convert it back to a timestamp when it needs to be used. For the first step - assuming that you want to include minutes & seconds - then you could use the following formula:

Year(Now) * 10000000000
+ Month(Now) * 100000000
+ Day(Now) * 1000000
+ Hour(Now) * 10000
+ Minute(Now) * 100
+ Second(Now)

Then to convert that back later, use the Text to Date column with the following pattern:

yyyyMMddhhmmss

Here is how that would look:

1 Like

iam still messing around with the math columns,

but i do have a BIG Question:
in my app i have two tables of data, Example:

1-Activities: 200 rows available to all users (no row owners here). where each user can record a timestamp of specific activity from the list(this can only be seen by the user himself, no one can see other’s timestamps.

2-Habits: each user can create his own habit and track it and it has row owners for each habit.(each habit belongs to one user only and can be viewed only by him.

now my question is about the ROW Limits:
is the row limit on any plan (lets say 5000 in the starter plan) includes the habits entered by other users even if user 1 cannot see or interact with habits from user 2 ? and what happens if i exceeded the limit?
PLease i need a specific answer . this will determine a lot of things regarding the plan i amgoing to purchase. thanks

The simple answer to this is that every row in every table that is referenced anywhere in your App counts towards your row limits. Neither Row Owners or User Specific Columns make any difference to this.

I have a question for you, because I’m still not convinced that you’re using User Specific Columns correctly…

Are those 200 rows one row per user, or are there 200 different activities?
If you have one row per user, and you’re using User Specific columns, then you can reduce the size of that table to just a single row.

2 Likes

No, for the activities table there are 200 activities available to all users.
Meaning all users have access to the same list with 200 activities, each activity have a name, image, points, description and many more columns.
But each user can create a custom activity and add it to this list. But each user will see his own custom activities. So there are some row owners here for the custom activities.
To give you a context of what i am creating although its still early in the meantime. I am creating a life balanceing and havit tracking app
Where the users can record whatever they have done today ( played a game , went to work, read abook …etc) and each record will reward the user points. And then later he can view the recorded activities using the main concept of my app ( the circle of life) were the userwill see his life as a circle (pie chart) and will try to balance xp points
This pie chart will include the categories for the activities were there are 6 categories( social- health -learn) and these 6 categories include all of the 200 activities list.
In addition users can create habits, record daily moods as well.
Thats why iam asking about row limits. If a timestamp added to an activity by user 1 , then user 2 should not see what user 1 recorded.
And if it is not seen by user 2 . Then it should not count towards the row limit because glide is not scanning and displaying the data from user 1 to user 2.
Thats how i understood from reading more than 10 posts about row limits today😅
Iam really excited about this project. Hopefully you all too😅

1 Like

Okay, got it.

Yes, those 200 rows will only ever be 200 rows, even if you have 1 million users writing data into each row.

Whether or not your Glide App will be capable of supporting 1 million users is a another story, but the example serves to demonstrate the point :wink:

1 Like

yea i understood your point, but the issue now I am not able to sort data based on the newly created timestamp
i ran into these 2 issue:
1- i couldn’t get each timestamp to display into a calendar by itself. (i think the miracle method don’t apply to my situation.
2- i couldn’t sort a list based on these timestamps:


because i recorded " met a friend " previously, its position stayed here although i rerecorded it again. weird after “spending time in nature” activity.
i don’t know man, things got complicated and i got lost :frowning:

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.