The data model
Chatbox.qs draws a conversation from one hypercube: the dimensions and measures under Data, in order. A message is a row — or, in From → To, a row for each of its recipients, folded back into one bubble. Getting those rows right is most of setting the object up.
Choose the conversation model first
Conversation → Conversation model decides what each dimension is for:
- Participants (one speaker dimension), the default: one dimension holds everyone who speaks. For group chats, or any conversation where who a message went to does not matter.
- From → To (sender and recipient dimensions): a sender and a recipient, for one-to-one conversations — an agent's chats, a direct-message export.
Choose it before adding dimensions. A new dimension takes the next role the model has free, and keeps it: switching the model later never changes the role of a dimension that is already there. As each slot is filled, the panel names its role — Dim 2 · Participant — the speaker. This is the dimension selections act on.
Participants
| Slot | Role | Notes |
|---|---|---|
| Dimension 1 | Message ID | Must be unique per message |
| Dimension 2 | Participant | The speaker. Selections act on this |
| Dimension 3 | Conversation / thread | Optional. Separates the conversations |
| Dimension 4 | To | Optional. Adds who each message went to |
| Measure 1 | Message text | Only([MsgText]) — a measure, so that long bodies never become selectable field values |
| Measure 2 | Integrity probe | Count([MsgId]) — detects merged bubbles. Count a field only the messages table has instead, when the Message ID also keys another table — see the integrity probe |
| Measure 3 and later | KPIs | Optional. Shown in each message's details |
From → To
| Slot | Role | Notes |
|---|---|---|
| Dimension 1 | Message ID | Must be unique per message |
| Dimension 2 | From | The sender. Selections act on this |
| Dimension 3 | To | The recipient, one per row |
| Dimension 4 | Conversation / thread | Optional |
| Measure 1 | Message text | Only([MsgText]) |
| Measure 2 | Integrity probe | Count a field only the messages table has, e.g. Count([MsgText]) |
| Measure 3 and later | KPIs | Optional |
- One recipient per row. A message to several people arrives as a row per recipient and is shown as one bubble listing them all. Store recipients one per row — split a stored list with
SubField()in the load script. - A message's rows must agree. They fold into one bubble only when their text and their Timestamp (numeric) are the same; rows that differ are shown as separate messages, and flagged as sharing an id.
- Spell each person identically in From and To. People are matched by their exact, case-sensitive text.
- Keep Include null values on for To. Chatbox.qs turns it on when the dimension is added. Unticking it silently drops every message without a recipient, and nothing downstream can detect that. A missing recipient is shown as (no recipient).
- Maximum messages counts rows, so a message to 20 people uses 20 of them — see Maximum messages. A data export likewise has one row per recipient.
The Message ID must be unique
A hypercube has one row per distinct combination of its dimensions' values. Two messages with the same Message ID, the same sender and the same values in the other dimensions are therefore one row, and Only([MsgText]) returns nothing for it: two messages have merged into one bubble. Make the id unique per message — a key field, RecNo() in the load script, or a hash of the message.
The integrity probe
The second measure counts the rows behind each message. Where it is more than 1, the object says so rather than showing a quietly wrong conversation:
- a banner — 3 bubble(s) combine more than one message. The Message ID dimension is not unique — separate messages are being merged.
- a dashed outline and a merged badge on the bubble, and in place of its text, 2 messages share this Message ID, so Only() returns nothing. Use a unique id, or Concat() to show them together.
The probe is optional, but without it nothing can tell a merged bubble from a real one. Count a field only the messages table has. In the Participants model that can be the Message ID itself, Count([MsgId]), as long as nothing else is linked by it. In From → To — and in any model where a recipients or keyword table is linked by the Message ID — the id is a key field, and counting a key field counts the linked table's rows: every message to several people, or with several keywords, would be reported as merged. Count [MsgText], or another field of the messages table, instead.
What else the object checks
| Banner | Means |
|---|---|
| 2 message(s) share a Message ID with a different message from the same sender. Make the id unique across conversations, not just within one. | Two different messages — different text or time — have the same id, sender and thread. They are kept apart, each marked shared id |
| 5 message(s) have no Message ID, so they cannot be selected or told apart. Every message needs an id. | Rows with a null Message ID |
| Not used by the conversation: Region. An unused dimension still splits messages into extra rows — remove it. | A dimension the model has no role for |
Every message the object can show is listed in the Reference.
Message metadata
Everything else about a message comes from expressions under Message metadata, evaluated for each message:
| Expression | For |
|---|---|
| Timestamp (numeric) | Time order, grouping messages by time, and days — e.g. Num(Min([SentAt])) |
| Timestamp (display) | The time shown with each message — e.g. Only(Time([SentAt])) |
| Avatar URL | The avatar: an https:// address, or a content library path such as /content/Default/ada.png |
| Media reference | Reserved for a future release |
| Message kind | The message's kinds, shown as chips with Show kinds as chips on |
| Own message (1/0) | Which side the message is drawn on |
| Accent colour | The colour of the bubble's rail |
| Badge text | A short label beside the time |
What each one does is under Using Chatbox.qs, and every setting with its default is in the Reference.
- Each must aggregate:
Only([Field]), not a bare field reference. A leading=, which the expression editor adds, makes no difference. - They are free. They are attribute expressions on the Message ID dimension: they add no columns and do not count against the engine's page limit.
- They take effect while the sheet is being edited. Chatbox.qs writes them onto the Message ID dimension then, with the time order, and saves them with the object. A reader opening the sheet never writes anything. Before 0.6.3, a value typed in the property panel could revert while the sheet was edited — see Troubleshooting.
Maximum messages
Behaviour → Maximum messages caps the rows read — 5000 by default, up to 50,000. Large conversations are better filtered than rendered. When the data has more rows, which are kept follows what is shown first:
- Behaviour → Message order set to Oldest first keeps the oldest rows, where the conversation starts.
- Newest first keeps the newest, and so do conversations side by side, in either order.
A banner says which — Showing the newest 5000 of 9000 messages. Filter to see the rest. — or, where messages span several rows, one per recipient, Showing 3871 messages from the newest 5000 of 9000 rows. Filter to see the rest. Where the limit falls part-way through a message's rows, its details say that some of its recipients may be missing, and its list of recipients ends with ….
Rows that are not messages
A table linked to a dimension — people, threads — adds a row for each of its values that has no message, and those rows come last in the data. When the newest rows are read and there are more rows than Maximum messages, they are found and left out first, so they never take the place of a message. Otherwise they are read and dropped, and where they used part of the limit, a banner says so: 212 loaded row(s) were not messages — a table linked to a dimension adds a row for each value that has none — and they count against the limit.
Keyword tables
A table of keywords to highlight, linked to the messages by the Message ID, makes the id a key field — count a field only the messages table has in the integrity probe, as above. How to load the keywords themselves is under Highlighting keywords.