Best practice: Pivoting data to edit many DB-rows in one DynamicForm or ListGrid-row

[Blama]

Hi Isomorphic,

how would you solve the following usecase best?
I want to import data with the BatchUploader. As the user export will have different columns by default than what I am expecting, I need a mapping csvColumnName -> dsFieldName.
You already have that in .ds.xml with uploadFieldName. In order to have this dynamic I'm using a DynamicDSGenerator that replaces the entries for uploadFieldName on the fly.

My table for the mapping looks like this (excerpt):

        <field foreignKey="T_SETTINGS_LEADIMPORT.ID" [B]name="SETTINGS_LEADIMPORT_ID"[/B] type="integer" required="true" />
        <field [B]name="DSXMLFIELDNAME"[/B] length="30" type="text" escapeHTML="true" [B]canEdit="false"[/B] required="true">
            <title><fmt:message key="fieldNameApplication" /></title>
        </field>
        <field [B]name="CSVFIELDNAME"[/B] length="30" type="text" escapeHTML="true">
            <title><fmt:message key="fieldNameCSVFile" /></title>
            <validators>
                <validator serverOnly="true" type="isUnique" [B]criteriaFields="SETTINGS_LEADIMPORT_ID"[/B]></validator>
            </validators>
        </field>

I know the number of rows for each SETTINGS_LEADIMPORT_ID in advance, it's 20. Users have to edit the CSVFIELDNAME for these 20 rows.
As you can see, I have many rows for what should be "one logical record" when maintaining the mapping.

Now the question:
How would you display the data best in a ListGrid or DynamicForm? There is some pivoting needed.
Currently I can think of two possibilities:

• Pivoting in an Oracle View, insteadOf-trigger on the view that distributes the values to the rows.
• 20 DynamicForms, each with just one TextItem (for every DSXMLFIELDNAME one DynamicForm), load and save as Queue

I'm pretty sure there is a more elegant (pivoting?) solution with standard SmartGWT mechanisms, I did not use so far.
Could you please advise?

Thank you & Best regards
Blama

[Isomorphic]

We're not really following this. You started by discussing BatchUploader, but we're not sure if the interface you're now talking about is supposed to be embedded in the BatchUploader, replaces the BatchUploader as an alternate way of doing imports, or is modifying data that the BatchUploader uses during import..

Any which way, this is an application issue - we need to set an expectation that we're unlikely to chime in here for a couple of weeks (release going on).

[Blama]

Hi Isomorphic,

BatchUploader not important here, it's just to explain why I have this table where I want to edit one "logical record" that is saved in many DB-table rows.

Yes it's an application issue and I understand your problem here. If you could just give pointers in the right direction anyway, this would really be appreciated.

Best regards
Blama

[claudiobosticco]

Hi Blama, what about a grid with autoSaveEdits:false ?

[Blama]

Hi,

I don't understand. How would that transpose the rows to columns?

Best regards
Blama

[claudiobosticco]

maybe I need an example with some data...but I understood that the user has to edit many records but in a single transaction, and a grid like that in the #massUpdate sample could do it.
Then, when saving this data, do you have to transform those records in a single record? Maybe some Guava APIs in a DMI could do it?

[Blama]

I'll nicely format sample data when I'm at my PC (mobile now). I think I understand what you are suggesting.
My question is how to best display the data as one record already at the client side.

Best regards
Blama

[Blama]

Hi claudiobosticco,

my data is in this format in the database table:

╔════════════════════════╦═══════════════════════════╦══════════════╗
║ SETTINGS_LEADIMPORT_ID ║      DSXMLFIELDNAME       ║ CSVFIELDNAME ║
╠════════════════════════╬═══════════════════════════╬══════════════╣
║                      1 ║ CREATORCOMMENT            ║ Comment      ║
║                      1 ║ CUST_ADDRESS_ADDRESSLINE1 ║ Add 1        ║
║                      1 ║ CUST_ADDRESS_CITY         ║ City         ║
║                      1 ║ CUST_ADDRESS_COUNTRY_ID   ║ Country      ║
║                      1 ║ CUST_ADDRESS_STREET       ║ Street       ║
║                      1 ║ CUST_ADDRESS_ZIPCODE      ║ ZIP          ║
║                      2 ║ CREATORCOMMENT            ║ Com.         ║
║                      2 ║ CUST_ADDRESS_ADDRESSLINE1 ║ Add1         ║
║                      2 ║ CUST_ADDRESS_CITY         ║ Cit.         ║
║                      2 ║ CUST_ADDRESS_COUNTRY_ID   ║ Cou.         ║
║                      2 ║ CUST_ADDRESS_STREET       ║ Str.         ║
║                      2 ║ CUST_ADDRESS_ZIPCODE      ║ ZIP          ║
╚════════════════════════╩═══════════════════════════╩══════════════╝

I need to edit it as one record, with CREATORCOMMENT, CUST_ADDRESS_ADDRESSLINE1 etc as column names (=.ds.xml fields) and "Comment", "Add 1", "City" etc as data of record one or "Com.", "Add1", Cit." etc. as data of record 2.

Best regards
Blama

[Blama]

Hi all,

for now I solved it with a PIVOT-view and an INSTEAD OF-trigger on the view.
I'm pretty sure though that this is not the best solution as the trigger needs this code *per field* (here shown for 'PRODUCTS'):

  IF(UPDATING('PRODUCTS') AND utils.valuehaschanged(:new.products, :old.products))
  THEN
    MERGE INTO t_settings_import_field sif USING
      (SELECT   :New.tenant_id,
          :New.settings_import_id,
          :New.products,
          :New.created_by,
          :New.created_at,
          :New.modified_by,
          :New.modified_at
           FROM DUAL
      ) ON(sif.tenant_id           = :New.tenant_id
        AND sif.settings_import_id = :New.settings_import_id
        AND sif.dsxmlfieldname     = 'PRODUCTS')
    WHEN MATCHED THEN
      UPDATE
        SET modified_by   = :New.modified_by,
            modified_at   = :New.modified_at,
            csvfieldname  = :New.products
    WHEN NOT MATCHED THEN
      INSERT
      (
        created_by,
        created_at,
        modified_by,
        modified_at,
        tenant_id,
        settings_import_id,
        dsxmlfieldname,
        csvfieldname
      )
      VALUES
      (
        :New.modified_by, /* correct */
        :New.modified_at, /* correct */
        :New.modified_by,
        :New.modified_at,
        :New.tenant_id,
        :new.settings_import_id,
        'PRODUCTS',
        :New.products
      );
  END IF;

This is so much boilerplate PL/SQL that I wrote a trigger-content generator for it that takes my field-list and outputs the IF-block per field.

If there is a cleaner SmartGWT-solution I'd be happy to use it.

Best regards
Blama

[Blama]

Hi all,

I'm just realizing that I can do all the trigger stuff from #9 in an update-DMI with much less code and I will additionally most likely need no adjustments when I add additional fields.
This seems to be the better solution (will try tomorrow). Only remaining I don't like is the PIVOT-view itself.

Best regards
Blama

[Blama]

FYI, the PIVOT-view for the data in post#8 looks like this:

╔════════════════════════╦════════════════╦═════════════════════╦═══════════════════════════╦══════════════════════╦═══════════════════╦═════════════════════════╗
║ SETTINGS_LEADIMPORT_ID ║ CREATORCOMMENT ║ CUST_ADDRESS_STREET ║ CUST_ADDRESS_ADDRESSLINE1 ║ CUST_ADDRESS_ZIPCODE ║ CUST_ADDRESS_CITY ║ CUST_ADDRESS_COUNTRY_ID ║
╠════════════════════════╬════════════════╬═════════════════════╬═══════════════════════════╬══════════════════════╬═══════════════════╬═════════════════════════╣
║                      1 ║ Comment        ║ Street              ║ Add 1                     ║ ZIP                  ║ City              ║ Country                 ║
║                      2 ║ Com.           ║ Str.                ║ Add1                      ║ ZIP                  ║ Cit.              ║ Cou.                    ║
╚════════════════════════╩════════════════╩═════════════════════╩═══════════════════════════╩══════════════════════╩═══════════════════╩═════════════════════════╝

[Blama]

Hi Isomorphic,

what is your best practice for this usecase?

It consists out of two problems:

1. Pivoting data
2. Editing the data

For now I solved it with

1. PIVOT-view in Oracle
2. Update-DMI that takes the attribute-changes from the incoming request and distributes the to the base rows of the narrow data table.

Right now I'm not completely sure the PIVOT-view in 1) is the best solution as it requires database changes every time the column-list changes, so I'm interested in your suggestion (not urgent).

Best regards
Blama

[Isomorphic]

Sorry, again this isn't really an area where we have a single "best practice". There are a lot of different ways to solve the problem and the best solution depends on the application.

[Blama]

Ok, thanks. Let me rephrase: Is there a way to pivot the data in SmartGWT? Can you give high-level pointers to possible solutions?
I'm happy to choose the best solution for me, but would like to know from what I can choose.

I already did the unpivot, which is easier IMHO. Would pivoting be done with a DynamicDSGenerator? Other possibilities?

Thank you,
Blama

[Isomorphic]

Sorry, you're just rephrasing the same question. Again, we're declining to dive into this particular application problem and give you a sketch of several possible solutions. We need to focus on bugs and product issues.