Skip to main content

Automatically create roll up / summary table from view

  • August 15, 2020
  • 5 replies
  • 69 views
C
Forum|alt.badge.img+1

Hi all

I have created an Unfulfilled Daily Orders view that filters orders that need to be put into production (handmade beanies :slightly_smiling_face: ) .

Unfulfilled Daily Orders

Link

I need to AUTOMATICALLY create a table or view from this that summarizes the orders by SKU and the quantities needed so we can see what we need to produce. This table will look like this…

Production Required

I have created this Production Required table manually using a combination of linked records and roll ups and it seems to work fine.

However, I need this to be created / updated automatically when new records appear in the Unfulfilled Daily Orders view.

I was hoping someone could suggest a way this happens automatically with an automation or scripting block script.

Please help!

Thanks

Claire

5 replies

Justin_Barrett
Forum|alt.badge.img+21

What type of field is the {Line Items: Variant id} field? It looks like a link field, but the screenshot you took isn’t from a logged-in view of your table, so I can’t tell.

C
Forum|alt.badge.img+1
  • Claire_Conza
  • Author
  • Inspiring
  • August 15, 2020

What type of field is the {Line Items: Variant id} field? It looks like a link field, but the screenshot you took isn’t from a logged-in view of your table, so I can’t tell.


Hi Justin

It’s a linked record field to the products table to pull through the SKU, Inventory Qty, Ideal Stock etc.

Hope that helps!

Thanks

Claire

Justin_Barrett
Forum|alt.badge.img+21

Hi Justin

It’s a linked record field to the products table to pull through the SKU, Inventory Qty, Ideal Stock etc.

Hope that helps!

Thanks

Claire


With that link, you could probably build the view that you want on the Products table itself. Add a field that rolls up all of your order info for each product, possibly using conditions to ensure you only get orders that haven’t yet been filled. Add extra fields as needed to compare available stock with incoming orders, and only make those fields visible in that specific orders view. In short, it’s best not to add new tables that contain the same data as existing tables, and instead see how you can use the existing structure (possibly with new views, and taking advantage of existing table links) to get what you want.

On a side note, I see you have an [Orders Unfilled] table, plus a [DailyOrders] table. You might be able to consolidate those into a single table, using views to toggle between the different groups.

C
Forum|alt.badge.img+1
  • Claire_Conza
  • Author
  • Inspiring
  • August 15, 2020

With that link, you could probably build the view that you want on the Products table itself. Add a field that rolls up all of your order info for each product, possibly using conditions to ensure you only get orders that haven’t yet been filled. Add extra fields as needed to compare available stock with incoming orders, and only make those fields visible in that specific orders view. In short, it’s best not to add new tables that contain the same data as existing tables, and instead see how you can use the existing structure (possibly with new views, and taking advantage of existing table links) to get what you want.

On a side note, I see you have an [Orders Unfilled] table, plus a [DailyOrders] table. You might be able to consolidate those into a single table, using views to toggle between the different groups.


Hi Justin

I’m not sure that would work for my situation as I need to then allocate the production required to different beanie maker groups and check them into inventory when they are returned etc and this I feel would get messy with views on the production table.

Ignore the orders unfulfilled table, I was just playing around with that :-), It’s now a view as you suggest and is in the link I shared in the original post.

My ideal solution is to automatically create the Production Required table automatically if at all possible as a table not as a view.

Any ideas using scripting or automation :slightly_smiling_face: ?

Thanks

Claire

Justin_Barrett
Forum|alt.badge.img+21

Hi Justin

I’m not sure that would work for my situation as I need to then allocate the production required to different beanie maker groups and check them into inventory when they are returned etc and this I feel would get messy with views on the production table.

Ignore the orders unfulfilled table, I was just playing around with that :-), It’s now a view as you suggest and is in the link I shared in the original post.

My ideal solution is to automatically create the Production Required table automatically if at all possible as a table not as a view.

Any ideas using scripting or automation :slightly_smiling_face: ?

Thanks

Claire


Scripting and automation can only modify records. There aren’t currently options for creating tables or views via either system. Even the REST API can’t do that (as far as I know). If you already have a table, though, you could use scripting or automation solutions to add or modify records in that table, and include links to your orders in those records if that would help. If you’d like help setting up such a system, send me a message and we can discuss this more thoroughly.

Terms & ConditionsAccessibility statement