Dates as Columns

ddc9772

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:
Rows: channels
Columns: dates
Cells: content table ID (displayed as event name lookup)

What would I do to achieve this?

Pawel Magdanski

Hi,

How channels and events whould be connected with each other? what should be the relation between those two?

ddc9772

Each piece of content corresponds to a channel and an event (top table). It is a many to many relationship.

Ray Deck

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.

ddc9772

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.

Ray Deck

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.

ddc9772

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.

Ray Deck

cc @Pawel Magdanski as an expert who could help you with this!

Pawel Magdanski

@Ray Deck Thanks for mentioning.

I would be happy to help. You can drop me a message at [email protected]