Transpose - vertical view in a Table

[Kriya]

Here is a table with the data where new records can be added:

I would like to see the data in a vertical view:

How to achieve this in SeaTable ?
I’ve tried with Data Processing but couldn’t succeed.

[cdb]

Data processing is not the right tool for such complex actions.

Please do me the favor: please describe why you want to do this, and also describe a logic how you would do this manually. Then there is a change to write a python or JavaScript to do this. For me, this looks like something super special corner case…

[Kriya]

Thank you for answering.

In the beginning i was thinking it will be easy (like in excel), but now i see without coding probably will not do.
Let me explain the background a little bit.
It is a part of a one or even more projects.
There is no possibility to submit more than one record at a time using forms (for anonymous users), but there is possibility to collect more data during one submission.
To make it easier let us concentrate on a practical aspect of collecting an orders from a customers via forms (part of inventory base - sales orders).
Customer wants to make an order where apart from Name, Address etc. he can choose several products (sometimes few but it can be several dozens) and of course quantity of each product. After 5 submissions we have:

Now this horizontal representation of the same type of data (X-Products and X-Qty) is not a natural one. We want to have a vertical view and in line with your suggestion in this post.
So, there is a need to transpose data.
We can list all not empty “X-Products” and “X-Qty” separately in a formula column (i.e. for Order-0001):
“Product 2”;“Product 7”;“Product 4”;“Product 3”
1;2;2;1
And now maybe via script to arrange data in another table in this way:

Data could be automatically transferred (via script) to the table after each new submission.
For Order-0002 we would expect adding only one row (number 5). In a similar way script would work for other orders. Finally the populated table would look like this:

I hope, this clarifies more the purpose of transposing data especially when using the forms.

[cdb]

Hey Kriya,

thanks for the explanation.

Here is what I think:
You should rethink your approach because your database structure is wrong. I understand that you want to use a SeaTable web form to create a complete order, and therefore you allow the customer to choose from various products… BUT this is the wrong way.

It does not make sense to decide for a bad database design because the web forms of SeaTable lack of the features you require, and then rearrange the data afterwards.

My advice is: your database structure has to work right from the beginning. There should be no “data manipulation afterwards”. Then, if the web forms are not sufficient, build your own web form with HTML and send the submitted data via API to SeaTable. Of course, the development of such a “custom order form” will take some hours, but you prevent so many problems that will arise due to a bad database design.

[Kriya]

Welcome back,

from the very beginning i’ve tried to search just a workaround for this problem, knowing very well that it will be not a perfect solution but still acceptable for a time being.
Unfortunately i cannot use suggested solution with HTML and API.

Rethinking the problem from the scratch leads in this case to one solution…
We will continue to use email communication for orders and manually transfer data to MS Access.
For other projects we can use Excel spreadsheets.
I’m sorry, but it becomes complicated (thus time consuming) and makes no sense for me to continue…

Apart from that i consider SeaTable as a great tool for many tasks and maybe we can use it for some.
Thank you for great support and answers. I really appreciate it.