I am trying to create a doc that can contains the following information:
Product Names
Supplier Names
Current prices for each product
Historic order quantities by store
This kind of document will contain a lot of data because we have around 7000 products that we use. I am not planning on using transaction level data so that should help to reduce some of the data that is contained in the document.
I tried importing the 7000 products and creating a lookup and was pleasantly surprised at how much better Coda performance is compared to the last time I tried this.
I remember someone on the forums mentioning that one can store data as JSON in a cell. I would appreciate it if anyone knows of any limitations to this approach. In particular, I am keen to understand the following:
Should one put data in a Text or Canvas column?
What are the limitations that you know of? Size of cell?
I’ve actually implemented this many times in my doc and have one running doc that stores over 100k rows worth of information
Rather than storing that data in JSON format though, I use CSV style format and store each “row” as a single line with column level data separated by commas
I then also use button actions to unroll that information back into actual rows when needed for editing and then use another action to roll back up that data when done.
I noticed that a single text cell was able to store around 14,000 characters, but that testing was done prior to canvas columns and I haven’t fully done testing with Canvas columns and this method. Heres a video on part of the method that gives some insight!
The data is also still accessible and queryable while in CSV storage, so its pretty dang flexible and worthwhile depending on your use-case
@Scott_Collier-Weir this is ingenious! Thanks for sharing your knowledge. Am I correct in saying that this method is good for archiving/rolling up information where one might need to access statistical information?
If however, I needed to search for a row that contained a specific product and extract historical order data by store, do you think that the csv method would work or would I need to use JSON because one can query for a key?
Hey there!
