How to create a temporary template table

[Steve_Chambers]

Hi all,

I have been trying to create a function whereby I give my user the ability to view a pivoted table.

Obviously this is not supported within appsheet so my idea is to create a workaround that uses a spreadsheet that has the data structured into a transposed manner, such as below

and then setup a series of actions/workflows to use this table as a template that the user activates from a row whilst in an Order Details view.

So for my particular app i have a parent child relationship of Order/Order Details, and i would like to marry this pivot table, lets call it - CostingSheet, to the Child (Order Details) so that the user can easily fill out fields in a quick edit table view such as below

So im guessing in order to enter the quick edit table view, i would need to create a grouped action that has:

1. Link to filtered view
2. Set the values of some columns in this row

The second action would populate a column in the CostingSheet table with the OrderDetails key, such as in red

Thereby allowing a form save action on the quick edit table to trigger a grouped action that:

1. Copies the entered values across to their corresponding OrderDetails table
2. Clears the fields

Is this idea worth pursuing or does one of the more advanced users here have a different solution to edit pivoted data?

[1minManager]

Whilst I’m not clear on your data structure here, it sounds doable. I would guess you create a slice of OrderDetails, which then displays in a View. If you then group that view by [Entity]? then that should pivot it.

If I’ve got this completely wrong then reply with details of both tables inc which columns you need to see and which are Key column and I’ll try to help

[Steve_Chambers]

Hi Simon, thanks for helping out!

I can’t pivot the entity with out setting it up manually in a new sheet, i think, unless that’ what you mean, my Order Details structure is like so

The yellow and orange columns i am looking to give the user pivoted, editable functionality for so they can fill the fields in like so;

So my idea is to create a CostingSheet table that has the column headers from my Order Details table structured like so

Blue fields are editable, and the elephant in the room is how i will create an action to copy those blue fields back to where they need to reside, in the Order Details table

btw i love your website i spend an hour lat week reading through your blogs!

edit: so just to clarify, one CostingSheet table is equivalent to one row in the OrderDetails table

[1minManager]

1 = Create a Table view which looks at the OrderDetail sheet
2 = Set to group by Entity
3 = Order by Description
4 = Turn on “Enable QuickEdit (beta)”
5 = By defrault this will allow users to edit any column they’d normally be able to edit in form. To restrict this you might need to create a CONTEXT(“View”)=“View Name” formula

Let me know if we are on the right track here

[Steve_Chambers]

The problem is there is no column named “entity” in the Order Details sheet, i added that column in the concept CostingSheet i created to explore this idea, the Order Details sheet has about 9 columns with the Entity metrics (Button description, Collar description, ect) and then 9 columns with the entity values (Button Cost, Collar cost, ect)

I could rename each of the 9 metrics to “Entity” but appsheet wont allow columns with the same names so i’m not sure im following you

See my order details sheet here

Each row in the Order Details sheet represents one garment, i am trying to pivot the orange column into 1 column (entity), and pivot the yellow columns into 1 column (Cost)

I dont think its possible to pivot data, i mean i know its not possible to pivot data as its not supported, unless i use a work-around, the one im exploring now is making progress.

Another idea would be to split off all the cost/entity types as grandchildren and then i could conceivably aggregate all those children into one table where i could then group by the same column name, and then use separate display names or something of that sort

[1minManager]

Are you wanting to see this pivoted table from the parent Order? record or in the OrderDetails record?

[Steve_Chambers]

just the order details record is fine

[1minManager · Accepted solution]

Then your best bet is to create those description and cost items as a subtable of Order Details. You could create a complicated set of Actions that could create/mod/delete them as you changed the main Order Details but thats a seperate discussion.

The only other option available to you would be to output the data in to a PDF report. With those you have some scope to lay it out how you want.

[Steve_Chambers]

Thanks Simon, i can see now my entities in question should all be children of the OrderDetails record.

appreciate the guidance