Create a dynamically changing set of tables depending on changing linked values

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

Person Sales
Steve 420

Canada

Person Sales
Bob 150
Wendy 200

Antarctica

Person Sales
Wendy 200
Jane 65

Week 2:

Maldives

Person Sales
Bob 150

Canada

Person Sales
Wendy 200

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.

  1. 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?
  2. Where is the week information stored? Is this a property on both Sales and Location object types?
  3. 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?
  4. 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!

  1. 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.
  2. The week information is more of an example of how the exports would change from one to the next, sorry if that is confusing
  3. 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 (:face_vomiting:) and organised depending on the whims of The Powers That Be [TM].
  4. 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 (:confused:) 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