Dates as Columns
Hi, what is the best approach in Xano for this? I have 3 tables: a channel table, an event table, and a table where events, channels and dates are combined.
On my frontend (WeWeb), I want to display a grid that has:
Cells: content table ID (displayed as event name lookup)
What would I do to achieve this?
How channels and events whould be connected with each other? what should be the relation between those two?
Each piece of content corresponds to a channel and an event (top table). It is a many to many relationship.
How long are your channel and event lists? If they are reasonably short, you might be better off storing them as simple strings and looking them up rather than table references you are joining through the database.
Channel list is short. Event list is really long. They need to be separate because of other uses of the data in the application.
My main question is how to use a date as a column header, since they are values, not fields, when stored initially.
You're talking about a cross-tabulation, or what Excel calls a pivot table. Schematically, you would:
First iterate over your result set from a query to find out what your possible unique columns are (e.g. dates) and build them out as an array of likely strings.
Then you would iterate again over your records to find the unique channels, which again become an array made of channel data plus the objects of the dates above. Now you have your "empty" data structure - dates by channel.
Then you iterate over the data a third time filling in the "cells" in the array of objects with the event that goes with the intersection of that date and channel.
It's not super-simple, but its only work if you break it down into those three steps!
Breaking down hard problems into their constituent elements is a common device we use in tackling the hardest 5% of problems in the State Change Pro daily office hours and discussions to unstick people.
Thank you, Ray. The only place I had found "pivot" in the community or the documentation was in one brief reference. https://docs.xano.com/database/database-relationships Thanks for pointing me in the right direction. I may need to hire someone to write the crosstab.
cc @Pawel Magdanski as an expert who could help you with this!1 · Share on Twitter
@Ray Deck Thanks for mentioning.
I would be happy to help. You can drop me a message at [email protected]1 · Share on Twitter
- 2.1K All Categories
- 43 📣 Announcements
- 32 🦾 Releases
- 30 👋 Welcome
- 552 🤷 Help! I'm a Noob
- 39 📱 No-Code Front-Ends
- 419 🌐 Working with APIs
- 278 🔢 Transforming data
- 100 🔌 Connect Xano to ...
- 13 🧑💻 Find an Expert
- 239 ❓Other questions
- 9 🔐 Security
- 13 ✂️ Snippets
- 16 🏆 Showcase
- 3 🗣️ Xano Chatter
- 49 📺 Video Tutorials
- 156 🙏 Request a feature
- 156 🐞 Report a Bug
- 17 🚀 Templates & Extensions
- 💬 Feedback