Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    12.0p Export with ListGrid.exportData() does not watch .ds.xml defined displayField attribute + wrong sort

    Hi Isomorphic,

    reading these docs it should be possible to export displayField with ListGrid.exportData() somehow:
    exportData(), in-record displayField (must be declared in DataSource)
    exportData(), valueMap declared in DataSource
    exportData(), valueMap defined in code
    exportData(), optionDataSource
    These docs also say "note, exportValueFields has no effect for server-driven exports" which contradicts it's own docs which say:
    For exportData() calls (server-driven), we ordinarily export the underlying data value of all fields. However, if you set the exportValueFields property explicitly to false, any fields that have a DataSource-defined valueMap will have the mapped value exported instead. This is similar to the client-side treatment of valueMaps, except that the defaults are reversed.
    So to me it seems that the 1st docs have an error in that sentence as well as in missing a checkBox for "exportData(), valueMap declared in DataSource".

    Regardless of these issues, the feature itself is not working for me at all (v12.0p_2020-03-11):

    The exported XLS in the attached BuiltInDS sample always results in an "Owner" column filled with IDs, where I'd expect employee-Names and empty fields, like in the GUI. This is for either settings of exportValueFields (see testcase).

    Additionally, the sort for the export SQL, is wrong:

    Code:
    GUI, correct: ...FROM animals  LEFT OUTER JOIN employeeTable ON animals.lifeSpan = employeeTable.EmployeeId WHERE ('1'='1') ORDER BY employeeName
    Export, incorrect: ...FROM animals  LEFT OUTER JOIN employeeTable ON animals.lifeSpan = employeeTable.EmployeeId WHERE ('1'='1') ORDER BY animals.lifeSpan
    BuiltInDS.java:
    Code:
    package com.smartgwt.sample.client;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import com.google.gwt.core.client.EntryPoint;
    import com.smartgwt.client.Version;
    import com.smartgwt.client.core.KeyIdentifier;
    import com.smartgwt.client.data.DSRequest;
    import com.smartgwt.client.data.DataSource;
    import com.smartgwt.client.data.SortSpecifier;
    import com.smartgwt.client.types.ExportFormat;
    import com.smartgwt.client.types.SortDirection;
    import com.smartgwt.client.types.VerticalAlignment;
    import com.smartgwt.client.util.Page;
    import com.smartgwt.client.util.PageKeyHandler;
    import com.smartgwt.client.util.SC;
    import com.smartgwt.client.widgets.IButton;
    import com.smartgwt.client.widgets.Window;
    import com.smartgwt.client.widgets.events.ClickEvent;
    import com.smartgwt.client.widgets.events.ClickHandler;
    import com.smartgwt.client.widgets.form.fields.SelectItem;
    import com.smartgwt.client.widgets.grid.HeaderSpan;
    import com.smartgwt.client.widgets.grid.ListGrid;
    import com.smartgwt.client.widgets.grid.ListGridField;
    import com.smartgwt.client.widgets.layout.HLayout;
    import com.smartgwt.client.widgets.layout.VLayout;
    
    public class BuiltInDS implements EntryPoint {
        private VLayout mainLayout;
        private IButton recreateBtn;
        private Boolean exportValueFields;
    
        public void onModuleLoad() {
            KeyIdentifier debugKey = new KeyIdentifier();
            debugKey.setCtrlKey(true);
            debugKey.setKeyName("D");
    
            Page.registerKey(debugKey, new PageKeyHandler() {
                public void execute(String keyName) {
                    SC.showConsole();
                }
            });
    
            mainLayout = new VLayout(20);
            mainLayout.setWidth100();
            mainLayout.setHeight100();
    
            recreateBtn = new IButton("Recreate");
            recreateBtn.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    recreate();
                }
            });
            mainLayout.addMember(recreateBtn);
            recreate();
            mainLayout.draw();
        }
    
        private void recreate() {
            Window w = new Window();
            w.setWidth("95%");
            w.setHeight("95%");
            w.setMembersMargin(0);
            w.setModalMaskOpacity(70);
            w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
            w.setTitle("Problem with export of displayFields and sorting" + w.getTitle());
            w.setShowMinimizeButton(false);
            w.setIsModal(true);
            w.setShowModalMask(true);
            w.centerInPage();
    
            final ListGrid animalsGrid = new ListGrid(DataSource.get("animals"));
    
            animalsGrid.setHeight100();
            animalsGrid.setAutoFetchData(true);
            animalsGrid.setCanEdit(true);
            animalsGrid.setCanRemoveRecords(true);
            animalsGrid.setHeaderHeight(40);
            animalsGrid.setExportFieldWidths(true);
    
            ListGridField scientificName = new ListGridField("scientificName");
            scientificName.setCanEdit(false);
            ListGridField commonName = new ListGridField("commonName");
            ListGridField diet = new ListGridField("diet");
            ListGridField information = new ListGridField("information");
            information.setHidden(true);
            ListGridField status = new ListGridField("status");
            ListGridField lifeSpan = new ListGridField("lifeSpan");
            lifeSpan.setEditorProperties(new SelectItem() {
                {
                    ListGridField employeeID = new ListGridField("EmployeeId");
                    ListGridField employeeName = new ListGridField("Name");
                    setAllowEmptyValue(true);
                    setPickListFields(employeeID, employeeName);
                    setSortField("Name");
                }
            });
            ListGridField employeeEmployeeId = new ListGridField("employeeEmployeeId");
            ListGridField employeeName = new ListGridField("employeeName");
    
            animalsGrid
                    .setHeaderSpans(new HeaderSpan("includeFrom fields", new String[] { employeeEmployeeId.getName(), employeeName.getName(), }));
    
            animalsGrid.setFields(scientificName, commonName, diet, information, status, lifeSpan, employeeEmployeeId, employeeName);
            animalsGrid.setSort(new SortSpecifier[] { new SortSpecifier(scientificName.getName(), SortDirection.ASCENDING) });
    
            HLayout buttonsLayout = new HLayout(10) {
                {
                    setHeight(30);
                    setWidth100();
                    setDefaultLayoutAlign(VerticalAlignment.CENTER);
    
                    IButton exportDataBtn = new IButton("exportData()", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            doExport(animalsGrid);
                        }
                    });
    
                    IButton exportClientDataBtn = new IButton("exportClientData()", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            doClientExport(animalsGrid);
                        }
                    });
    
                    IButton invalidateCacheBtn = new IButton("invalidateCache()", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            animalsGrid.invalidateCache();
                        }
                    });
    
                    IButton filterRowBtn = new IButton("Toggle filterRow", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            animalsGrid.setShowFilterEditor(!animalsGrid.getShowFilterEditor());
                        }
                    });
    
                    IButton exportValueFieldsTrueBtn = new IButton("exportValueFields: true", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            exportValueFields = true;
                        }
                    });
    
                    IButton exportValueFieldsFalseBtn = new IButton("exportValueFields: false", new ClickHandler() {
                        @Override
                        public void onClick(ClickEvent event) {
                            exportValueFields = false;
                        }
                    });
    
                    addMembers(exportDataBtn, exportClientDataBtn, invalidateCacheBtn, filterRowBtn, exportValueFieldsTrueBtn,
                            exportValueFieldsFalseBtn);
                }
            };
    
            VLayout vLayout = new VLayout() {
                {
                    addMembers(animalsGrid, buttonsLayout);
                }
            };
            w.addItem(vLayout);
            w.show();
        }
    
        private void doExport(ListGrid workLG) {
            // The field list to use for the export
            final List<String> exportFieldList = getVisibleFieldList(workLG);
    
            // The export request to use (direct, no-load-all, load-all)
            final DSRequest exportRequest = new DSRequest() {
                {
                    setExportAs(ExportFormat.OOXML);
                    setExportFilename("myExport");
                    setExportFields(exportFieldList.toArray(new String[exportFieldList.size()]));
                    setExportValueFields(exportValueFields);
                }
            };
            workLG.exportData(exportRequest);
        }
    
        private void doClientExport(ListGrid workLG) {
            // The field list to use for the export
            final List<String> exportFieldList = getVisibleFieldList(workLG);
    
            // The export request to use (direct, no-load-all, load-all)
            final DSRequest exportRequest = new DSRequest() {
                {
                    setExportAs(ExportFormat.OOXML);
                    setExportFilename("myExport");
                    setExportFields(exportFieldList.toArray(new String[exportFieldList.size()]));
                }
            };
            workLG.exportClientData(exportRequest);
        }
    
        private List<String> getVisibleFieldList(ListGrid lg) {
            List<String> visibleFieldList = new ArrayList<String>();
            for (ListGridField lgf : lg.getFields())
                if (lg.fieldIsVisible(lgf.getName()) && (lgf.getCanExport() == null || lgf.getCanExport()))
                    visibleFieldList.add(lgf.getName());
            return visibleFieldList;
        }
    
    }
    animals.ds.xml:
    Code:
    <DataSource
        ID="animals"
        serverType="sql"
        tableName="animals"
        testFileName="animals.data.xml" useAnsiJoins="true"
    >
        <fields>
            <field name="commonName"      title="Animal"             type="text" sortByField="diet" />
            <field name="scientificName"  title="Scientific Name"    type="text"  primaryKey="true"  required="true" />
            <field name="lifeSpan"        title="Life Span"          type="integer"/>
            <field name="status"          title="Endangered Status"  type="text">
                <valueMap>
                    <value>Threatened</value>
                    <value>Endangered</value>
                    <value>Not Endangered</value>
                    <value>Not currently listed</value>
                    <value>May become threatened</value>
                    <value>Protected</value>
                </valueMap>
            </field>
            <field name="diet"            title="Diet"               type="text"/>
            <field name="information"     title="Interesting Facts"  type="text"  length="1000"/>
            <field name="picture"         title="Picture"            type="image" detail="true"
                   imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/"/>
            <field foreignKey="employees.EmployeeId" name="lifeSpan" title="Owner" type="integer" joinType="outer" displayField="employeeName" />
    
            <field name="employeeEmployeeId" includeFrom="employees.EmployeeId" title="employeeEmployeeId-iF" />
            <field name="employeeName" includeFrom="employees.Name" title="employeeName-iF" />
        </fields>
    </DataSource>

    Best regards
    Blama

    #2
    Firstly, we agree that the documentation is contradictory here, so thank you for pointing that out - we will fix it.

    As to your test case, there are a couple of wrinkles that make things a bit confusing, but it is working as documented. The documentation you quote shows that your use case - exportData() with in-record displayField - will export both the valueField and the displayField. This was the original case that inspired the note in the docs that "exportValueFields has no effect for server-driven exports"; the exception to that rule where valueMapped fields are concerned came later, and the documentation was not updated to match.

    Thus, it is correct that the "lifeSpan" field is exported with valueField contents. The "employeeName" in your contrived example is also being exported, but that is because you are including that field in the grid in its own right. In a situation like that, we would normally expect "employeeField" to be exported twice: once in its own right, and once because it is the displayField for "lifeSpan". Whether or not that is correct is arguable, but that is what we would expect. It isn't happening in your case because you are explicitly setting the "exportFields" property on the export, so we only export the fields named in that property. You can see the difference by simply not setting "exportFields" and allowing the export system to include the fields it ordinarily would.

    More generally, your post seems to be based on an expectation that exportData() should create an exported document that is close to exactly the same as the UI. That is not really the case: we do aim to make exportClientData() exports mirror the UI very closely, but exportData() is more intended for cases where you want to export possibly large amounts of data, and the UI is really only providing a guide for the export process.

    Regards,
    Isomorphic Software Support

    Comment


      #3
      Hi Isomorphic,

      thanks, I'll wait for the docs change and then retry with the updated information.

      I still think I'll be able to solve my "kinda-exportClientData()" user case with exportData(). I do think that this is an actual use case when you have many records matching the criteria.
      Here exportClientData() will take loo long at some point because of long running JavaScript and also you need to load all records clientside before.
      But if I can get my displayField, everything is OK. I do though think I (or anyone) will need the underlying id-field, so having a setter here to suppress it would be nice.

      So my remaining questions are:
      • Is there already a setter to suppress the underlying id-field? If not, could you add one?
      • Did you see the sorting issue in #1?
      • Minor: Should there also be two "x" in the row for "exportData(), valueMap declared in DataSource"
      Thank you & Best regards
      Blama

      Comment


        #4
        Hi Isomorphic,

        another issue: I commented the line
        Code:
        doExport():
                  setExportFields(exportFieldList.toArray(new String[exportFieldList.size()]));
        This results in both modes, meaning setExportValueFields(true/false) to a column at the end with no name in the 1st row and the owner names in the data (v12.0p_2020-03-11).

        Best regards
        Blama

        Comment


          #5
          Hi Isomorphic,

          in addition to the sorting issue in #1 (which is a serverside issue I assume) it seems that the export request does also not include all sort fields from the ListGrid, if the grid is grouped and setSortByGroupFirst(true) is set. As written here, I think that the request should include all the information that also the ListGrid requests, that lead to the current data, includes.

          This means: operationId (see other thread), criteria (OK), implicitCriteria (OK), sort (setSortByGroupFirst-issue) - basically everything that invalidateCache() would issue minus startRow/endRow plus what is needed to make the request an export (OK).

          Best regards
          Blama

          Comment


            #6
            Is there some step that needs taking to produce the incorrect sort behavior you claim in #1? We are unable to reproduce this with recent 12.0 builds, even with the exact SmartGWT code you posted

            Comment


              #7
              Hi Isomorphic,

              I retried with the sample from #1 and v12.0p_2020-03-11 as well as v12.0p_2020-04-10.
              You are right, I can't reproduce there. I tested other settings and it's still working as expected.
              In my application (v12.0p_2020-03-11) I do see the issue, though. I'll try to reproduce in the test case again tomorrow.

              Sorry for the wrong testcase & Best regards
              Blama

              Comment


                #8
                Hi Isomorphic,

                here the updated testcase.
                • On load: In SQL, it orders by employeeName (=the displayField of the sortBy field lifeSpan) (expected)
                • On exportData(): In SQL, it orders by lifeSpan, the sortBy field (not expected, expect SQL order by as above)
                animals.ds.xml:
                Code:
                <DataSource
                    ID="animals"
                    serverType="sql"
                    tableName="animals"
                    testFileName="animals.data.xml" useAnsiJoins="true"
                >
                    <fields>
                        <field name="commonName"      title="Animal"             type="text" sortByField="diet" />
                        <field name="scientificName"  title="Scientific Name"    type="text"  primaryKey="true"  required="true" />
                        <field name="status"          title="Endangered Status"  type="text">
                            <valueMap>
                                <value>Threatened</value>
                                <value>Endangered</value>
                                <value>Not Endangered</value>
                                <value>Not currently listed</value>
                                <value>May become threatened</value>
                                <value>Protected</value>
                            </valueMap>
                        </field>
                        <field name="diet"            title="Diet"               type="text"/>
                        <field name="information"     title="Interesting Facts"  type="text"  length="1000"/>
                        <field name="picture"         title="Picture"            type="image" detail="true"
                               imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/"/>
                        <field foreignKey="employees.EmployeeId" name="lifeSpan" title="Owner" type="integer" joinType="outer" displayField="employeeName" />
                
                        <field name="employeeEmployeeId" includeFrom="employees.EmployeeId" title="employeeEmployeeId-iF" />
                        <field name="employeeName" includeFrom="employees.Name" title="employeeName-iF" />
                    </fields>
                </DataSource>
                BuiltInDS.java:
                Code:
                package com.smartgwt.sample.client;
                
                import java.util.ArrayList;
                import java.util.List;
                
                import com.google.gwt.core.client.EntryPoint;
                import com.smartgwt.client.Version;
                import com.smartgwt.client.core.KeyIdentifier;
                import com.smartgwt.client.data.DSRequest;
                import com.smartgwt.client.data.DataSource;
                import com.smartgwt.client.data.SortSpecifier;
                import com.smartgwt.client.types.ExportFormat;
                import com.smartgwt.client.types.SortDirection;
                import com.smartgwt.client.types.VerticalAlignment;
                import com.smartgwt.client.util.Page;
                import com.smartgwt.client.util.PageKeyHandler;
                import com.smartgwt.client.util.SC;
                import com.smartgwt.client.widgets.IButton;
                import com.smartgwt.client.widgets.Window;
                import com.smartgwt.client.widgets.events.ClickEvent;
                import com.smartgwt.client.widgets.events.ClickHandler;
                import com.smartgwt.client.widgets.form.fields.SelectItem;
                import com.smartgwt.client.widgets.grid.HeaderSpan;
                import com.smartgwt.client.widgets.grid.ListGrid;
                import com.smartgwt.client.widgets.grid.ListGridField;
                import com.smartgwt.client.widgets.layout.HLayout;
                import com.smartgwt.client.widgets.layout.VLayout;
                
                public class BuiltInDS implements EntryPoint {
                    private VLayout mainLayout;
                    private IButton recreateBtn;
                    private Boolean exportValueFields;
                
                    public void onModuleLoad() {
                        KeyIdentifier debugKey = new KeyIdentifier();
                        debugKey.setCtrlKey(true);
                        debugKey.setKeyName("D");
                
                        Page.registerKey(debugKey, new PageKeyHandler() {
                            public void execute(String keyName) {
                                SC.showConsole();
                            }
                        });
                
                        mainLayout = new VLayout(20);
                        mainLayout.setWidth100();
                        mainLayout.setHeight100();
                
                        recreateBtn = new IButton("Recreate");
                        recreateBtn.addClickHandler(new ClickHandler() {
                            @Override
                            public void onClick(ClickEvent event) {
                                recreate();
                            }
                        });
                        mainLayout.addMember(recreateBtn);
                        recreate();
                        mainLayout.draw();
                    }
                
                    private void recreate() {
                        Window w = new Window();
                        w.setWidth("95%");
                        w.setHeight("95%");
                        w.setMembersMargin(0);
                        w.setModalMaskOpacity(70);
                        w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
                        w.setTitle("Problem with export of displayFields and sorting" + w.getTitle());
                        w.setShowMinimizeButton(false);
                        w.setIsModal(true);
                        w.setShowModalMask(true);
                        w.centerInPage();
                
                        final ListGrid animalsGrid = new ListGrid(DataSource.get("animals"));
                
                        animalsGrid.setHeight100();
                        animalsGrid.setAutoFetchData(true);
                        animalsGrid.setCanEdit(true);
                        animalsGrid.setCanRemoveRecords(true);
                        animalsGrid.setHeaderHeight(40);
                        animalsGrid.setExportFieldWidths(true);
                
                        ListGridField scientificName = new ListGridField("scientificName");
                        scientificName.setCanEdit(false);
                        ListGridField commonName = new ListGridField("commonName");
                        ListGridField diet = new ListGridField("diet");
                        ListGridField information = new ListGridField("information");
                        information.setHidden(true);
                        ListGridField status = new ListGridField("status");
                        ListGridField lifeSpan = new ListGridField("lifeSpan");
                        lifeSpan.setEditorProperties(new SelectItem() {
                            {
                                ListGridField employeeID = new ListGridField("EmployeeId");
                                ListGridField employeeName = new ListGridField("Name");
                                setAllowEmptyValue(true);
                                setPickListFields(employeeID, employeeName);
                                setSortField("Name");
                            }
                        });
                        ListGridField employeeEmployeeId = new ListGridField("employeeEmployeeId");
                        ListGridField employeeName = new ListGridField("employeeName");
                
                        animalsGrid
                                .setHeaderSpans(new HeaderSpan("includeFrom fields", new String[] { employeeEmployeeId.getName(), employeeName.getName(), }));
                
                        animalsGrid.setFields(scientificName, commonName, diet, information, status, lifeSpan, employeeEmployeeId, employeeName);
                        animalsGrid.setSort(new SortSpecifier[] { new SortSpecifier(lifeSpan.getName(), SortDirection.ASCENDING) });
                
                        HLayout buttonsLayout = new HLayout(10) {
                            {
                                setHeight(30);
                                setWidth100();
                                setDefaultLayoutAlign(VerticalAlignment.CENTER);
                
                                IButton exportDataBtn = new IButton("exportData()", new ClickHandler() {
                                    @Override
                                    public void onClick(ClickEvent event) {
                                        doExport(animalsGrid);
                                    }
                                });
                
                                IButton invalidateCacheBtn = new IButton("invalidateCache()", new ClickHandler() {
                                    @Override
                                    public void onClick(ClickEvent event) {
                                        animalsGrid.invalidateCache();
                                    }
                                });
                
                                addMembers(exportDataBtn, invalidateCacheBtn);
                            }
                        };
                
                        VLayout vLayout = new VLayout() {
                            {
                                addMembers(animalsGrid, buttonsLayout);
                            }
                        };
                        w.addItem(vLayout);
                        w.show();
                    }
                
                    private void doExport(ListGrid workLG) {
                        // The field list to use for the export
                        final List<String> exportFieldList = getVisibleFieldList(workLG);
                
                        // The export request to use (direct, no-load-all, load-all)
                        final DSRequest exportRequest = new DSRequest() {
                            {
                                setExportAs(ExportFormat.OOXML);
                                setExportFilename("myExport");
                                setExportFields(exportFieldList.toArray(new String[exportFieldList.size()]));
                                setExportValueFields(exportValueFields);
                            }
                        };
                        workLG.exportData(exportRequest);
                    }
                
                    private List<String> getVisibleFieldList(ListGrid lg) {
                        List<String> visibleFieldList = new ArrayList<String>();
                        for (ListGridField lgf : lg.getFields())
                            if (lg.fieldIsVisible(lgf.getName()) && (lgf.getCanExport() == null || lgf.getCanExport()))
                                visibleFieldList.add(lgf.getName());
                        return visibleFieldList;
                    }
                
                }
                Best regards
                Blama

                Comment


                  #9
                  Hi Isomorphic,

                  could you reproduce this one? (Wrong ORDER BY in #8, missing ORDER BY in #5)

                  Thank you & Best regards
                  Blama

                  Comment

                  Working...
                  X