I need to be able to create a set of tables that dynamically change depending on a value in a linked Object Set. If required I can make a new Object Set to remove the link, then it would be on a value within the data, but would prefer to keep separate if possible…
For example - I have sales data, and location data. The people can change, the locations can change. I want to be able to show the current data in a summary document that can be exported, split by location. My customer is very clear that they don’t want to have the location listed as a column in the table (as it would if it did a pivot), they want each location split out automagically, and that they can arrange the order that those locations are listed in.
Week one:
| Person |
Sales |
| Bob |
150 |
| Wendy |
200 |
| Jane |
65 |
| Steve |
420 |
| Person |
Location |
| Bob |
Canada |
| Wendy |
Canada |
| Jane |
Antarctica |
| Steve |
Chile |
Week 2:
| Person |
Sales |
| Bob |
150 |
| Wendy |
200 |
| Jane |
65 |
| Louis |
40 |
| Person |
Location |
| Bob |
Maldives |
| Wendy |
Canada |
| Jane |
Chile |
| Steve |
Chile |
Outputs:
Week 1:
Chile
Canada
| Person |
Sales |
| Bob |
150 |
| Wendy |
200 |
Antarctica
| Person |
Sales |
| Wendy |
200 |
| Jane |
65 |
Week 2:
Maldives
Canada
Chile
| Person |
Sales |
| Jane |
65 |
| Louis |
420 |
How do I go about doing this?! Bonus points if there is a way that a user can easily add comments to each row of the output tables.
Hey @HashOutput,
I’ve got a few clarifying questions, but in general I don’t think this workflow can be accomplished without Ontology changes and multiple resources in play.
- Can you clarify what the changing value in a linked Object Set refers to? Are you just saying that the Sales and Location object types dynamically receive new data and the output needs to reflect that?
- Where is the
week information stored? Is this a property on both Sales and Location object types?
- What is the expected workflow here? It sounds like an admin users wants a document displaying sales per week by location and people. Is this document expected to contain all weeks of data, or a sub-selection?
- Does this workflow have to live in a Notepad or Quiver? Can this instead be a Workshop module?
Best,
Schadt
Hey, not surprised on the clarifying questions, I feel like I get a dozen each time I look at things myself!
- So the Sales table contains about 85k rows of constantly changing and updating data. It is linked via a key (the Person) to the Location which is held in a different system, and therefore a different object type. All Sales and Location updates daily, or more.
- The week information is more of an example of how the exports would change from one to the next, sorry if that is confusing
- The document should show the most recent information (only a small subset of the available columns) of around 1000 rows from the Sales table, along with all user added comments. These need to be split by location, and then the locations split by person, with the sales details inside that. The location rapidly changes, and the report has to be printed (
) and organised depending on the whims of The Powers That Be [TM].
- Because of the requirement of printing, as far as I’m aware it will have to be a notepad that has things embedded in it - exporting to Excel won’t do the job. Happy to be proven wrong.
Cheers!
you could try to embed everything in an object view and hide sections/tables depending on parameters. this could be done in workshop if what you need os in the ontology, or in a slate app where you have full control over the tavle styling and table layout. we had a usecase for a onepager print out which eventually was styled to the correct dimensions using css and then printed via the browse…
Hey @HashOutput,
Thanks for the clarifications!
I’m unable to think of a quick solution that checks off all of your boxes.
You could create a Notepad template, that takes a Location saved object set filtered down to locations that have had sales within the last week (exact logic to be determined by your use case). Create a template Section widget containing the location name and a Quiver dashboard which both take the Location object as an input (template object). The Quiver dashboard will search around to Sales objects (filtered to sales in the last week) and renders a resulting object set table or transform table.
The Location saved object set would filter down to locations that have had sales within the last week (exact logic to be determined by your use case).
Some things to consider with this workflow:
- Comments can’t be added per sale/row (at least not nicely) but rather per section. This is because in this example we are using a Quiver dashboard to show the Sales objects which doesn’t allow editing.
- When exporting the data, the user must ensure they’ve fully expanded the Quiver dashboards or the tables will be cutoff in the resulting PDF (i.e. the Quiver tables are scrollable if the Dashboard widget hasn’t been allocated enough height for all of the rows).
- It would be important to keep the recent filter in the saved Location object set and the Quiver dashboard consistent (i.e. above they are both set to 1 week).
For example, this would look something like this if instead I were looking at Machines in each Location:
- Note how the first table size has been reduced such that printing it would result in partial results.
- Note how comments would be added to a column next to the table instead of an individual sale.
As @Flackermann mentioned, Slate could likely be used to construct something like what you are looking for but is a bit more technically involved; well beyond the scope of a single help thread!
Another option would be to not group sales by location (
) and add a location property to the Sales object type, then create a templated Notepad where the input is all recent Sales.
Best,
zschadt
1 Like
