Looking for Best Practice Excel-like Form with N:M DB-Table

[Blama]

Hi All,

I'm looking for a Best Practice for this task:

- Planning multiple persons on a per-Day basis:

Imagine three tables:
- a table person (id [PK], name)
- a table calendar (id [PK], day)
- a table planning (id [PK], person_id [FK], calendar_id [FK], intvalue).

I'd like to build a Form with the dates (days of current month) as Columns+one person column in the front.
The different persons are shown as rows (a row is only shown if at least one entry for that person exists in the n:m table in that timespan).
It should have an "add row" button and execute the CRUD operations on the n:m table as I edit the rows.

I'd think that this is a pretty interesting use case, but couldn't find anything like this in the showcases.
Of course you could use the Databound Calendar for this, but this is not what I'm looking for. The form should look like an Excel sheet.

Thank you for your suggestions,
Blama

[Blama]

Hi Isomorphic,

I just saw that you pointed someone with a M:N question to this showcase example:

http://www.smartclient.com/smartgwtee/showcase/#row_drag_save

but that's not what I'm looking for.

Thanks
Blama

[Isomorphic]

In rough outline this is just a grid connected to a DataSource that does a join as part of it's fetch operation, and has customized it's update operation via DMI logic to update more than one table. If you want it built for you, we do offer commercial services, otherwise you should actually start on writing it and ask questions about specific issues you encounter on the way, if any.

[Blama]

Hi Isomorphic,

thanks for the fast answer.
At first I thought "stupid me, it's really that easy". But at home I noticed that that the fact table stores one (short, only IDs and one value) row per DAY and person.
The Grid has to display one row per MONTH and person. So at some level, the multiple rows (e.g. 31 for a fully planned person in August) have to be transformed into one row with many columns.
I could do this with Views with multiple Decode statements at database level, but this is definitely a workaround and leads to other problems.

How would you handle that task on SmartGWT level? Again, only short directions needed. I'll definitely do that on my own as I want to learn it from scratch. Only problem is that I don't have much time aside normal daywork.

Thanks,
Blama

[Isomorphic]

Typically this is done via SQL templating, such as in this sample, which does a similar type of aggregation. No need for DECODE or anything like that, it's done for you.